Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Newbie: Joining internal tables

Former Member
0 Kudos

I would like to join two internal tables, but it looks like I cannot run INNER JOIN / LEFT OUTER JOIN statements on internal tables. Is there an efficient way of achieving the same result or would I be better off creating physical tables and join them that way.

The tables have roughly 10000 records and about 15 colums each which I think would be better dealt with in memory rather that going back to the database. Any thoughts?

Here is the code:

DATA: BEGIN OF WA_USREPORT,

WA_US_GLOBN TYPE ZCA_PRODUCT-GLOBN,

WA_US_GLOBT TYPE ZCA_PRODUCT-GLOBT,

WA_US_DESCR TYPE ZCA_PRODUCT-DESCR,

WA_US_INTOR TYPE ZCA_PRODUCT-INTOR,

WA_US_ACT TYPE ZCA_PRODUCT-ACT,

WA_US_TUOME TYPE ZCA_PRODUCT-TUOME,

WA_US_USITM TYPE ZCA_USITM-USITM,

WA_US_USEUMAT TYPE ZCA_USITM-USEUMAT,

WA_US_GPSUOMC TYPE ZCA_USITM-GPSUOMC,

WA_US_GPSDENOM TYPE ZCA_USITM-GPSDENOM,

WA_US_GPSNUMER TYPE ZCA_USITM-GPSNUMER,

WA_US_USPLANT TYPE ZCA_USITM-USPLANT,

WA_US_USITMDESC TYPE /BIC/TUSITM-TXTMD,

WA_US_USDIVSN TYPE /BIC/PUSITM-/BIC/USDIVSN,

WA_US_USERL TYPE /BIC/PUSITM-/BIC/US_ERL,

WA_US_USSTKTYP TYPE /BIC/PUSITMSTR-/BIC/USSTKTYP,

WA_US_USSUPNO TYPE /BIC/PUSITMSTR-/BIC/USSUPNO,

END OF WA_USREPORT,

TBL_USREPORT LIKE TABLE OF WA_USREPORT WITH HEADER LINE.

DATA: BEGIN OF WA_EUREPORT,

WA_EU_GLOBN TYPE ZCA_PRODUCT-GLOBN,

WA_EU_GLOBT TYPE ZCA_PRODUCT-GLOBT,

WA_EU_EUMAT TYPE ZCA_EUMAT-EUMAT,

WA_EU_USITM TYPE ZCA_EUMAT-USITM,

WA_EU_GPSUOMC TYPE ZCA_EUMAT-GPSUOMC,

WA_EU_GPSDENOM TYPE ZCA_EUMAT-GPSDENOM,

WA_EU_GPSNUMER TYPE ZCA_EUMAT-GPSNUMER,

WA_EU_EUMATTXT TYPE /BI0/TMATERIAL-TXTMD,

WA_EU_PRODH1 TYPE /BI0/PMATERIAL-PRODH1,

WA_EU_PRODH2 TYPE /BI0/PMATERIAL-PRODH2,

WA_EU_MSTAE TYPE /BI0/PMATERIAL-/BIC/MSTAE,

WA_EU_VENDOR TYPE /BI0/PMATERIAL-VENDOR,

END OF WA_EUREPORT,

TBL_EUREPORT LIKE TABLE OF WA_EUREPORT WITH HEADER LINE.

DATA: BEGIN OF WA_REPORT,

  • from ZCA_Product, ZCA_USITM, USITM and USITMSTR

WA_US_GLOBN TYPE ZCA_PRODUCT-GLOBN,

WA_US_GLOBT TYPE ZCA_PRODUCT-GLOBT,

WA_US_DESCR TYPE ZCA_PRODUCT-DESCR,

WA_US_INTOR TYPE ZCA_PRODUCT-INTOR,

WA_US_ACT TYPE ZCA_PRODUCT-ACT,

WA_US_TUOME TYPE ZCA_PRODUCT-TUOME,

WA_US_USITM TYPE ZCA_USITM-USITM,

