topic Re: inner joins in Application Development and Automation Discussions

inner joins

Former Member — Fri, 06 Oct 2006 10:09:35 GMT

i am looping at mchb and i need to join on the following conditions

i. Read table MKPF and retrieve MAX (MKPF-CPUDT) inner join MSEG based on MSEG-WERKS = MCHB-WERKS and MSEG-LGORT = MCHB-LGORT and MSEG-MATNR = MCHB-MATNR and MSEG-CHARG = MCHB-CHARG

ii. Read table MKPF and retrieve MKPF-BDATU MAX (MKPF-CPUTM) inner join MSEG WHERE MSEG-WERKS = MCHB-WERKS, and MSEG-LGORT = MCHB-LGORT and MSEG-MATNR = MCHB-MATNR and MSEG-CHARG = MCHB-CHARG and MKPF-CPUDT = above retrieved date

DETERMINE latest material doc for movement type (MSEG-BWART) 531 using above logic to get latest doc and get MSEG-AUFNR DETERMINE protein contact (AFKO-PLNBEZ) using MSEG-AUFNR

can any one help me out. its very urgent

raju

Re: inner joins

Former Member — Fri, 06 Oct 2006 10:23:17 GMT

Check the sample code:

data: begin of i_data occurs 0,

MBLNR type MKPF-MBLNR,

MJAHR type MKPF-MJAHR,

CPUDT type mkpf-cpudt,

ZEILE type MSEG-zeile,

end of i_data.

select a_{mblnr a}mjahr a_{cpudt b}zeile

into table i_data

from mkpf as a inner join mseg as b

on a_{MBLNR = b}MBLNR

<b> for all entries in i_mchb </b>

where b~WERKS = i_mchb-werks

and b~lgort = i_mchb-lgort

and b~matnr = i_mcbh-matnr

and b~charg = i_mcbh-charg.

Sort i_data by CPUDT Descending.

*U will get the latest record

*Follow the same step 2 select query also.

Regards,

Prakash.

Re: inner joins

Former Member — Fri, 06 Oct 2006 10:27:55 GMT

SELECT MAX(MKPF-CPUDT) MAX(MKPF-CPUTM) into (ldate, ltime)

from mkpf inner join mseg on

mkpf_{mblnr = mseg}mblnr and mkpf_{mjahr = mseg}mjahr

where mseg_{werks = mchb}werks and

mseg_{lgort = mchb}lgort and

mseg_{matnr = mchb}matnr and

mseg_{charg = mchb}charg.

Re: inner joins

Former Member — Fri, 06 Oct 2006 10:34:07 GMT

hi bankley,

thanks for ur answer but have seen it the second point should be based on above retrived data( from point 1).

look at it once again

Re: inner joins

Former Member — Fri, 06 Oct 2006 10:37:41 GMT

Raju,

No need to give 2 sepaerate select queries u can do that in a single query as said by anurag.

Regards,

Prakash.

Re: inner joins

Former Member — Fri, 06 Oct 2006 10:42:37 GMT

DETERMINE latest material doc for movement type (MSEG-BWART) 531 using above logic to get latest doc and get MSEG-AUFNR DETERMINE protein contact (AFKO-PLNBEZ) using MSEG-AUFNR

I am not 100% certain about your requirement whether you require all three information max-cpudt and time as well as docno for movement type 531 or it is just the docno for 531 movt type ??

select max(mblnr) into ldocno

from mblnr inner join mseg

on (same as above)

where mseg~bwart = '531'.

select afko~plnbez into lplnbez

from afko inner join mseg on

afko_{aufnr = mseg}aufnr

where mseg~mblnr = ldocno.

~~Please award points to all helpful answers!!~~

Message was edited by: Anurag Bankley