Question Re: why left join is not working in Technology Q&A

why left join is not working

ekekakos — Tue, 02 Apr 2024 07:33:43 GMT

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

Re: why left join is not working

Sandra_Rossi — Tue, 02 Apr 2024 07:18:46 GMT

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

Re: why left join is not working

ekekakos — Tue, 02 Apr 2024 07:32:42 GMT

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

Re: why left join is not working

ekekakos — Tue, 02 Apr 2024 08:57:11 GMT

I searched more deeply in the internet and I found the reason in this answer

https://community.sap.com/t5/application-development-discussions/left-join-restrictions-that-opensql-poses-discussion/m-p/1104790

So I changed the select using CTE

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 = 'ZKRH_SCMD_MMKIND' AND dd07t~ddlanguage = -langu FIELDS selected~*, concat_with_space( invh~invkind, dd07t~ddtext, 2 ) AS invkind INTO CORRESPONDING FIELDS OF TABLE @it_final_data.

Thanks

Elias

Re: why left join is not working

Sandra_Rossi — Tue, 02 Apr 2024 09:04:39 GMT

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

Re: why left join is not working

Sandra_Rossi — Tue, 02 Apr 2024 08:54:14 GMT

See my answer, it's more simple.

Re: why left join is not working

ekekakos — Tue, 02 Apr 2024 09:07:13 GMT

Which answer Sandra?

Re: why left join is not working

Sandra_Rossi — Tue, 02 Apr 2024 09:10:43 GMT

This one:

Re: why left join is not working

ekekakos — Tue, 02 Apr 2024 09:13:24 GMT

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