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: 

Does "SELECT - FROM @itab" not work for left outer join?

mahlzeit1948
Explorer
0 Kudos

I'm trying to do a left outer join between an itab and a DBtab. The results are identical to an inner join. Am I doing something wrong or is this not supported? Couldn't find any stipulation in the documentation.

Example

    SELECT FROM @lt_mutable_implementations AS all
      LEFT OUTER JOIN ztsl_impl_users AS specific
      ON all~impl_id = specific~impl_id
      FIELDS all~impl_id,
             specific~low,
             specific~muted,
             specific~mute_expiration
             WHERE specific~low = @if_email
               AND specific~muted = @abap_true
             INTO TABLE @DATA(lt_details).<br>
1 ACCEPTED SOLUTION

raymond_giuseppi
Active Contributor

In the WHERE clause of the SELECT statement, you have put some criteria on the fields of the table linked by the LEFT OUTER JOIN.
As these criteria are not part of the JOIN conditions (ON condition), these fields contain NULL values and excluded by the WHERE option.

Did you try to move those criteria to the ON condition?

3 REPLIES 3

Sandra_Rossi
Active Contributor
0 Kudos

This code works fine for me, the database table SCARR contains the value 'LH' but not '??' (ABAP 7.52).

TYPES: BEGIN OF ty_itab_line,
         carrid TYPE scarr-carrid,
       END OF ty_itab_line,
       ty_itab TYPE STANDARD TABLE OF ty_itab_line WITH EMPTY KEY.
DATA(itab) = VALUE ty_itab( ( carrid = 'LH' ) ( carrid = '??' ) ).
DATA(result) = VALUE ty_itab( ).
SELECT itab~carrid FROM @itab AS itab INNER JOIN scarr ON itab~carrid = scarr~carrid INTO TABLE @result.
ASSERT result = VALUE ty_itab( ( carrid = 'LH' ) ).
SELECT itab~carrid FROM @itab AS itab LEFT OUTER JOIN scarr ON itab~carrid = scarr~carrid INTO TABLE @result.
ASSERT result = VALUE ty_itab( ( carrid = 'LH' ) ( carrid = '??' ) ).

raymond_giuseppi
Active Contributor

In the WHERE clause of the SELECT statement, you have put some criteria on the fields of the table linked by the LEFT OUTER JOIN.
As these criteria are not part of the JOIN conditions (ON condition), these fields contain NULL values and excluded by the WHERE option.

Did you try to move those criteria to the ON condition?

Raymond, I'm an idiot....should have caught that.Thanks