Join+group by

[Former Member]

Hi experts,

I m generating a report in which i have to track all materails with PP whether there PO's are issued or not.For this i put left join b/w eben & ekebe.In where clause . i fetch data on basis of plant & PR creation date((getting data) .

But i have a problem that in second table , i have to fetch only those data whose movement type is maximum .for this i put max function , but i m not getting what to put in where clause.

Plz tell me the code, i want to put in group by clause only fields from second table(ekbe), but it is shoeing error, it is asking to put all fields from both the tables

select a_{banfn a}ekgrp a_{afnam a}txz01 a_{matnr a}werks a_{lgort a}bednr

a_{menge a}meins a_{badat a}frgdt a_{ebeln a}ebelp b_{ebeln b}ebelp

b_{belnr max( b}bwart ) b_{budat b}menge b~matnr

b~werks

from ( eban as a left outer join ekbe as b on

a_{ebeln eq b}ebeln and

a_{ebelp eq b}ebelp )

into corresponding fields of

table eban_ekbe

where a~ekgrp in s_ekgrp and

a~afnam in s_afnam and

a~matnr in s_matnr and

a~werks eq p_werks and

a~lgort in s_lgort and

a~badat in s_badat

group by

b_{ebelp b}belnr b_{bwart b}budat b_{menge b}matnr b~werks.

Definitely rewards pts.

Savita

[Former Member]

Hi,

If u r using aggregate functions liek max, sum etc, we need to use all fields in the gruop by clause other than used in aggregate.

Reward appropriate points

Regards,

Mansi.

[Former Member]

Hi

You hav to use max as follows,

C4A = '000'.

SELECT * FROM T100

WHERE SPRSL = 'D' AND

ARBGB = '00'.

CHECK: T100-MSGNR > C4A.

C4A = T100-MSGNR.

ENDSELECT.

SELECT MAX( MSGNR ) FROM T100 INTO C4A

WHERE SPRSL = 'D' AND

ARBGB = '00'.

Regards,

Jagadish