Pattern how to realise the SQL outer join with the loops over the internal tables.
The sample program below generates following output:
Here the source code:
REPORT zitab_outjoin.
TYPES:
BEGIN OF ts_data,
carrid TYPE s_carr_id,
carrname TYPE s_carrname,
connid TYPE s_conn_id,
cityfrom TYPE s_city,
END OF ts_data,
tt_data TYPE TABLE OF ts_data.
PERFORM database_join.
SKIP.
PERFORM internal_table_join.
*&---------------------------------------------------------------------*
*& Form database_join
*&---------------------------------------------------------------------*
FORM database_join.
DATA:
lt_data TYPE tt_data,
ls_data TYPE ts_data,
m1 TYPE i,
m2 TYPE i.
GET RUN TIME FIELD m1.
SELECT scarr~carrid scarr~carrname spfli~connid spfli~cityfrom
INTO (ls_data-carrid, ls_data-carrname, ls_data-connid, ls_data-cityfrom)
FROM scarr
LEFT OUTER JOIN spfli ON scarr~carrid = spfli~carrid
WHERE scarr~carrid BETWEEN 'AA' AND 'LH'
ORDER BY scarr~carrid scarr~carrname spfli~connid.
APPEND ls_data TO lt_data.
ENDSELECT.
GET RUN TIME FIELD m2.
LOOP AT lt_data INTO ls_data.
WRITE: / ls_data-carrid, ls_data-carrname, ls_data-connid, ls_data-cityfrom.
ENDLOOP.
m2 = m2 - m1.
WRITE: / 'db join', m2, 'microseconds'.
ENDFORM. "database_join
*&---------------------------------------------------------------------*
*& Form internal_table_join
*&---------------------------------------------------------------------*
FORM internal_table_join.
DATA:
lt_data TYPE tt_data,
ls_data TYPE ts_data,
lt_scarr TYPE SORTED TABLE OF scarr WITH NON-UNIQUE KEY carrid,
ls_scarr TYPE scarr,
lt_spfli TYPE HASHED TABLE OF spfli WITH UNIQUE KEY carrid connid,
ls_spfli TYPE spfli,
m1 TYPE i,
m2 TYPE i,
m3 TYPE i,
m4 TYPE i.
* Init-Load
GET RUN TIME FIELD m1.
SELECT carrid carrname
INTO CORRESPONDING FIELDS OF TABLE lt_scarr
FROM scarr
WHERE scarr~carrid BETWEEN 'AA' AND 'LH'
ORDER BY carrid carrname.
SELECT carrid connid cityfrom
INTO CORRESPONDING FIELDS OF TABLE lt_spfli
FROM spfli.
GET RUN TIME FIELD m2.
m2 = m2 - m1.
* Join the internal tables
GET RUN TIME FIELD m3.
LOOP AT lt_scarr INTO ls_scarr.
CLEAR ls_data.
MOVE-CORRESPONDING ls_scarr TO ls_data.
LOOP AT lt_spfli INTO ls_spfli
WHERE carrid = ls_scarr-carrid.
MOVE-CORRESPONDING ls_spfli TO ls_data.
APPEND ls_data TO lt_data.
ENDLOOP.
IF sy-subrc <> 0.
APPEND ls_data TO lt_data.
ENDIF.
ENDLOOP.
GET RUN TIME FIELD m4.
LOOP AT lt_data INTO ls_data.
WRITE: / ls_data-carrid, ls_data-carrname, ls_data-connid, ls_data-cityfrom.
ENDLOOP.
m4 = m4 - m3.
WRITE: / 'itable join - load ', m2, 'microseconds'.
WRITE: / 'itable join - loops', m4, 'microseconds'.
m4 = m4 + m2.
WRITE: / 'itable join - total', m4, 'microseconds'.
ENDFORM. "internal_table_join
See other related notes on my website: