SELECT FROM DB_TABLE with left join where column from joined table

Question

I want to create a select that left joins multiple tables.

In the following code, I need to select only lines of table EKPO where column EBELN = variable lv_ebeln. But as soon as I add this condition to the WHERE, I get this syntax error:

The elements in the "SELECT LIST" list must be separated using commas.

So, is there a way to add this condition?

DATA: BEGIN OF wa_itab,
        gjahr TYPE rseg-gjahr,
        ebelp TYPE ekpo-ebelp,
      END OF wa_itab,
      itab     LIKE TABLE OF wa_itab,
      lv_belnr TYPE rseg-belnr,
      lv_ebeln TYPE ekpo-ebeln.

SELECT rseg~gjahr ekpo~ebelp FROM rseg
  LEFT JOIN ekpo ON rseg~ebeln = ekpo~ebeln AND rseg~ebelp = ekpo~ebelp
  INTO (wa_itab-gjahr, wa_itab-ebelp )
  WHERE rseg~belnr = lv_belnr 
    AND ekpo~ebeln = lv_ebeln. " <=== SYNTAX ERROR because of this line

  ...
  " some other code
  ...

  APPEND wa_itab TO itab.
ENDSELECT.

I edited the message so that the code is a MCVE and I removed the part about the performance because it's a different question. Feel free to ask it separately. — Sandra Rossi
– Sandra Rossi, Commented Apr 10, 2019 at 18:40
From the WHERE condition it is clear that what you need is an INNER JOIN and not LEFT (OUTER) JOIN. — Jagger
– Jagger, Commented Apr 10, 2019 at 19:03

Sandra Rossi · Accepted Answer · 2019-04-10 19:01:26Z

4

The syntax error message is misleading, the commas are not required, because the problem is not related to the OpenSQL strict mode.

The true error is that in a left outer join, the condition on the right table EKPO is not accepted in the WHERE because if the join result has some lines only from the left table RSEG, the columns from the right table EKPO will have the value null and thus the conditions based on the right table columns are false and the lines are not selected, making the left outer join behave like an inner join.

Either you use an inner join with the condition in the WHERE (what you don't want).
Or you keep the left outer join and you move the condition to the ON join condition as below.

Code:

SELECT rseg~gjahr ekpo~ebelp FROM rseg
  LEFT JOIN ekpo ON rseg~ebeln = ekpo~ebeln 
                AND rseg~ebelp = ekpo~ebelp
                AND ekpo~ebeln = lv_ebeln " <=== move it here
  INTO (wa_itab-gjahr, wa_itab-ebelp )
  WHERE rseg~belnr = lv_belnr.

PS: I don't find a reference in the ABAP documentation that explains it officially, except there is this close example:

SELECT s~carrid, s~carrname 
   FROM scarr AS s 
   LEFT OUTER JOIN spfli AS p ON s~carrid   =  p~carrid 
                              AND p~cityfrom = @cityfr  " <=== not in the WHERE
   WHERE p~connid IS NULL 
   ORDER BY s~carrid, s~carrname 
   INTO TABLE @DATA(itab).

answered Apr 10, 2019 at 19:01

Sandra Rossi

14k6 gold badges28 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jagger Over a year ago

You do not find it in the ABAP documentation because these are SQL basics.

Collectives™ on Stack Overflow

SELECT FROM DB_TABLE with left join where column from joined table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related