Re: If selection results in too many rows

[Marek Zyskowski]

Well the result is close.

SALES_DOCUMENT / ITEM / EU / US / E1
000156 / 001 / EU / 0 / 0 /
000156 / 001 / 0 / US / 0 /
000156 / 001 / 0 / 0 / E1 /
003344 / 005 / EU / 0 / 0 /
003344 // 005 / 0 / US / 0 /

You should not have to over think this. Basically you want the same result but grouped together. You can assume that the value EU, US and E1 is greater than zero.

So create an alias for the result you created like so:


(SELECT A.VBELN AS SALES_DOCUMENT,
A.POSNR as ITEM,
if(D.GEGRU='EU', D.ALNUM,'0') as EU,
if(D.GEGRU='US', D.ALNUM,'0') as US,
if(D.GEGRU='E1', D.ALNUM,'0') as E1
FROM TABLE1 A
LEFT JOIN TABLE2 D
on A.MATNR=D.MATNR
and D.ALAND='BE' ) ALIAS1

Then select from this ALAIS1 result with a grouping and a max or min function (Depends on your sort order).

Select ALIAS1.SALES_DOCUMENT,
ALIAS1.ITEM,
min(ALIAS1.EU),
min(ALIAS1.US),
min(ALIAS1.E1)
from
(SELECT A.VBELN AS SALES_DOCUMENT,
A.POSNR as ITEM,
if(D.GEGRU='EU', D.ALNUM,'0') as EU,
if(D.GEGRU='US', D.ALNUM,'0') as US,
if(D.GEGRU='E1', D.ALNUM,'0') as E1
FROM TABLE1 A
LEFT JOIN TABLE2 D
on A.MATNR=D.MATNR
and D.ALAND='BE' ) ALIAS1
GROUP BY ALIAS1.SALES_DOCUMENT,
ALIAS1.ITEM