Skip to Content
0

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

Dec 06, 2017 at 05:54 PM

117

avatar image

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 !
SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Evgeniy Astafev Dec 07, 2017 at 05:08 AM
1

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.
Show 3 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

1

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 !

0
Sandra Rossi Dec 07, 2017 at 07:57 AM
2

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.

Share
10 |10000 characters needed characters left characters exceeded
Nic Teunckens Dec 06, 2017 at 09:43 PM
1

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.

Share
10 |10000 characters needed characters left characters exceeded