MSCEWI3090

Non-Ansi Outer Join has an invalid Between predicate

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

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:

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:

SELECT
*
/*** MSC-WARNING - MSCEWI3081 - SNOWFLAKE NON-ANSI OUTER JOIN SYNTAX HAS SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE. IT COULD CAUSE COMPILATION ERRORS ***/
FROM hr.countries c, hr.regions r, hr.locations l WHERE
-- ** MSC-ERROR - MSCEWI3090 - NON-ANSI OUTER JOIN INVALID 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 [email protected]