topic Re: Join fails between tables !! in Application Development and Automation Discussions

Join fails between tables !!

Former Member — Tue, 21 Aug 2012 13:26:35 GMT

Hi all,

The below join statement fails to retrieve necessary data. For every PO number(ebeln) in EKBE table, multiple entries of document numbers(belnr) will be found in RSEG table. But the below statement returns zero entries.

IF NOT it_ekpo[] IS INITIAL.

SELECT a~ebeln "PO number

a~ebelp

a~zekkn

a~vgabe

a~gjahr

a~belnr "Document Number

a~buzei

b~menge

b~shkzg

b~wrbtr

INTO CORRESPONDING FIELDS OF TABLE it_ekbe

FROM ekbe AS a

INNER JOIN rseg as b

ON a~ebeln = b~ebeln

AND a~ebelp = b~ebelp

AND a~buzei = b~buzei

AND a~gjahr = b~gjahr

AND a~belnr = b~belnr

For all entries in it_ekpo

WHERE a~ebeln = it_ekpo-ebeln

AND a~ebelp = it_ekpo-ebelp.

ENDIF.

Please suggest. Thanks.

Hema.

Re: Join fails between tables !!

Former Member — Tue, 21 Aug 2012 13:52:51 GMT

Hi,

The code in which you have written does not contain common key, i.e "b~ebeln" is not been written and in inner join you taken

ON a~ebeln = b~ebeln

AND a~ebelp = b~ebelp

AND a~buzei = b~buzei

AND a~gjahr = b~gjahr

AND a~belnr = b~belnr

Re: Join fails between tables !!

sjeevan — Tue, 21 Aug 2012 15:43:48 GMT

Your table join seems right. I suggest you to check if it_ekpo is empty or if it's not empty it may not have history (no entries in EKBE for the corresponding EBELN of it_ekpo) or no invoices (no entries in RSEG for the corresponding EBELN of it_ekpo) .

Re: Join fails between tables !!

amy_king — Tue, 21 Aug 2012 17:08:25 GMT

Hi Hema,

Use a LEFT OUTER JOIN to join RSEG to EKBE instead of an INNER JOIN.

Cheers,

Amy

Re: Join fails between tables !!

Former Member — Wed, 22 Aug 2012 11:05:22 GMT

Thanks Amy... LEFT OUTER JOIN worked.

Re: Join fails between tables !!

Former Member — Sat, 08 Sep 2012 08:28:07 GMT

hi sundar

Use more conditions to increase the speed of the program because for given vendor der are many po nums so that to increase the speed use more condtions and selection parameters.and use for all entries.

Regards

R Sai Kiran