Skip to Content
0
Jul 29, 2019 at 09:47 PM

Help for converting from Oracle SQL to Open SQL

94 Views

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