12-06-2017 5:54 PM
Hello everybody,
I would like to know what is the best practice to select data in several tables that are linked by keys and exploit the result of each table individually.
Example: What is the best practice that goes on between *************************
data: lt_mast standard type table of mast,
lt_stko standard type table of stko,
lt_stpo standard type table of stpo,
lt_draw standard type table of draw.
*************************
* what is the best practice for filling lt_mast (for example werks 0001), lt* _stko (bound in mast-matnr), lt_stpo (bound in stko-stlnr / stko-stlty * * * ...), lt_draw (bound in stpo-doknr).
select * from mast where " ...
loop at lt_mast " ...
" constuction of a range lr_matnr
endloop.
select * from where matnr in lr_matnr " ...
loop at stko into ls_stko.
" I can not do range on two related fields ..
" if not another request on stpo in join on stpo ...
select * from stpo appending table lt_stpo where
stlnr = ls_stko-stlnr and stlty = ls_stko-stlty.
endloop
" etc ...
" we can not make a single query that will fill all tables at once / or a b" " i" g join that can be split into each table? ... ?? I need help
*************************
" lt_mast lt .... filled tables
Thanks a lot !
12-07-2017 5:08 AM
If you're on 7.40 SP08 or higher you can do this:
SELECT stko~*, stpo~*
INTO TABLE @DATA(lt)
FROM stko JOIN stpo ON stko~stlnr = stpo~stlnr
AND stko~stlty = stpo~stlty.
Table lt will be created with structure
TYPES:BEGIN OF ztype,
stko TYPE stko,
stpo TYPE stpo,
END OF ztype.
So now you just loop at lt and fill your tables
LOOP AT lt INTO ls.
APPEND ls-stko TO lt_stko.
APPEND ls-stpo TO lt_stpo.
ENDLOOP.
12-06-2017 9:43 PM
I would suggest :
I hope this helps ...
Kind regards
Nic T.
12-07-2017 5:08 AM
If you're on 7.40 SP08 or higher you can do this:
SELECT stko~*, stpo~*
INTO TABLE @DATA(lt)
FROM stko JOIN stpo ON stko~stlnr = stpo~stlnr
AND stko~stlty = stpo~stlty.
Table lt will be created with structure
TYPES:BEGIN OF ztype,
stko TYPE stko,
stpo TYPE stpo,
END OF ztype.
So now you just loop at lt and fill your tables
LOOP AT lt INTO ls.
APPEND ls-stko TO lt_stko.
APPEND ls-stpo TO lt_stpo.
ENDLOOP.
12-07-2017 8:46 AM
Thank you very much this is exactly the kind of things I searched for !
12-07-2017 3:38 PM
12-12-2017 10:08 AM
Yes, I took a little time to understand the exact operation, with the inner joins, external left / right. And it's true that we can end up with a lot of recordings !
12-07-2017 7:57 AM
General answer: always prefer a join. Make sure to use the indexes (if not, ask the database expert). It also dépends on the quantity of data you need to extract. Then a database expert does performance tests on real data and adjusts the query if necessary, or adjusts things on the database (index, statistics, etc.), and sometimes a rewrite of some portions of the program may be needed.