Hi,
Yes,  but source of one column is ommitedVAREFRVSTR_DETAIL.LAENGDE_NAVNYou 
group by VAREFRVSTR.LAENGDE_NAVN
Change your group by or change source of column in subquery
Regards,Karol Bieniaszewski
-------- Oryginalna wiadomość --------Od: "michael.vilhelm...@microcom.dk 
[firebird-support]" <firebird-support@yahoogroups.com> Data: 08.02.2018  09:06  
(GMT+01:00) Do: firebird-support@yahoogroups.com Temat: Re: [firebird-support] 
Query problem 

 



  


    
      
      
      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.comTemat: [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?



    
     

    
    


  • [firebird-supp... michael.vilhelm...@microcom.dk [firebird-support]
    • Re: [fire... liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
      • Re: [... michael.vilhelm...@microcom.dk [firebird-support]
        • R... liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
          • ... michael.vilhelm...@microcom.dk [firebird-support]

Reply via email to