CODEWITHSUNDEEP
where-clauseleft-joinabapopensql
ekekakos
ekekakos

Reputation: 611

Why does LEFT join omits a line from the left side, like INNER join

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

Answers (2)

Sandra Rossi
Sandra Rossi

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:

  • It's because you are doing a filter in the WHERE clause on a column of the "left join" table.
  • If there's no line in the "left join" table, its columns will be considered as NULL value and the WHERE condition will fail.
  • General solution: move the condition to the 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

ekekakos
ekekakos

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

Related Questions