Re: [firebird-support] Query stop working after upgrade from 2.1.5 to 2.1.6
>Hi, > >I overcome this issue with this query > >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 = any (select ID >from IDY_DO_ZAPYTANIA)) > ) 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) > >I have to change "where kk.I_ID_KOSZTY = ko.I_ID_KOSZTY" to >"kk.I_ID_KOSZTY = any (select ID from IDY_DO_ZAPYTANIA)". Good to hear you overcome your issue. I took a look at your database and query this evening, and to my surprise your original query executed fine on 2.5.2 (26539)! (well, not yielding any result due to IDY_DO_ZAPYTANIA being empty, but removing that table gave 12 rows). An alternative query you could use (up to you to choose, you could try both if this is just a small test database and you have a bigger real database), is: with tmp (C_SYMBOL, I_ID_KOSZTY, C_OPIS_X) as (select cs.C_SYMBOL, kk.I_ID_KOSZTY, min(cs.C_OPIS) from T_CENY_SKLADNIKI as cs join T_CENY_W_OKRESIE as cwo on cwo.I_ID_CENY_OKRS = cs.I_ID_CENY_OKRS join R_KOSZTY kk on cwo.I_ID_CENNIKA = kk.I_ID_CENNIKA group by 1, 2) select ke.I_ID_GRUPY, ke.C_SYMBOL, t.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 join R_KOSZT_OKRESY as ko on ko.I_ID_KOSZTY = k.I_ID_KOSZTY join R_KOSZT_ELEMENTY as ke on ke.I_ID_KOSZT_OKRESY = ko.I_ID_KOSZT_OKRESY join IDY_DO_ZAPYTANIA z on k.I_ID_KOSZTY = z.ID left join tmp t on ke.C_SYMBOL = t.C_SYMBOL and ko.I_ID_KOSZTY = t.I_ID_KOSZTY where ke.SI_USEINSUMMARY = 1 group by ke.I_ID_GRUPY, ke.C_SYMBOL, t.C_OPIS_X, SI_WYKLADNIK, SI_IDJEDNOSTKA, SI_STPOTYPE, SI_CURRENCYPERVALUE order by max(ke.I_ORDER_INDEX) HTH, Set
Re: [firebird-support] Query stop working after upgrade from 2.1.5 to 2.1.6
Hi, I overcome this issue with this query 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 = any (select ID from IDY_DO_ZAPYTANIA)) ) 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) I have to change "where kk.I_ID_KOSZTY = ko.I_ID_KOSZTY" to "kk.I_ID_KOSZTY = any (select ID from IDY_DO_ZAPYTANIA)". 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 <*> 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: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Query stop working after upgrade from 2.1.5 to 2.1.6
W dniu 2014-09-03 o 08:51, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] pisze: > > > >Hi, > > > >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 > > > >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 > > ) 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? > > Hi Macma! > > I would have tried this query (I've assumed that ID is the primary key > of IDY_DO_ZAPYTANIA) and checked whether the result was as desired > (admittedly, I'm not used to using MIN in a subselect): > > select > ke.I_ID_GRUPY, > ke.C_SYMBOL, > (select min(cs.C_OPIS) > from T_CENY_SKLADNIKI as cs > join T_CENY_W_OKRESIE as cwo > on cwo.I_ID_CENY_OKRS = cs.I_ID_CENY_OKRS > join R_KOSZTY kk on cwo.I_ID_CENNIKA = kk.I_ID_CENNIKA > where cs.C_SYMBOL = ke.C_SYMBOL > and kk.I_ID_KOSZTY = ko.I_ID_KOSZTY) 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, > max(ke.I_ORDER_INDEX) I_ORDER > from R_KOSZTY k > join R_KOSZT_OKRESY as ko on ko.I_ID_KOSZTY = k.I_ID_KOSZTY > join R_KOSZT_ELEMENTY as ke on ke.I_ID_KOSZT_OKRESY = ko.I_ID_KOSZT_OKRESY > join IDY_DO_ZAPYTANIA z on k.I_ID_KOSZTY = z.ID > where ke.SI_USEINSUMMARY = 1 > group by ke.I_ID_GRUPY, ke.C_SYMBOL, 3, SI_WYKLADNIK, SI_IDJEDNOSTKA, > SI_STPOTYPE, SI_CURRENCYPERVALUE > order by 12 > I get the same error with Your query > > I don't know whether your error is a result of grouping by C_OPIS_X (I > didn't think it was allowed), ordering by a field you do not select (I > added it to the output, change back if this isn't the problem) or > something else. Using LEFT JOIN as opposed to [INNER] JOIN reduces the > options for the optimizer - it is sometimes useful as part of > optimization, but only after experiencing the original query being slow. > > If this doesn't solve your problem, please tell us more about your > table definitions - I've no clue which fields are unique or having > lots of duplicates in your tables, nor which indexes/keys you've > defined and that limits the possible modifications I (and others on > this list) can do to your query. > Here You can download sample database https://sydel.technicon.com.pl/~grzegorz/tmp/EKONOMICZNA.zip I've check that this problem also occur when upgrading from 2.5.2 to 2.5.3 Best Regards, macma > > HTH, > Set > > PS! I've never used Fb 2.1 myself, only 0.9.4, 1.5 and 2.5. > > ++ 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: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] Query stop working after upgrade from 2.1.5 to 2.1.6
>Hi, > >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 > >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 > ) 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? Hi Macma! I would have tried this query (I've assumed that ID is the primary key of IDY_DO_ZAPYTANIA) and checked whether the result was as desired (admittedly, I'm not used to using MIN in a subselect): select ke.I_ID_GRUPY, ke.C_SYMBOL, (select min(cs.C_OPIS) from T_CENY_SKLADNIKI as cs join T_CENY_W_OKRESIE as cwo on cwo.I_ID_CENY_OKRS = cs.I_ID_CENY_OKRS join R_KOSZTY kk on cwo.I_ID_CENNIKA = kk.I_ID_CENNIKA where cs.C_SYMBOL = ke.C_SYMBOL and kk.I_ID_KOSZTY = ko.I_ID_KOSZTY) 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, max(ke.I_ORDER_INDEX) I_ORDER from R_KOSZTY k join R_KOSZT_OKRESY as ko on ko.I_ID_KOSZTY = k.I_ID_KOSZTY join R_KOSZT_ELEMENTY as ke on ke.I_ID_KOSZT_OKRESY = ko.I_ID_KOSZT_OKRESY join IDY_DO_ZAPYTANIA z on k.I_ID_KOSZTY = z.ID where ke.SI_USEINSUMMARY = 1 group by ke.I_ID_GRUPY, ke.C_SYMBOL, 3, SI_WYKLADNIK, SI_IDJEDNOSTKA, SI_STPOTYPE, SI_CURRENCYPERVALUE order by 12 I don't know whether your error is a result of grouping by C_OPIS_X (I didn't think it was allowed), ordering by a field you do not select (I added it to the output, change back if this isn't the problem) or something else. Using LEFT JOIN as opposed to [INNER] JOIN reduces the options for the optimizer - it is sometimes useful as part of optimization, but only after experiencing the original query being slow. If this doesn't solve your problem, please tell us more about your table definitions - I've no clue which fields are unique or having lots of duplicates in your tables, nor which indexes/keys you've defined and that limits the possible modifications I (and others on this list) can do to your query. HTH, Set PS! I've never used Fb 2.1 myself, only 0.9.4, 1.5 and 2.5.
[firebird-support] Query stop working after upgrade from 2.1.5 to 2.1.6
Hi, 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 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 ) 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? 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 <*> 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: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
