ABAP Best Pratice for getting data from multiple internal tables

[former_member276617]

Hello,

I'm looking for the best practice to get data from multiple tables into an internal table. I'll give a simple example in which there is a selection screen where a user can put in a purchase document and wants to get some header data from table EKKO ( fields ebeln and lifnr), and some data at the item level with table EKPO (field werks). I've seen threads before discussing using joins, loops to append this data into a third internal table, but these seem to come with really bad performance and I'm just not sure what the best approach is. If someone could provide an answer with actually code and explaination that would be very helpful.

[matt]

Since you can define internal tables as HASHED or SORT, and to have key and secondary key access, there should not be any performance issues.

https://blogs.sap.com/2014/03/18/first-real-use-of-secondary-indexes-on-an-internal-table/

(Oh, and if anyone comes along and says "use parallel cursor technique", they're using old techniques that are no longer really necessary.

[former_member276617]

Hello Matt,

Thanks for your reply... So are you saying the join and/or looping method is ok as long as you're defining the internal table as HASHED / SORT? Would join be better then loop within a loop etc? Just to give some sample code, see the below with looping method. The internal tables aren't marked with HASHED / SORT, but if they were would something like that be a way to get this data, or join ?

TABLES: ekko,ekpo.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS:
so_ebeln FOR ekko-ebeln. " PO Number

SELECTION-SCREEN END OF BLOCK b1.

START-OF-SELECTION.

TYPES:

BEGIN OF my_podata,
ebeln TYPE ekko-ebeln,
lifnr TYPE ekko-lifnr,
werks TYPE ekpo-werks,
matnr TYPE ekpo-matnr,
END OF my_podata.

DATA:

lt_ekko TYPE TABLE OF ekko,
lt_ekpo TYPE TABLE OF ekpo,
lt_podata TYPE TABLE OF my_podata,
ls_podata TYPE my_podata.



FIELD-SYMBOLS:
<ls_ekko> TYPE ekko,
<ls_ekpo> TYPE ekpo,

SELECT * FROM ekko INTO TABLE lt_ekko
WHERE ebeln IN so_ebeln.

IF sy-subrc = 0.

SELECT * FROM ekpo INTO TABLE lt_ekpo FOR ALL ENTRIES IN lt_ekko WHERE ebeln = lt_ekko-ebeln.

ENDIF.

LOOP AT lt_ekko ASSIGNING <ls_ekko>.
LOOP AT lt_ekpo ASSIGNING <ls_ekpo>.

ls_podata-ebeln = <ls_ekko>-ebeln.
ls_podata-lifnr = <ls_ekko>-lifnr.
ls_podata-werks = <ls_ekpo>-werks.
ls_podata-matnr = <ls_ekpo>-matnr.

APPEND ls_podata TO lt_podata.

ENDLOOP.

ENDLOOP.

[matt]

I'm talking about merging internal tables that are already populated. Not how to populate them in the first place. Of course it is far better to use a database JOIN into a single internal table, than two selects into an internal table each, and then merge the two tables into one.

And as has been discussed many times, JOINs are in most cases entirely preferable to FOR ALL ENTRIES.

Looping is fine so long as you are access the tables in an optimal way. TYPE TABLE is not optimal. Define your internal tables as precisely as possible. HASHED for preference, SORTED for most other cases, STANDARD if all else fails. If you need alternate access to an internal table, create a secondary key on it.

[former_member182550]

I'm with Matt on that - use the database for what it's best at, and a well crafted join has minimal performance impact.

Rich

[former_member276617]

Thanks Matt & Richard for your helpful info - appericate the information.

[Jelena]

+1 to Matt. First of all: use the right terminology. EKKO/EKPO are transparent tables. Internal tables exist in memory to hold the data while the program runs. Not sure if the word "table" is causing confusion here, but SAP is notorious for this, so get used to it.

You've mentioned reading many posts but they seem to be on the different subjects. Reading data from DB (e.g. transparent tables) into internal tables is one thing. Reading/manipulating internal tables in the program is another. In ABAP Help, you'll also find the respective commands described in two different sections.

As Matt said, general best practice is to get DB do what it does best and to use the appropriate types for the internal tables (you'll do well if you look up Matt's blogs on SCN through his profile). If there is still confusion then you might want to go to the example section in ABAP Editor and get a new ABAP for Beginners book from SAP Press. Of course, great way to learn is also just to try different things in a test system.