topic Re: joins in Application Development and Automation Discussions

joins

Former Member — Thu, 22 Feb 2007 08:18:09 GMT

Hi all ,

Please provide the following query using inner join.

SELECT ebeln bstyp bsart

FROM ekko

INTO TABLE t_ekko

WHERE bstyp = c_bstyp AND

bsart = c_bsart.

IF NOT t_ekko[] IS INITIAL.

SELECT mblnr mjahr zeile bwart matnr werks ebeln

FROM mseg

INTO TABLE t_r_mseg

FOR ALL ENTRIES IN t_ekko

WHERE ebeln = t_ekko-ebeln AND

bwart IN s_bwart1 AND

matnr IN s_matnr AND

werks IN s_werks.

endif.

Re: joins

Former Member — Thu, 22 Feb 2007 08:21:24 GMT

select a_{ebeln a}bstyp a_{bsart mblnr b}mjahr b_{zeile b}bwart b_{matnr b}werks

from ekko as a inner join mseg as b on a_{ebeln = b}ebeln WHERE a_{bstyp = a}c_bstyp AND

a~bsart = c_bsart AND

b~bwart IN s_bwart1 AND

b~matnr IN s_matnr AND

b~werks IN s_werks.

regards

shiba dutta

Re: joins

seshatalpasai_madala — Thu, 22 Feb 2007 08:23:04 GMT

Hi,

Try this query.

SELECT mblnr mjahr zeile bwart matnr werks ebeln

FROM mseg INNER JOIN ekko ON mseg-ebeln = ekko-ebeln

INTO TABLE t_r_mseg

bwart IN s_bwart1 AND

matnr IN s_matnr AND

werks IN s_werks.

Regards,

Sesh

Re: joins

Former Member — Thu, 22 Feb 2007 08:26:49 GMT

SELECT ekko_{ebeln ekko}bstyp ekko~bsart

mseg_{mblnr mseg}mjahr mseg_{zeile mseg}bwart mseg_{matnr mseg}werks

INTO TABLE t_ekko

FROM ekko inner join mseg

on ekko_{ebeln = mseg}ebeln

WHERE ekko~bstyp = c_bstyp AND

ekko~bsart = c_bsart and

mseg~bwart IN s_bwart1 AND

mseg~matnr IN s_matnr AND

mseg~werks IN s_werks.

Re: joins

Former Member — Thu, 22 Feb 2007 08:29:55 GMT

shweta,

SELECT EKKO_{EBELN EKKO}bstyp EKKO~bsart

MSEG_{EBELN MSEG}mblnr MSEG~mjahr

MSEG_{zeile MSEG}bwart MSEG_{matnr MSEG}werks

MSEG~ebeln

INTO TABLE i_itab

FROM ( EKKO

INNER JOIN MSEG

ON MSEG_{EBELN = EKKO}EBELN )

WHERE EKKO~bstyp eq c_bstyp AND

EKKO~bsart eq c_bsart AND

MSEG~ebeln = t_ekko-ebeln AND

MSEG~bwart IN s_bwart1 AND

MSEG~matnr IN s_matnr AND

MSEG~werks IN s_werks.

Note here i_itab should have all the fileds which and all you are selecting

Pls. Mark if useful

Re: joins

anilnal — Thu, 22 Feb 2007 08:35:09 GMT

Hi,

You cannot link EKKO and MSEG directly..

Fetch the PO item details from EKPO and then the PO History for EKBE.

Then select the fields you require from MSEG for all entries in EKBE where mblnr eq ekbe-belnr and mjhar eq ekbe-gjahr and zeile eq ekbe-buzei.

Anil.

Correction: this is only in case you need to pick material docs for GR/IR individually

Message was edited by:

Anil