topic Re: possible to join multiple internal tables ? in Application Development and Automation Discussions

possible to join multiple internal tables ?

Former Member — Mon, 26 Jun 2006 22:05:40 GMT

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.

Re: possible to join multiple internal tables ?

Former Member — Mon, 26 Jun 2006 22:06:44 GMT

hi Nuren,

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

Re: possible to join multiple internal tables ?

RichHeilman — Mon, 26 Jun 2006 22:09:38 GMT

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

Re: possible to join multiple internal tables ?

RichHeilman — Mon, 26 Jun 2006 22:10:57 GMT

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

Re: possible to join multiple internal tables ?

Former Member — Mon, 26 Jun 2006 22:16:11 GMT

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

Re: possible to join multiple internal tables ?

Former Member — Mon, 26 Jun 2006 22:24:28 GMT

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.

Re: possible to join multiple internal tables ?

RichHeilman — Mon, 26 Jun 2006 22:29:50 GMT

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

Re: possible to join multiple internal tables ?

Former Member — Mon, 26 Jun 2006 22:34:00 GMT

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.

Re: possible to join multiple internal tables ?

RichHeilman — Mon, 26 Jun 2006 22:39:23 GMT

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