possible to join multiple internal tables ?

[Former Member]

Is it possible to join internal tables like this:

itab1:

fields are matnr f1 f2

itab2:

fields are matnr f3 f4

itab3:

fields are matnr f5 f6

required final-itab:

fields are - matnr f1 f2 f3 f4 f5 f6

(which are joined by matnr)

Thanks.

[Former Member]

Yes, you will need a final internal table which has all fields. Does ITAB2 and ITAB3 have multiple records for a MATNR? If so, then you will need to LOOP at them, otherwise you can just READ TABLE.

Loop at itab1.

clear final_itab.
final_itab-f1 = itab1-f1.
final_itab-f2 = itab1-f2.

read table itab2 with key matnr = itab1-matnr.
if sy-subrc = 0.
final_itab-f3 = itab1-f3.
final_itab-f4 = itab1-f4.
endif.

read table itab3 with key matnr = itab1-matnr.
if sy-subrc = 0.
final_itab-f5 = itab1-f5.
final_itab-f6 = itab1-f6.
endif.

append final_itab.


endloop.

Regards,

Rich Heilman

[Former Member]

hi Nuren,

join statements doesn't work on internal tables as joins are used for database table

[RichHeilman]

Yes, you will need a final internal table which has all fields. Does ITAB2 and ITAB3 have multiple records for a MATNR? If so, then you will need to LOOP at them, otherwise you can just READ TABLE.

Loop at itab1.

clear final_itab.
final_itab-f1 = itab1-f1.
final_itab-f2 = itab1-f2.

read table itab2 with key matnr = itab1-matnr.
if sy-subrc = 0.
final_itab-f3 = itab1-f3.
final_itab-f4 = itab1-f4.
endif.

read table itab3 with key matnr = itab1-matnr.
if sy-subrc = 0.
final_itab-f5 = itab1-f5.
final_itab-f6 = itab1-f6.
endif.

append final_itab.


endloop.

Regards,

Rich Heilman

[RichHeilman]

You can not use INNER JOIN on internal tables, but you can use INNER JOIN in your select statement and try to get all of the fields all at one shot. It depends on what fields and tables that you need.

Regards,

Rich Heilman

[Former Member]

hi Rich,

Thanks for correcting me for a spunk of moment i forgot that he has a requirement for joining Internal Tables. Nuren follow the method suggested by Rich it works...

Hi Nuren,

Please close this thread

in case if your problem is resloved.

Regards,

Santosh

[Former Member]

Rich,

Thank you for your sample code. Please take a moment to answer this question.

Suppose if the itab3 does not have a matnr as of itab1 (in that case, sy-subrc NE 0 while reading itab3 in your sample program). Now if I still want this itab3 record to be appended to final itab, can it be worked?

I mean:

If the 3 internal tables have same matnrs, I would like to join it and create that record in final itab with all fields f1 f2 f3 f4 f5 f6.

If the 2 internal tables have same matnrs, I would still like to join them and create that record in final itab with field f5 and f6 as empty.

I am guessing that some matnrs of other itabs (Which are not present in itab1) may be neglected seeing your program sample. It's my mistake not to put up question in complete picture.

Thanks a lot.

[RichHeilman]

THe way my sample is coded, if there is no match in ITAB3, it will still create the record in FINAL_ITAB, but F5 and F6 will be empty.

If a MATNR does not exits in ITAB1, then no record will be created in FINAL_ITAB.

REgards,

Rich Heilman

[Former Member]

Rich, Thanks.

What if I want to create a record in final-itab even if matnr does n't exist in itab1.

For ex: if all the 3 itabs have different matnrs, I would still like to create a final record in final-itab with those matnr values and field values empty from whichever itabs those matnrs does n't exist.

Hopefully I am clear.

Thank you very much.

[RichHeilman]

Ok, then we need to take all three ITABs and get all of the MATNRs into another internal table, then loop at this internal table and read the records for ITAB1, ITAB2, and ITAB3.

data: begin of imatnr occurs 0,
      matnr type mara-matnr,
      end of imatnr.

loop at itab1.
  imatnr-matnr = itab1-matnr.
  collect imatnr.
endloop.
loop at itab2.
  imatnr-matnr = itab2-matnr.
  collect imatnr.
endloop.
loop at itab3.
  imatnr-matnr = itab3-matnr.
  collect imatnr.
endloop.




Loop at imatnr.

clear final_itab.


final_itab-matnr = imatnr-matnr.

clear itab1.
read table itab1 with key matnr = imatnr-matnr.
if sy-subrc = 0.
final_itab-f1 = itab1-f1.
final_itab-f2 = itab1-f2.
endif.

clear itab2.
read table itab2 with key matnr = imatnr-matnr.
if sy-subrc = 0.
final_itab-f3 = itab2-f3.
final_itab-f4 = itab2-f4.
endif.

clear itab3.
read table itab3 with key matnr = imatnr-matnr.
if sy-subrc = 0.
final_itab-f5 = itab3-f5.
final_itab-f6 = itab3-f6.
endif.

append final_itab.


endloop. 

REgards,

Rich Heilman