Feature not supported: Group by in correlated subselect

pbreidenich
Padawan

Hi everybody,

I get the following error message when I run the following SQL:

"[0A000] Feature not supported: Group by in correlated subselect".

SELECT  Auftragsnr,
Artikelnr,
(
SELECT DISTINCT
FIRST_VALUE(kp.PREIS)
OVER (PARTITION BY kp.firma, kp.Kundennr, kp.Artikelnr ORDER BY kp.GUELTIG_AB DESC)
FROM SYSADM.MV_KUNDE_PREISE kp
WHERE kp.Firma = 2 AND kp.KUNDENNR = 8141379
and kp.Artikelnr = ba.ARTIKELNR
AND kp.ABNAHME_PREISKENNZEICHEN = ba.LIEFEREINHEIT
AND kp.GUELTIG_AB <= '02.10.2021'
) Test_Preis
FROM PS_BASE_AUFTRAG ba
WHERE AUFTRAGSNR = 6908918
AND ARTIKELNR = 14001;

Can someone please help? I don't use Group By in the SQL. Is there an alternative how I can implement the SQL?

With best regards and thanks,
Peter

1 ACCEPTED SOLUTION

Accepted Solutions

mwellbro
Xpert

Hi @pbreidenich ,

I tried to test your query with the following code ( obviously I took some liberties regarding the data types 😉 ) :

 

CREATE OR REPLACE TABLE PS_BASE_AUFTRAG(
Auftragsnr DECIMAL(20,0),
artikelnr decimal(10,0),
test_preis decimal(18,2),
LIEFEREINHEIT decimal(10,0)
);

CREATE OR REPLACE TABLE MV_KUNDE_PREISE(
KUNDENNR decimal(10,0),
artikelnr decimal(10,0),
ABNAHME_PREISKENNZEICHEN decimal(10,0),
GUELTIG_AB TIMESTAMP,
firma varchar(200),
preis decimal(18,2)
);


SELECT  ba.Auftragsnr,
        ba.Artikelnr,
        kp.Test_Preis
FROM PS_BASE_AUFTRAG ba
, (
        SELECT distinct  kp.Firma,kp.KUNDENNR,kp.Artikelnr,kp.ABNAHME_PREISKENNZEICHEN,FIRST_VALUE(kp.PREIS) OVER (PARTITION BY kp.firma, kp.Kundennr, kp.Artikelnr ORDER BY kp.GUELTIG_AB DESC) test_preis
        FROM MV_KUNDE_PREISE kp
        WHERE --kp.Firma = 2 AND kp.KUNDENNR = 8141379
        --and kp.Artikelnr = ba.ARTIKELNR
        --AND kp.ABNAHME_PREISKENNZEICHEN = ba.LIEFEREINHEIT
        kp.GUELTIG_AB <= '02.10.2021'
        ) kp
WHERE ba.AUFTRAGSNR = 6908918
AND ba.ARTIKELNR = 14001
AND kp.Artikelnr = ba.ARTIKELNR
AND kp.ABNAHME_PREISKENNZEICHEN = ba.LIEFEREINHEIT
AND kp.Firma = 2 AND kp.KUNDENNR = 8141379

 

This was executable, when I tried the suggestion from @wunderdata I got an error because the CTE q1 referenced a columns from ba ( aka. for the correlation/the join ), so unless I messed up this doesn´t seem to work.

I would probably go for an actual group by instead of a window-function + a distinct, but maybe you have good reasons for
building the construct in such a way.
Also ( just personal preference ) I´d probably use the ANSI style join notation with "ba inner join kp on ....." , might end up more readable and with this SQL-86-notation I always get the feeling I´m implicitly condoning a cross join ( because everything in the FROM clause just floats about and somewhere in the WHERE clause I hope to find a correlation/join criteria 🙂  ).

Cheers,
Malte

View solution in original post

4 REPLIES 4

wunderdata
Contributor

