topic Re: Self join using ABAP Query in Application Development and Automation Discussions

Self join using ABAP Query

Former Member — Wed, 24 Jan 2007 07:30:21 GMT

Hello all,

How to create a self join using ABAP Query?

Say, I have this table EKBE with belnr (materials doc num) and lfbnr (reference material doc num). I have to pick the PO's from EKBE whose belnr doesnt have any lfbnr.

Regards

Madhumathi A

Re: Self join using ABAP Query

Former Member — Wed, 24 Jan 2007 07:35:25 GMT

in select option mention the condition that lfbnr = 0 and belnr <> 0.

this will fecth only the required

thanks

Shiva

Re: Self join using ABAP Query

Former Member — Wed, 24 Jan 2007 07:48:03 GMT

hi madhu,

tables : ekbe.

data itab like standard table of ekbe with header line.

select * from ekbe into table itab where belnr >< 0 and lfbnr = ' '.

loop at itab.

write 😕 itab-belnr,itab-lfbnr.

endloop.

check this code this works to select the orders whose belnr doesnt have any lfbnr

reward points if useful.

Re: Self join using ABAP Query

Former Member — Wed, 24 Jan 2007 07:53:32 GMT

Hi ,

My requirement is that,

I have to pick the PO's with movement type 103, which does not have a corresponding 104 document (which are not rejected) using ABAP query.

So, in this case I think a different logic should be used altogether.

Regards

Madhumathi A

Re: Self join using ABAP Query

Former Member — Wed, 24 Jan 2007 08:04:36 GMT

Hi,

Use the below select -

select *

from ekbe as a

into table itab

where not exists ( select * from ekbe

where ifbnr eq a~belnr ).

Hope it works..

Reward points if found useful...!

Cheers

Abhishek

Re: Self join using ABAP Query

Former Member — Wed, 24 Jan 2007 08:15:24 GMT

Try out this code.

select-options:

s_belnr for ekbe-belnr.

data : begin of it_ekbe occurs 0,

ebeln like ekbe-ebeln,

belnr like ekbe-belnr,

end of it_ekbe.

select distinct a_{ebeln a}belnr

into corresponding fields of table it_ekbe

from ekbe as a inner join ekbe as b

on a_{belnr <> b}lfbnr

where a~belnr in s_belnr and

b~belnr in s_belnr.

HOPE THIS WILL HELP YOU.

Regs

Nimesh.

Re: Self join using ABAP Query

Former Member — Wed, 24 Jan 2007 09:44:24 GMT

Thanks all of u. I have got the solution..

Regards

Madhumathi A

Re: Self join using ABAP Query

Former Member — Mon, 19 Sep 2016 09:45:13 GMT

It would be helpful for me, if you post your solution that you found for your problem.