Skip to Content
0
Former Member
Jul 18, 2007 at 02:59 PM

Newbie: Joining internal tables

28 Views

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