Passing a range in open sql select join conditions

[ekekakos]

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

[Sandra_Rossi]

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").

[ekekakos]

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

[Sandra_Rossi]

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.

[ekekakos]

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

[Sandra_Rossi]

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

[Sandra_Rossi]

---

@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