SSC-EWI-OR0090

Non-Ansi Outer Join has an invalid Between predicate

Severity

Medium

Some parts of the output code are omitted for clarity reasons.

Description

This issue happens when there is an OUTER JOIN with the (+) operator inside a BETWEEN clause that cannot be executed in Snowflake. This generally happens when multiple tables are used in the interval of the BETWEEN clause.

Example Code

Input Code Oracle:

IN -> Oracle_01.sql

SELECT
*
FROM 
hr.countries c, hr.regions r,  hr.locations l WHERE 
l.location_id  BETWEEN r.region_id(+) AND c.region_id(+);

Output Code:

OUT -> Oracle_01.sql

SELECT
*
FROM
hr.countries c,
hr.regions r,
hr.locations l WHERE
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0090 - INVALID NON-ANSI OUTER JOIN BETWEEN PREDICATE CASE FOR SNOWFLAKE. ***/!!!
l.location_id  BETWEEN r.region_id(+) AND c.region_id(+);

Recommendations

• Manually change the Outer Join to ANSI syntax.

• If you need more support, you can email us at [e-mail]