topic Re: inner join and outer join statement in Application Development and Automation Discussions

inner join and outer join statement

Former Member — Thu, 05 Apr 2007 04:25:47 GMT

Hi all,

can any one tell the code for joining vbap and vbpa.

i want item details from vbap based on vbpa-lifnr (broker for the sales order)

common field is vbeln. In one sale order there are no of lifnr (broker) for individual items.

i want all d items corresponding to lifnr(broker) in a sales order.

please help the same.

sure reward points sure.

Regards

Deepa

Re: inner join and outer join statement

Former Member — Thu, 05 Apr 2007 04:35:31 GMT

Hi Deepa ,

The code would look some think like this

select VBPA~LIFNR

VBAP_{vbeln VBAP}posnr vbap~matnr <add other feilds you want.

INTO TABLE <IT>

FROM VBPA INNER JOIN VBAP

ON VBPA_{VBELN = VBAP}VBELN

VBPA_{POSNR = VBAP}POSNR

WHERE

VBPA~VBELN = <SO Number> and

VBPA~LIFNR = <Vendor>.

Regards

Arun

Re: inner join and outer join statement

Former Member — Thu, 05 Apr 2007 04:39:31 GMT

select A~field1 ... B~field2 ...
         into table itab
         from vbap as A inner join
                 vbpa as B on
                 A~vbeln = B~vbeln and
                 A~lifnr = B~lifnr
                 where ...<>.

Re: inner join and outer join statement

Former Member — Thu, 05 Apr 2007 04:41:34 GMT

ThanQ one and all

Deepa

Re: inner join and outer join statement

Former Member — Thu, 05 Apr 2007 04:41:55 GMT

Hi,

VBPA contains all the partners for the sales document.

select VBPA~LIFNR

VBAP_{vbeln VBAP}posnr vbap~matnr .

INTO TABLE ITAB

FROM VBPA as a INNER JOIN VBAP as b

ON a_{VBELN = b}VBELN

a~POSNR = '000000' (don't join POSNR's of both simply check whether the POSNR in VBPA is maintained or not , because most of the times the POSNR in VBPA is 000000)

WHERE

a~VBELN = <SO Number> and

a~LIFNR = <Vendor>.

reward if useful

regards,

Anji

Re: inner join and outer join statement

Former Member — Thu, 05 Apr 2007 04:45:33 GMT

Hi Deepa,

Here is the code....

select-options:s_lifnr for vbpa-lifnr.

data: begin of itab occurs 0,

................,

end of itab.

select a~.........

a~.........

b~.........

into corresponding fields of table itab

from vbpa as a inner join vbap as b

on a_{lifnr = b}lifnr

where a~lifnr in s_lifnr.

Reward points if helpful.

Regards,

Ramana

Re: inner join and outer join statement

Former Member — Thu, 05 Apr 2007 04:47:11 GMT

Hi deepa,

Innerjoin:

select VBPA~LIFNR

VBAP_{vbeln VBAP}posnr vbap~matnr <add other feilds you want.

INTO TABLE ><IT>

FROM VBPA INNER JOIN VBAP

ON VBPA_{VBELN = VBAP}VBELN

VBPA_{POSNR = VBAP}POSNR

WHERE

VBPA~VBELN = <SO Number> and

VBPA~LIFNR = <Vendor>.

Outer join means for all entries.

select vbeln posnr from vbap into table itab where

vbeln = p_vbeln.

if not itab[] is initial.

select lifnr from vbpa into itab1

for allentries in itab

where vbeln = itab-vbeln.

Hope this helps you, reply for queries, Shall post you the updates.

Regards.

Kumar

Re: inner join and outer join statement

Former Member — Thu, 05 Apr 2007 04:49:57 GMT

Hi Deepa

please try this.

select <items> from vbap

into corresponding fields of itab

where vbpa-vbeln = vbap-vbeln and

vbap~lifnr = <vendor>

hope this will work for u.

please reward if helpful.

regards

ravish

Re: inner join and outer join statement

Former Member — Fri, 06 Apr 2007 09:32:21 GMT

ThanQ all

Regards

Deepa

Message was edited by:

Deepa KN

Re: inner join and outer join statement

Former Member — Fri, 06 Apr 2007 09:35:19 GMT

Hi All,

ThanQ

Regards

Deepa

Message was edited by:

Deepa KN