topic Re: inner join (multiple tables access) in Application Development and Automation Discussions

inner join (multiple tables access)

former_member649905 — Mon, 13 Jun 2011 19:34:45 GMT

Hi Experts,

Could you pl look into the code and let me now what that inner join is doing? I am thinking that data from all tables AFKO, AFVC, MSEG, EKBE, ESLL, T430 are selected and extracted but someone told me without further explanations that data are extracted only from AFKO, AFVC. From MSEG, EKBE, ESLL the join is only doing a validation check and I am not understanding that.

Below is the code.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT

t1~mandt AS mandt

t1~aufnr AS aufnr

t1~aufpl AS aufpl

t2~aplzl AS aplzl

t2~banfn AS banfn

t2~bnfpo AS bnfpo

t3~ebeln AS ebeln

t3~ebelp AS ebelp

t4~vgabe AS vgabe

t4~belnr AS belnr

t4~buzei AS buzei

t4~gjahr AS gjahr

t4~zekkn AS zekkn

t4~srvpos AS srvpos

t6~menge AS menge "D02K924592

t3~dmbtr AS dmbtr "D02K924378

t4~cpudt AS cpudt

t4~shkzg AS shkzg

t4~budat AS budat

t3~lifnr AS lifnr

t2~vornr AS vornr_sactv

t7~vornr AS vornr_actv

t2~sumnr AS sumnr

t2~prctr AS prctr

t6~meins AS meins "D02K924592

t3~waers AS waers "D02K924378

t3~sakto AS kstar "D02K924456

t6~packno AS packno "D02K924592

t6~introw AS introw "D02K924592

FROM afko AS t1

INNER JOIN afvc AS t2 ON t2_{mandt = t1}mandt

AND t2_{aufpl = t1}aufpl

INNER JOIN mseg AS t3 ON t3_{mandt = t2}mandt "D02K924373

AND t3_{aufpl = t2}aufpl "D02K924373

AND t3_{aplzl = t2}aplzl "D02K924373

INNER JOIN ekbe AS t4 ON t4_{mandt = t3}mandt

AND t4_{ebeln = t3}ebeln

AND t4_{ebelp = t3}ebelp

AND t4_{zekkn = t3}zekkn "D02K924373

AND t4~vgabe = 1 "D02K924373

AND t4_{gjahr = t3}mjahr "D02K924373

AND t4_{belnr = t3}mblnr "D02K924373

AND t4_{buzei = t3}zeile "D02K924373

INNER JOIN t430 AS t5 ON t5_{mandt = t2}mandt "D02K924489

AND t5~plnaw = '*' "D02K924489

AND t5_{steus = t2}steus "D02K924489

AND t5~service = 'X' "D02K924489

LEFT OUTER JOIN esll AS t6 ON t6_{mandt = t4}mandt "D02K924592

AND t6_{packno = t4}packno "D02K924592

AND t6_{introw = t4}introw "D02K924592

INNER JOIN afvc AS t7 ON t2_{mandt = t7}mandt

AND t2_{aufpl = t7}aufpl

AND t2_{sumnr = t7}aplzl

WHERE t1~mandt = sy-mandt

AND t1~aufnr LIKE '00006%'

AND t1~aufnr IN l_r_aufnr

AND t2~prctr = '0000005080'

AND t2~sumnr <> '00000000'

AND t4~vgabe = 1

AND t4~cpudt IN l_r_cpudt

AND t2~steus IN l_r_steus "D02K924627

AND t5~steus IN l_r_steus. "D02K924627.

Thanks

Re: inner join (multiple tables access)

former_member212713 — Tue, 14 Jun 2011 06:17:14 GMT

Hi Blaiso;

I dont know Why did use this method? Because this method not healthy and has more performance problem.

If you want to more performance you add some index to tables.

If you want to "Correct Result", You must know tables relation between tables, according to your specific scenario.

I suggest to you, use loop method and itab at this status.

You can examine bellow way

Se38->Environment->Performance Example

Best regards.

Re: inner join (multiple tables access)

former_member649905 — Tue, 14 Jun 2011 14:14:03 GMT

Hi Melih,

Thanks for replying, this program fetch online data (in R/3) and pass them to the setup tables for BW. Now certain tables (in R/3)would be archived based on archiving like PS_Project, MM_Matbel...

Generic datasource need to be put in place in case archived data + online data may need to be load (full load) to BW.

Archived data are indexed using PBS functionality

I would like to now exactly from which tables(with explanation please), data are extrated on that program.

Regards.

Re: inner join (multiple tables access)

former_member649905 — Wed, 15 Jun 2011 13:05:18 GMT

Issue clarified.

Thanks

Re: inner join (multiple tables access)

former_member649905 — Wed, 15 Jun 2011 13:06:00 GMT

Answered by myself