why left join is not working

[ekekakos]

Hello, 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

[Sandra_Rossi]

I recommend not saying "is not working", but instead explain your input data, what result you get and what you expect.

[ekekakos]

Sorry my mistake. It is working as INNER JOIN and not as LEFT.

[Sandra_Rossi · Accepted solution]

It's because you are doing a WHERE 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, so it will fail on the WHERE.

You should indicate it in the ON condition.

EDIT: but it's not accepted (ABAP 7.58) to have a complex function in the ON part (= ( SELECT ... )), so in your case you can keep it in the WHERE part 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.

Test reproducible by anyone:

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

[ekekakos · Accepted solution]

Thanks Sandra. And I did it but without parenthesis and instead of IS NULL I put = '' but mine of course did not work.