Help for converting from Oracle SQL to Open SQL

[luis_rod]

Hi all,

(My apologies beforehand as I'm not very experienced in either SAP or Open SQL)

I'm trying to convert from Oracle the following SQL statement:

SELECT 1
FROM sapsr3.zt01 t1
INNER JOIN sapsr3.zt01 t2 ON (
        (
            t1.number_from <= t2.number_to
            AND t2.number_from <= t1.number_to
            )
        AND NOT (
            t1.number_from = t2.number_from
            AND t2.number_to = t1.number_to
            )
        )
WHERE t1.dat1 = '00000000';

(In this case, I'm trying to detect number ranges (number_from, number_to) that may be duplicated or overlap).

So I write this in what I would think is the proper OPEN SQL syntax:

SELECT t1~str1 into w_str
FROM zt01 as t1
INNER JOIN zt01 as t2 ON (
        (
            t1~number_from <= t2~number_to
            AND t2~number_from <= t1~number_to
            )
        AND NOT (
            t1~number_from = t2~number_from
            AND t2~number_to = t1~number_to
            )
        )
WHERE t1~dat1 = '00000000'.

And then I find that I cannot use NOT in an ON clause... In any other SQL I could simply omit the NOT clause by inverting all the relationships inside the parentheses, changing = to <> and AND to OR. BUT, it seems that OPEN SQL does not like the OR either.

Any ideas?

Thanks in advance,

Luis

[DoanManhQuynh]

First thing, I don't have any problem to use NOT in ON clause. what is your ABAP version (mine is 7.4):

SELECT * FROM sflight INNER JOIN spfli ON (     ( sflight~carrid = spfli~carrid ) AND 
                                            NOT ( sflight~connid = spfli~connid ) )
  INTO TABLE @DATA(itab).

Second, I think your query is something like below (assume that from_date < to_date for both table):

SELECT t1~str1
FROM zt01 as t1 INNER JOIN zt01 as t2 ON ( ( t1~number_from <= t2~number_to    AND 
                                             t1~number_from > t2~number_from ) AND
                                             t1~number_to   > t2~number_to )
   into w_str
WHERE t1~dat1 = '00000000'.

Third, I think it better to join table based on their key, your query feel like the WHERE condition than a join condition.

[Sandra_Rossi]

OR, NOT allowed in joins after ON since 7.40 SP08 -> cf ABAP SQL in Release 7.40, SP08 - Restrictions removed

[Sandra_Rossi]

Quynh Doan Manh Can you convert it into an answer so that I can vote for it?

[luis_rod]

Quynh,

Thanks for your answer. Unfortunately, we are somewhat on the Dark Ages regarding our ABAP version (7.00) and the compiler messages are very clear about the fact that neither NOT nor OR are allowed when using the ON clause 😞

The user needs to check that a particular range (could be even a range of one, e.g. from 5 to 5) does not coincide with another in a Z table (e.g. 4 to 7). Let me try your code and will comment back. Also, as Sandra wrote, please convert it to an answer in order to be able to vote for it.

Thanks again,
Luis

[Sandra_Rossi]

In 7.0, maybe use the trick to join on the client column (add it if it's not there), use CLIENT SPECIFIED words to allow the client to be specified, and push all the current join conditions to the WHERE clause.

[DoanManhQuynh]

Sandra Rossi

Idk what change I made but the notification not work when someone mention my name as usual.

Luis Rodriguez

In that case i think you could change a little bit, cause from_date <= to_date so the to_date just need to difference to each other:

SELECT t1~str1
FROM zt01 as t1 INNER JOIN zt01 as t2 ON ( ( t1~number_from <= t2~number_to     AND 
                                             t1~number_from >  t2~number_from ) AND
                                             t1~number_to   <> t2~number_to )
   into w_str
WHERE t1~dat1 = '00000000'.