WA_US_USEUMAT TYPE ZCA_USITM-USEUMAT,

WA_US_GPSUOMC TYPE ZCA_USITM-GPSUOMC,

WA_US_GPSDENOM TYPE ZCA_USITM-GPSDENOM,

WA_US_GPSNUMER TYPE ZCA_USITM-GPSNUMER,

WA_US_USPLANT TYPE ZCA_USITM-USPLANT,

WA_US_USITMDESC TYPE /BIC/TUSITM-TXTMD,

WA_US_USDIVSN TYPE /BIC/PUSITM-/BIC/USDIVSN,

WA_US_USERL TYPE /BIC/PUSITM-/BIC/US_ERL,

WA_US_USSTKTYP TYPE /BIC/PUSITMSTR-/BIC/USSTKTYP,

WA_US_USSUPNO TYPE /BIC/PUSITMSTR-/BIC/USSUPNO,

  • from ZCA_Product, ZCA_EUMAT and 0Material

WA_EU_EUMAT TYPE ZCA_EUMAT-EUMAT,

WA_EU_USITM TYPE ZCA_EUMAT-USITM,

WA_EU_GPSUOMC TYPE ZCA_EUMAT-GPSUOMC,

WA_EU_GPSDENOM TYPE ZCA_EUMAT-GPSDENOM,

WA_EU_GPSNUMER TYPE ZCA_EUMAT-GPSNUMER,

WA_EU_EUMATTXT TYPE /BI0/TMATERIAL-TXTMD,

WA_EU_PRODH1 TYPE /BI0/PMATERIAL-PRODH1,

WA_EU_PRODH2 TYPE /BI0/PMATERIAL-PRODH2,

WA_EU_MSTAE TYPE /BI0/PMATERIAL-/BIC/MSTAE,

WA_EU_VENDOR TYPE /BI0/PMATERIAL-VENDOR,

END OF WA_REPORT,

TBL_REPORT LIKE TABLE OF WA_REPORT WITH HEADER LINE.

----


  • SQL Queries *

----


SELECT

PGLOBN PGLOBT PDESCR PINTOR PACT PTUOME

UUSITM UUSEUMAT UGPSUOMC UGPSDENOM UGPSNUMER UUSPLANT

ITMT~TXTMD

ITMA/BIC/USDIVSN ITMA/BIC/US_ERL

ITMSTR/BIC/USSTKTYP ITMSTR/BIC/USSUPNO

FROM ZCA_PRODUCT AS P

INNER JOIN ZCA_USITM AS U ON PGLOBN = USYNPROD

INNER JOIN /BIC/TUSITM AS ITMT ON UUSITM = ITMT/BIC/USITM

INNER JOIN /BIC/PUSITM AS ITMA ON UUSITM = ITMA/BIC/USITM

LEFT OUTER JOIN /BIC/PUSITMSTR AS ITMSTR ON UUSITM = ITMSTR/BIC/USITMSTR AND ITMSTR/BIC/USBRPLANT = UUSPLANT

INTO TABLE TBL_USREPORT

WHERE ITMA~OBJVERS = 'A'

ORDER BY PGLOBT UUSITM.

SELECT

PGLOBN PGLOBT

EEUMAT EUSITM EGPSUOMC EGPSDENOM E~GPSNUMER

T~TXTMD

MPRODH1 MPRODH2 M/BIC/MSTAE MVENDOR

FROM ZCA_PRODUCT AS P

INNER JOIN ZCA_EUMAT AS E ON PGLOBN = ESYNPROD

INNER JOIN /BI0/TMATERIAL AS T ON EEUMAT = TMATERIAL

INNER JOIN /BI0/PMATERIAL AS M ON EEUMAT = MMATERIAL

INTO TABLE TBL_EUREPORT

WHERE M~OBJVERS = 'A'

ORDER BY PGLOBT EEUMAT.

  • TODO: join TBL_USREPORT and TBL_EUREPORT into TBL_REPORT and output as a list

Thank you for your help,

