W dniu 2014-09-03 o 08:31, Dmitry Yemanov [email protected] [firebird-support] pisze: > 03.09.2014 09:44, Macma wrote: >> I found that after upgrade from FireBird 2.1.5 Update 1 to 2.1.6 my >> query stop working with error "Dynamic SQL Error SQL error code = -104 >> Invalid expression in the select list (not contained in either an >> aggregate function or the GROUP BY clause)". The same error occur on 2.5.3 > Interesting. The stricter checks were not really intended, it looks like > an indirect result of some bugfix. I hope this issue doesn't reject > formally correct queries. > >> select >> ke.I_ID_GRUPY, >> ke.C_SYMBOL, >> ( >> select first 1 cs.C_OPIS from T_CENY_SKLADNIKI as cs >> left join T_CENY_W_OKRESIE as cwo on (cwo.I_ID_CENY_OKRS = >> cs.I_ID_CENY_OKRS) >> where cs.C_SYMBOL = ke.C_SYMBOL and cwo.I_ID_CENNIKA = any (select >> I_ID_CENNIKA from R_KOSZTY as kk where kk.I_ID_KOSZTY = ko.I_ID_KOSZTY) >> group by cs.C_OPIS > Out of curiosity, why both "FIRST 1 cs.C_OPIS" and "GROUP BY cs.C_OPIS"? > The grouping looks redundant here. Because I have to sum all element with the same "ke.C_SYMBOL" and description of that symbol are changing over time i need to display first found. But You right that "group by cs.C_OPIS" are not necessary. >> ) as C_OPIS_X, >> sum(ke.N_VALUE) as N_VALUE_SUMA, >> sum(ke.N_VATIN) as N_VATIN_SUMA, >> sum(ke.N_COSTNETTO) as N_COSTNETTO_SUMA, >> sum(ke.N_COSTBRUTTO) as N_COSTBRUTTO_SUMA, >> ke.SI_WYKLADNIK, ke.SI_IDJEDNOSTKA, ke.SI_STPOTYPE, >> ke.SI_CURRENCYPERVALUE >> from >> R_KOSZTY k >> left join R_KOSZT_OKRESY as ko on (ko.I_ID_KOSZTY = k.I_ID_KOSZTY) >> left join R_KOSZT_ELEMENTY as ke on (ke.I_ID_KOSZT_OKRESY = >> ko.I_ID_KOSZT_OKRESY) >> where ke.SI_USEINSUMMARY = 1 and k.I_ID_KOSZTY = any (select ID from >> IDY_DO_ZAPYTANIA) >> group by ke.I_ID_GRUPY, ke.C_SYMBOL, C_OPIS_X, SI_WYKLADNIK, >> SI_IDJEDNOSTKA, SI_STPOTYPE, SI_CURRENCYPERVALUE >> order by max(ke.I_ORDER_INDEX) >> >> Any idea how to overcome this issue? > Instead of grouping on the subquery, you need to group on its > dependencies. At the first glance, there are two of them: ke.C_SYMBOL > and ko.I_ID_KOSZTY. The latter one is missing in the GROUP BY list. When I add "ko.I_ID_KOSZTY" to GROUP BY list the results are not what I expected because I get partial sum for every "ke.C_SYMBOL" and "ko.I_ID_KOSZTY" insted of partial sum of elements with different "ke.C_SYMBOL" only. I share sample database here https://sydel.technicon.com.pl/~grzegorz/tmp/EKONOMICZNA.zip
Best Regards, macma > > > > > ------------------------------------ > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > ------------------------------------ > > Yahoo Groups Links > > > ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
