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.
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.
WHERE(what you don't want).
ONjoin condition as below.
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).