Dennis

1 ACCEPTED SOLUTION

former_member194669
Active Contributor
0 Kudos

Hi,

Martin :-->I missed the binary search , thanks for pointing out.


loop at tbl_usreport.
  move-corresponding tbl_usreport to tbl_report.
  append tbl_report.
endloop.
 
sort tbl_report by globn globt.

loop at tbl_eureport.
  read table tbl_report with key globn = tbl_eureport
                                 globt = tbl_eureport
                                 binary search.
  if sy-subrc eq 0.
   move-corresponding tbl_eureport to tbl_report.
   modify tbl_report index sy-tabix.
  else.
   move-corresponding tbl_eureport to tbl_report.
   append tbl_report.
  endif.
endloop.

aRs

9 REPLIES 9

Former Member
0 Kudos

There are many ways to join the tables: -

LOOP AT TAB1

LOOP AT TAB2 WHERE ......

LOOP AT TAB1

READ TABLE TAB2 WITH KEY

Run TCODE SE30 and hit the button 'Hints and tips' is will show u the best way to link two internal tables together.

0 Kudos

Hi,

use the logic

LOOP AT TABL1 INTO WA1.

LOOP AT TABL2 INTO WA2 WHERE FIELD1 = WA1-FILED1. "Use your join condtion here.

CLEAR WA3.

MOVE-CORRESPONDING WA1 to WA3.

MOVE-CORRESPONDING WA2 to WA3.

APPEND WA3 TO TABL3.

ENDLOOP.

ENDLOOP.

Regards,

Sesh

0 Kudos

Nothing against the previous post, except that if you use this coding for large internal tables you will get bad performance problems.

Look at the coding for nested loops in SE30 as I mentioned earlier. You should use the following example.

I = 1.

LOOP AT ITAB1 INTO WA1.

LOOP AT ITAB2 INTO WA2 FROM I.

IF WA2-K <> WA1-K.

I = SY-TABIX.

EXIT.

ENDIF.

" ...

ENDLOOP.

ENDLOOP.

This is only if more than one entry will be valid in ITAB2.

former_member194669
Active Contributor
0 Kudos

Hi,


loop at tbl_usreport.
  move-corresponding tbl_usreport to tbl_report.
  append tbl_report.
endloop.

loop at tbl_eureport.
  read table tbl_report with key globn = tbl_eureport
                                 globt = tbl_eureport
  if sy-subrc eq 0.
   move-corresponding tbl_eureport to tbl_report.
   modify tbl_report index sy-tabix.
  else.
   move-corresponding tbl_eureport to tbl_report.
   append tbl_report.
  endif.
endloop.

aRs

0 Kudos

aRs,

You have over 1500 points, yet you do not mention to him to use Binary Search. The guy says he is a newbie. He may start getting performance issue.

Just giving him random code will not help. Well it might help you to get the points I suppose.

former_member194669
Active Contributor
0 Kudos

Hi,

Martin :-->I missed the binary search , thanks for pointing out.


loop at tbl_usreport.
  move-corresponding tbl_usreport to tbl_report.
  append tbl_report.
endloop.
 
sort tbl_report by globn globt.

loop at tbl_eureport.
  read table tbl_report with key globn = tbl_eureport
                                 globt = tbl_eureport
                                 binary search.
  if sy-subrc eq 0.
   move-corresponding tbl_eureport to tbl_report.
   modify tbl_report index sy-tabix.
  else.
   move-corresponding tbl_eureport to tbl_report.
   append tbl_report.
  endif.
endloop.

aRs

0 Kudos

Thanks everyone for your prompt reply. aRs' solution worked like a charm. I only had to rename a few fields in all three tables so that they were populated by either the us or the eu table (before it was only from the us table).

However, I have one question regarding this solution: would it make sense to mark key fields as such in my internal tables or does it not matter (performance?).

former_member194669
Active Contributor
0 Kudos

Hi,

For performance point only we used binary search in read.

aRs

0 Kudos

Ok, thank you.