Subject Re: [firebird-support] Query problem
Author
Post date 2018-02-08T08:06:04Z
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,

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

Regards,
Karol Bieniaszewski

-------- Oryginalna wiadomość --------
Od: "Michael.Vilhelmsen@... [firebird-support]" <firebird-support@yahoogroups.com>
Data: 07.02.2018 22:24 (GMT+01:00)
Do: firebird-support@yahoogroups.com
Temat: [firebird-support] Query problem

 

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?