topic Re: [SQL/ABAP] Best practice for selection in multiple tables ? in Application Development and Automation Discussions

[SQL/ABAP] Best practice for selection in multiple tables ?

thibault_gauthier — Wed, 06 Dec 2017 17:54:20 GMT

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 !

Re: [SQL/ABAP] Best practice for selection in multiple tables ?

NTeunckens — Wed, 06 Dec 2017 21:43:20 GMT

I would suggest :

Check if you can get the required Output by using the "BOM API's", such as FM "CS_BOM_EXPL_MAT_V2" / "CSAP_MAT_BOM_READ" / "CAVC_C_GET_BOM_HEADER_DATA" / "CAVC_C_GET_BOM_ITEM_DATA" ...
Found a Report that uses MAST - STKO - STPO - STAS : link
Some old content on SDN (.PDF "BOM Header and Item Extraction in SAP - Business Intelligence") : link

I hope this helps ...

Kind regards

Nic T.

Re: [SQL/ABAP] Best practice for selection in multiple tables ?

former_member210008 — Thu, 07 Dec 2017 05:08:07 GMT

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.

Re: [SQL/ABAP] Best practice for selection in multiple tables ?

Sandra_Rossi — Thu, 07 Dec 2017 07:57:46 GMT

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.

Re: [SQL/ABAP] Best practice for selection in multiple tables ?

thibault_gauthier — Thu, 07 Dec 2017 08:46:06 GMT

Thank you very much this is exactly the kind of things I searched for !

Re: [SQL/ABAP] Best practice for selection in multiple tables ?

RaymondGiuseppi — Thu, 07 Dec 2017 15:38:33 GMT

Don't forget, in case of join, to remove duplicates.

Re: [SQL/ABAP] Best practice for selection in multiple tables ?

thibault_gauthier — Tue, 12 Dec 2017 10:08:44 GMT

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 !