| Subject | Re: [firebird-support] Query problem |
|---|---|
| Author | liviuslivius |
| Post date | 2018-02-08T08:37:21Z |
Hi
Your bolded subquery need to have value e.g. VAREPLU_ID but you not group by this field and server do not know its value then
If I understand that correct, I should do something like this:
SELECT
VARER.PLU_NR,
VAREFRVSTR_DETAIL.V509INDEX,
Sum(VAREFRVSTR_DETAIL.ANTALSTK) AS AntalStk,
(SELECT
Sum(TRANSAKTIONER.SALGSTK)
FROM
TRANSAKTIONER
WHERE
TRANSAKTIONER.ART IN (0, 1) AND
TRANSAKTIONER.VAREFRVSTRNR = VAREFRVSTR_DETAIL.VAREPLU_ID AND
TRANSAKTIONER.FARVE_NAVN = VAREFRVSTR_DETAIL.FARVE_NAVN AND
TRANSAKTIONER.STOERRELSE_NAVN = VAREFRVSTR_DETAIL.STOERRELSE_NAVN AND
TRANSAKTIONER.LAENGDE_NAVN = VAREFRVSTR_DETAIL.LAENGDE_NAVN) AS Antal,
VAREFRVSTR.EANNUMMER,
VAREFRVSTR.VAREPLU_ID,
VAREFRVSTR.FARVE_NAVN,
VAREFRVSTR.LAENGDE_NAVN,
VAREFRVSTR.STOERRELSE_NAVN
FROM
VAREFRVSTR_DETAIL
INNER JOIN VAREFRVSTR ON VAREFRVSTR.V509INDEX = VAREFRVSTR_DETAIL.V509INDEX
INNER JOIN VARER ON VAREFRVSTR_DETAIL.VAREPLU_ID = VARER.PLU_NR AND VARER.PLU_NR IN ('006149', '009219', '009220', '§!¤')
GROUP BY
VARER.PLU_NR,
VAREFRVSTR_DETAIL.V509INDEX,
VAREFRVSTR.EANNUMMER,
VAREFRVSTR.VAREPLU_ID,
VAREFRVSTR.FARVE_NAVN,
VAREFRVSTR.LAENGDE_NAVN,
VAREFRVSTR.STOERRELSE_NAVN
But the Bolded query does not work. Same 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)
---In firebird-support@yahoogroups.com, <liviuslivius@...> wrote :
Hi
I have 2 querys. One is working.
The other is not.
I use Firebird 2.5.3.26780 (i have tried in 2.5.6.27020 and 3.0.0.32483 as well with same result)
Query working:
SELECT
VARER.PLU_NR,
VAREFRVSTR_DETAIL.V509INDEX,
Sum(VAREFRVSTR_DETAIL.ANTALSTK) AS AntalStk,
(SELECT
Sum(VV_OMSETNING.ANTAL)
FROM
VV_OMSETNING
WHERE
VV_OMSETNING.V509INDEX = VAREFRVSTR_DETAIL.V509INDEX) AS Antal,
VAREFRVSTR.EANNUMMER
FROM
VAREFRVSTR_DETAIL
INNER JOIN VAREFRVSTR ON VAREFRVSTR.V509INDEX = VAREFRVSTR_DETAIL.V509INDEX
INNER JOIN VARER ON VAREFRVSTR_DETAIL.VAREPLU_ID = VARER.PLU_NR AND VARER.PLU_NR IN ('006149', '009219', '009220', '§!¤')
GROUP BY
VARER.PLU_NR,
VAREFRVSTR_DETAIL.V509INDEX,
VAREFRVSTR.EANNUMMER
Query not working
SELECT
VARER.PLU_NR,
VAREFRVSTR_DETAIL.V509INDEX,
Sum(VAREFRVSTR_DETAIL.ANTALSTK) AS AntalStk,
(SELECT
Sum(TRANSAKTIONER.SALGSTK)
FROM
TRANSAKTIONER
WHERE
TRANSAKTIONER.ART IN (0, 1) AND
TRANSAKTIONER.VAREFRVSTRNR = VAREFRVSTR_DETAIL.VAREPLU_ID AND
TRANSAKTIONER.FARVE_NAVN = VAREFRVSTR_DETAIL.FARVE_NAVN AND
TRANSAKTIONER.STOERRELSE_NAVN = VAREFRVSTR_DETAIL.STOERRELSE_NAVN AND
TRANSAKTIONER.LAENGDE_NAVN = VAREFRVSTR_DETAIL.LAENGDE_NAVN) AS Antal,
VAREFRVSTR.EANNUMMER
FROM
VAREFRVSTR_DETAIL
INNER JOIN VAREFRVSTR ON VAREFRVSTR.V509INDEX = VAREFRVSTR_DETAIL.V509INDEX
INNER JOIN VARER ON VAREFRVSTR_DETAIL.VAREPLU_ID = VARER.PLU_NR AND VARER.PLU_NR IN ('006149', '009219', '009220', '§!¤')
GROUP BY
VARER.PLU_NR,
VAREFRVSTR_DETAIL.V509INDEX,
VAREFRVSTR.EANNUMMER
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 difference between the two querys are made in BOLD.
Im not quite qure why one works, and not the other
Anyone with any hints or explanation?