topic Re: Passing a range in open sql select join conditions in Application Development and Automation Discussions

Passing a range in open sql select join conditions

ekekakos — Sun, 28 Sep 2025 07:51:02 GMT

Hello all,

I have the below problem and I do not know if there is a solution. The system is an ECC EHP8 so it is 7.50.

I have the below SQL:

SELECT FROM lfa1 INNER JOIN lfb1 ON lfa1~lifnr = lfb1~lifnr AND lfb1~bukrs = _options_structure-p_bukrs LEFT JOIN ztable ON ztable~rzzlifnr = lfa1~lifnr AND ztable~rbukrs = lfb1~bukrs AND ztable~rzzumskz <> @Space AND ztable~rzzumskz NOT IN (@range_umskz) FIELDS DISTINCT lfa1~lifnr, lfb1~mindk, lfb1~busab WHERE lfa1~lifnr IN _options_structure-s_lifnr AND lfa1~ktokk IN _options_structure-s_ktokk AND lfb1~akont IN _options_structure-s_akont AND lfb1~mindk IN _options_structure-s_mindk AND lfb1~busab IN _options_structure-s_busab INTO TABLE (LOCAL_TABLE).

The problem I am getting is with the range RANGE_UMSKZ. And the error is:

"range_umskz" cannot be a table or a reference and cannot contain either of these objects or strings.

Is there anyway to pass this range in JOIN ON condition, because if I put it in where I will loose the record and not the values of the Ztable.

Thanks in advance,

Elias

Re: Passing a range in open sql select join conditions

Sandra_Rossi — Sat, 27 Sep 2025 13:27:47 GMT

In ABAP 7.50, probably no other solution than taking the range out of the LEFT JOIN and filtering the internal table after SELECT.

But what is the goal of doing a LEFT OUTER JOIN in your SELECT? The SELECT you show, with or without the LEFT OUTER JOIN, are 100% equivalent.

NB: your SELECT has currently an invalid syntax for sure (@ everywhere or nowhere, missing right argument in "ztable~rzzumskz <> AND").

Re: Passing a range in open sql select join conditions

ekekakos — Sun, 28 Sep 2025 07:39:05 GMT

Hello Sandra. I did not notice the missing part which is @Space. I fix the above code.

I am trying to find a solution to my logic which is: I want to display all the customers who satisfy the where clause and I want the data from ZTABLE for the above vendors which satisfy the ON clause in the above select. In a few words, I want all the vendors from LFA1, LFB1 to be displayed and the data from ZTABLE for some of them, the others have empty fields from ZTABLE.

How I can do it? Thanks

Re: Passing a range in open sql select join conditions

Sandra_Rossi — Sun, 28 Sep 2025 09:03:51 GMT

Again, the SELECT you show will be equivalent to the same SELECT without LEFT OUTER JOIN because you don't extract any column from ZTABLE.

In ABAP 7.50, not a lot of solutions.

First solution: take the range selection out of the LEFT JOIN and filter the internal table after SELECT.

Second solution, based on the fact that WHERE IS NULL on a left-joined table column means that there's no line found in table:

DATA range_currency TYPE RANGE OF sflight-currency. range_currency = VALUE #( ( sign = 'I' option = 'EQ' low = 'USD' ) ). SELECT FROM scarr LEFT JOIN sflight ON sflight~carrid = scarr~carrid AND sflight~currency <> 'EUR' FIELDS scarr~carrid, sflight~connid, sflight~currency WHERE sflight~currency IS NULL OR sflight~currency NOT IN @range_currency INTO TABLE @DATA(itab).

Third solution, by building the from/join dynamically with rzzumskz <> value1 AND rzzumskz <> value2 ... instead of your ranges table (range_umskz), assuming that it's composed of lines I EQ value1 ...:

TYPES: BEGIN OF itab_line, carrid TYPE scarr-carrid, connid TYPE sflight-connid, currency TYPE sflight-currency, END OF itab_line. DATA itab TYPE STANDARD TABLE OF itab_line. DATA range_currency TYPE RANGE OF sflight-currency. range_currency = VALUE #( ( sign = 'I' option = 'EQ' low = 'USD' ) ). DATA(dynamic_from) = ` scarr` && ` LEFT JOIN sflight` && ` ON sflight~carrid = scarr~carrid` && ` AND sflight~currency <> 'EUR'`. LOOP AT range_currency ASSIGNING FIELD-SYMBOL(<line_range_currency>) WHERE sign = 'I' AND option = 'EQ'. dynamic_from = dynamic_from && | AND sflight~currency <> '{ <line_range_currency>-low }'|. ENDLOOP. SELECT scarr~carrid, sflight~connid, sflight~currency FROM (dynamic_from) INTO TABLE @itab.

Re: Passing a range in open sql select join conditions

ekekakos — Sun, 28 Sep 2025 09:02:47 GMT

Unfortunately, it does not work as it returns 200 records less. Most probably, I will take out the ZTABLE, I will read it for all vendors that we have from the previous select and the with a loop I pass the values for the specific vendors.

Thanks

Re: Passing a range in open sql select join conditions

Sandra_Rossi — Sun, 28 Sep 2025 09:15:04 GMT

@ekekakos wrote:
I did not notice the missing part which is @Space. I fix the above code.

Thanks, bug reproduced and just reported here: Bug Report: "@space" disappears from code section ... - SAP Community

Re: Passing a range in open sql select join conditions

Sandra_Rossi — Sun, 28 Sep 2025 09:24:20 GMT

Please clarify your question, because as I understood it, I tested the 3 solutions and all of them solve your question.