W dniu 2014-09-03 o 08:31, Dmitry Yemanov dimitr@...
[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
>
>
>