Left Outer Join

[Former Member]

Hi All,

I have a program where in I have to join six tables one of which is a Left Outer Join. Now I have to change that using For All Entries...

Please let me know how to split this Left Outer Join using For All Entries.

Or Is there any other way of doing it? ie., Can I write a Select stmt using Left Outer join and for all entries?

Thanks in adavance for your efforts!!

Regards.

Adapala M.

[Former Member]

Hai.

check this.

Syntax

... [(] {dbtab_left [AS tabalias_left]} | join

{[INNER] JOIN}|{LEFT [OUTER] JOIN}

{dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

Effect

The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of [INNER] JOIN or LEFT [OUTER] JOIN . Depending on the type of join, a join expression can be either an inner ( INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

On the left-hand side, either a single database table, a view dbtab_left, or a join expression join can be specified. On the right-hand side, a single database table or a view dbtab_right as well as join conditions join_cond can be specified after ON. In this way, a maximum of 24 join expressions that join 25 database tables or views with each other can be specified after FROM.

AS can be used to specify an alternative table name tabalias for each of the specified database table names or for every view. A database table or a view can occur multiple times within a join expression and, in this case, have various alternative names.

The syntax of the join conditions join_cond is the same as that of the sql_cond conditions after the addition WHERE, with the following differences:

At least one comparison must be specified after ON.

Individual comparisons may be joined using AND only.

All comparisons must contain a column in the database table or the view dbtab_right on the right-hand side as an operand.

The following language elements may not be used: BETWEEN, LIKE, IN.

No sub-queries may be used.

For outer joins, only equality comparisons (=, EQ) are possible.

If an outer join occurs after FROM, the join condition of every join expression must contain at least one comparison between columns on the left-hand and the right-hand side.

In outer joins, all comparisons that contain columns as operands in the database table or the view dbtab_right on the right-hand side must be specified in the corresponding join condition. In the WHERE condition of the same SELECT command, these columns are not allowed as operands.

Resulting set for inner join

The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

Resulting set for outer join

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.

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.

regards.

sowjanya.b

[Former Member]

Hello.

Yes, you can use both left outer join and for all entries.

But .... Join with 6 different tables is not correct and not effective,

Try seperate it to 2 or even 3 different select statements into temporary internal tables and join them together within a simple loop.

Reward if helpfull..

[Former Member]

Hi Rebeka,

Thanks for the input.

What I wanted to know is: Can we use Left Outer Join and For All Entries in a single stmt. If Yes, could you provide me with an example?

Regards,

Adapala M

[Former Member]

Hi,

Of course, yuou can use JOIN and FOR ALL ENTRIES in single statement.

Following is an example:

  SELECT ek~ebeln
         ep~ebelp
         ek~lifnr
         ek~ekorg
         ek~angnr
         ek~yy28mmjv
         ep~matnr
         ep~werks
 INTO TABLE g_t_ekko_ekpo
 FROM ekko AS ek INNER JOIN ekpo AS ep
   ON ek~ebeln  = ep~ebeln
 WHERE ek~loekz = space
   AND ek~ekorg = p_ekorg
   AND ek~angnr IN so_angnr
   AND ( ( ek~bstyp = 'A' )
    OR (   ek~bstyp = 'K' AND ek~yy28marbeitsver = g_c_x )
       )
   AND ep~loekz = space.

  IF NOT g_t_ekko_ekpo[] IS INITIAL.
    SELECT hdr~matnr
           hdr~werks
           hdr~jahr
           hdr~ekorg
           pos~lifnr
           hdr~yy28m_phek_m
           hdr~yy28m_phek_mpe
           hdr~yy28m_phek_mwaer
           hdr~yy28m_maprs
           hdr~yy28m_maprs_pe
           hdr~yy28m_ergwaer
           hdr~yy28m_zprs_m
           hdr~yy28m_zprs_mpe
           hdr~yy28m_zprs_mwaer
           hdr~yy28m_mviprs
           hdr~yy28m_mviprs_pe
      INTO TABLE g_t_hdr_pos
      FROM y28m_basmat_hdr AS hdr INNER JOIN y28m_basmat_pos AS pos
        ON hdr~matnr =  pos~matnr
       AND hdr~werks =  pos~werks
       AND hdr~jahr  =  pos~jahr
       AND hdr~ekorg =  pos~ekorg
     FOR ALL ENTRIES IN g_t_ekko_ekpo
     WHERE hdr~ekorg =  p_ekorg
       AND hdr~matnr =  g_t_ekko_ekpo-matnr
       AND hdr~werks IN so_werks
       AND hdr~jahr BETWEEN l_f_sjahr AND l_f_ejahr.
  ENDIF.

Regards,

Karuna.

[Former Member]

Hi,

For the first three tables write the innerjoin and select data in to itab, it will be effective performance wise. for the others if itab[] is not initial.

select for all entries in itab from other tables. for the left join also you have to join the itab and dbtable as left outer join. looping itab reading the other tables append data in to final table.

Reward.

[Former Member]

My suggestion is put all the intrenal table values in to single table using for all entries those who have primary key values and combine that table with left outer join statement.

[Former Member]

Hi all,

Thanx to every one.... My issue is resolved....