Skip to Content

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

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 !
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Dec 07, 2017 at 05: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.
    Add comment
    10|10000 characters needed characters exceeded

  • Dec 07, 2017 at 07: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.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 06, 2017 at 09:43 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded