CODEWITHSUNDEEP
abapopensql
Robert Setnicka
Robert Setnicka

Reputation: 3

SELECT FROM DB_TABLE with left join where column from joined table

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.

Upvotes: 0

Views: 5444

Answers (1)

Sandra Rossi
Sandra Rossi

Reputation: 13638

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).

Upvotes: 4

Related Questions