splitting the huge SELECT statement

[former_member649905]

Hi Gurus,

I need help in order to split the huge SELECT statement into individual select for each table (AFKO, AFVC)...I don't have to much experience into that, could someone guide me into or provide finished code?

Thanks

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.

Regards

[Former Member]

What do you mean "split" and why do you want to do this?

Rob

[former_member649905]

Thanks for all your input guys,

Tables like AFKO, AFVC.. would be archived using related archiving object and indexed with the PBS functionality... client has PBS archive add ons installed.

The requirement is to develop a generic extrator based on function module capable to fetch online data as well as archived data.

PBS has a conversion tool (not very strong tool) that doesn't understand certain statements like join, get...

So I need to rewrite those programs (function module), to split the huge SELECT statement (get rid of the joins condition) into individual SELECT for each table statements before going ahead in converting them(manual conversion or help of conversion tool).

Could you kindly look into splitting the huge SELECT statement into individual SELECT for each table?

Regards

Edited by: Blaiso on Jun 15, 2011 7:29 PM

[Former Member]

you can use for all entries option like this...

" first select data from first table

data: it_table1 type table of afko .

SELECT

t1~mandt

t1~aufnr

t1~aufpl

FROM afko as t1

WHERE t1~mandt = sy-mandt

AND t1~aufnr LIKE '00006%'

AND t1~aufnr IN l_r_aufnr into table it_table1 .

" then we ll select the data from second table using the entries selected from the first table and field conditions for second tabel itself.

data: it_table2 type table of afvc .

select

t2~aplzl

t2~banfn

t2~bnfpo

t2~vornr

t2~sumnr

t2~prctr

from afvc as t2 into table it_table2

for all entries in it_table1

where it_table1-mandt = t2~mandt

and it_table1-aufpl = t2~aufpl

and t2~prctr = '0000005080'

AND t2~sumnr '00000000'

AND t2~steus IN l_r_steus "D02K924627 .

" then we ll select the data from third table using the entries selected from the first and second table and field conditions for second tabel itself.

data: it_table3 type table of mseg .

select

t3~ebeln AS ebeln

t3~ebelp AS ebelp

t3~dmbtr AS dmbtr "D02K924378

t3~lifnr AS lifnr

t3~waers AS waers "D02K924378

t3~sakto AS kstar "D02K924456

from mseg AS t3

for all entries in it_table2

where it_table2-mandt = t3~mandt

and it_table2-aufpl = t3~aufpl

and it_table2-aplzl = t3~aplzl .

like wise u can do for all the tables you have...

Edited by: prashanti korlepara on Jun 16, 2011 3:41 PM

[former_member649905]

Thanks so much for all your input and specialy to prashanti... You resolved my issue---Pts assigned

[Former Member]

for clear idea you can create a structure or you can use for all entries concept there you can get clear cut idea . combining these many d.b tables you may get garbage values

thanking you

[Former Member]

I'm curious how long it'd take you to run this statement. Normally, I'd prefer to join 2 db tables into a global internal table, then select data from the rest of the db tables with this internal table basing on all entries in this table.

[Former Member]

You should use multiple internal tables and perform "FOR ALL ENTRIES". That will improve the performence of your code. You should not perfrom this much of joins for ensuring better DB performence. Hope wou will change your thoughts.

Regards

Gaurav

[Former Member]

You should use multiple internal tables and perform "FOR ALL ENTRIES". That will improve the performence of your code. You should not perfrom this much of joins for ensuring better DB performence. Hope wou will change your thoughts.
> 
> Regards
> Gaurav

This is incorrect.

Rob