LEFT OUTER JOIN with internal tables

Former Member — Mon, 03 Mar 2008 04:11:12 GMT

hi all,

I have a query to execute,

SELECT SKAT_{SAKNR SKAT}TXT20

SUM( GLT0~HSLVT ) AS HSLVT

INTO CORRESPONDING FIELDS OF TABLE ITAB_ZSTMGLRACC

FROM SKAT

LEFT OUTER JOIN GLT0 ON GLT0_{RACCT = SKAT}SAKNR

AND GLT0~RYEAR = I_GJAHR

AND GLT0~BUKRS = 'comp1'

WHERE SKAT~KTOPL = 'EICA'

GROUP BY SKAT_{SAKNR SKAT}TXT20.

here i want to replace GLT0~BUKRS = 'comp1' with multiple values from Internal table ITAB_T001

....

since i used SUM ......FOR ALL ENTRIES IN ITAB_T001 cant be used .

any one have options??

thx

Jose

Re: LEFT OUTER JOIN with internal tables

Former Member — Mon, 03 Mar 2008 04:18:03 GMT

The number of values should not be too big for the company code. Use it as a range. In the where clause use field in r_myrange.... The range is an internal table with 3 or 4 fields

option (IN|EX) sign(EQ|NE|LE|LT|GE|GT|BT) plus a low value and a high value field. Read up on Ranges|

Hope it helps.

Re: LEFT OUTER JOIN with internal tables

Former Member — Mon, 03 Mar 2008 04:26:42 GMT

The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

Note

If the same column name occurs in several database tables in a join expression, they have to be identified in all remaining additions of the SELECT statement by using the column selector ~.

Example

Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

Example

Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

PARAMETERS p_cityfr TYPE spfli-cityfrom.

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

AND p~cityfrom = p_cityfr.

LOOP AT itab INTO wa.

IF wa-connid = '0000'.

WRITE: / wa-carrid, wa-carrname.

ENDIF.

ENDLOOP.

topic Re: LEFT OUTER JOIN with internal tables in Application Development and Automation Discussions

LEFT OUTER JOIN with internal tables

Re: LEFT OUTER JOIN with internal tables

Re: LEFT OUTER JOIN with internal tables