First_value is a grouping. But beside that u're not really having any relations except for the article_number, but ensuring the fit just with you where clauses.
Therefor, a simple cross join should produce the result you're seeking:

with q1 as (
SELECT DISTINCT
FIRST_VALUE(kp.PREIS)
OVER (PARTITION BY kp.firma, kp.Kundennr, kp.Artikelnr ORDER BY kp.GUELTIG_AB DESC) as Test_Preis
FROM SYSADM.MV_KUNDE_PREISE kp
WHERE kp.Firma = 2 AND kp.KUNDENNR = 8141379
and kp.Artikelnr = ba.ARTIKELNR
AND kp.ABNAHME_PREISKENNZEICHEN = ba.LIEFEREINHEIT
AND kp.GUELTIG_AB <= '02.10.2021'
)
Select Auftragsnr,
Artikelnr,
Test_Preis
FROM PS_BASE_AUFTRAG ba cross join q1
WHERE AUFTRAGSNR = 6908918
AND ARTIKELNR = 14001;

mwellbro
Xpert

Hi @pbreidenich ,

I tried to test your query with the following code ( obviously I took some liberties regarding the data types 😉 ) :

 

CREATE OR REPLACE TABLE PS_BASE_AUFTRAG(
Auftragsnr DECIMAL(20,0),
artikelnr decimal(10,0),
test_preis decimal(18,2),
LIEFEREINHEIT decimal(10,0)
);

CREATE OR REPLACE TABLE MV_KUNDE_PREISE(
KUNDENNR decimal(10,0),
artikelnr decimal(10,0),
ABNAHME_PREISKENNZEICHEN decimal(10,0),
GUELTIG_AB TIMESTAMP,
firma varchar(200),
preis decimal(18,2)
);


SELECT  ba.Auftragsnr,
        ba.Artikelnr,
        kp.Test_Preis
FROM PS_BASE_AUFTRAG ba
, (
        SELECT distinct  kp.Firma,kp.KUNDENNR,kp.Artikelnr,kp.ABNAHME_PREISKENNZEICHEN,FIRST_VALUE(kp.PREIS) OVER (PARTITION BY kp.firma, kp.Kundennr, kp.Artikelnr ORDER BY kp.GUELTIG_AB DESC) test_preis
        FROM MV_KUNDE_PREISE kp
        WHERE --kp.Firma = 2 AND kp.KUNDENNR = 8141379
        --and kp.Artikelnr = ba.ARTIKELNR
        --AND kp.ABNAHME_PREISKENNZEICHEN = ba.LIEFEREINHEIT
        kp.GUELTIG_AB <= '02.10.2021'
        ) kp
WHERE ba.AUFTRAGSNR = 6908918
AND ba.ARTIKELNR = 14001
AND kp.Artikelnr = ba.ARTIKELNR
AND kp.ABNAHME_PREISKENNZEICHEN = ba.LIEFEREINHEIT
AND kp.Firma = 2 AND kp.KUNDENNR = 8141379

 

This was executable, when I tried the suggestion from @wunderdata I got an error because the CTE q1 referenced a columns from ba ( aka. for the correlation/the join ), so unless I messed up this doesn´t seem to work.

I would probably go for an actual group by instead of a window-function + a distinct, but maybe you have good reasons for
building the construct in such a way.
Also ( just personal preference ) I´d probably use the ANSI style join notation with "ba inner join kp on ....." , might end up more readable and with this SQL-86-notation I always get the feeling I´m implicitly condoning a cross join ( because everything in the FROM clause just floats about and somewhere in the WHERE clause I hope to find a correlation/join criteria 🙂  ).

Cheers,
Malte

View solution in original post

wunderdata
Contributor

Right you would have needed to replace the referenced ba,artikelnummer-column with the actual value too. Wrote that blind because i didn't have the tables 😉

pbreidenich
Padawan

Thanks for your help 💪