Reputation: 611
can you tell me why in the following select the LEFT join is working as INNER join?
SELECT FROM @it_selected_data AS selected
LEFT JOIN zkrh_scmt_invh AS invh
ON invh~ebeln = selected~ebeln
FIELDS selected~*, invh~invkind
WHERE invh~invkind = ( SELECT MAX( invkind ) FROM zkrh_scmt_invh WHERE ebeln = invh~ebeln )
INTO CORRESPONDING FIELDS OF TABLE @it_final_data.
Thanks in advance
Elias
Upvotes: 0
Views: 79
Reputation: 13686
Below is the copy of my answer to the same question in the SAP forum here.
The question and answer are already given here, here and many other places, but let me explain again with my words:
WHERE
clause on a column of the "left join" table.WHERE
condition will fail.ON
clause.In ABAP SQL, it's not accepted to have a complex function in the ON clause (e.g. = ( SELECT ... )
in ABAP 7.58), the alternative is to keep it in the WHERE
clause and indicate OR ... IS NULL
:
SELECT FROM @it_selected_data AS selected
LEFT JOIN zkrh_scmt_invh AS invh
ON invh~ebeln = selected~ebeln
FIELDS selected~*, invh~invkind
WHERE ( invh~invkind IS NULL
OR invh~invkind = ( SELECT MAX( invkind )
FROM zkrh_scmt_invh
WHERE ebeln = invh~ebeln ) )
INTO CORRESPONDING FIELDS OF TABLE @it_final_data.
Another test, reproducible by anyone (DE
exists in table T005T
but not ZY
):
TYPES tt_land1 TYPE STANDARD TABLE OF t005-land1 WITH EMPTY KEY.
TYPES: BEGIN OF ts_country_code_and_name,
land1 TYPE t005t-land1,
landx TYPE t005t-landx,
END OF ts_country_code_and_name.
TYPES tt_country_codes_and_names TYPE STANDARD TABLE OF ts_country_code_and_name WITH EMPTY KEY.
DATA(countries) = VALUE tt_land1( ( 'DE' ) ( 'ZY' ) ).
DATA(country_codes_and_names) = VALUE tt_country_codes_and_names( ).
SELECT
FROM @countries AS countries
LEFT JOIN t005t
ON t005t~land1 = countries~table_line
AND t005t~spras = 'E'
FIELDS countries~table_line AS land1, t005t~landx
WHERE ( t005t~LAND1 IS NULL
OR t005t~LAND1 IN ( SELECT land1 from t005 WHERE land1 LIKE 'D%' ) )
INTO TABLE @country_codes_and_names.
ASSERT country_codes_and_names = VALUE tt_country_codes_and_names(
( land1 = 'DE' landx = 'Germany' )
( land1 = 'ZY' landx = '' ) ).
Upvotes: 0
Reputation: 611
Well after searching in the internet I found the answer here
left join restrictions
So I use CTE as below
WITH
+invkind AS ( SELECT ebeln, MAX( invkind ) AS invkind FROM zkrh_scmt_invh GROUP BY ebeln )
SELECT FROM @it_selected_data AS selected
LEFT JOIN +invkind AS invh
ON invh~ebeln = selected~ebeln
LEFT JOIN dd07t
ON invh~invkind = dd07t~domvalue_l AND
dd07t~domname = 'ZTEST' AND
dd07t~ddlanguage = @sy-langu
FIELDS selected~*, concat_with_space( invh~invkind, dd07t~ddtext, 2 ) AS invkind
INTO CORRESPONDING FIELDS OF TABLE @it_final_data.
Thanks a lot
Elias
Upvotes: 0