Links

MSCEWI1059

Non-Ansi Outer Join is not supported in OR predicate

Severity

Medium

Description

Snowflake does not support using Non-Ansi Outer Join syntax in any OR statement.

Example Code

Input Code (Oracle):

SELECT * FROM
EMPLOYEE e, PRODUCT p, LOCATION l WHERE
e.id = p.eid(+) AND
(p.productname(+) = 'LAPTOP' OR
p.productname(+) = 'MONITOR') AND
p.id = 1;

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 PUBLIC.EMPLOYEE e, PUBLIC.PRODUCT p, PUBLIC.LOCATION l WHERE
e.id = p.eid(+) AND
(-- ** MSC-ERROR - MSCEWI1059 - NON-ANSI OUTER JOIN IN 'OR' PREDICATE IS NOT SUPPORTED IN SNOWFLAKE **
-- p.productname(+) = 'LAPTOP' OR
--p.productname(+) = 'MONITOR') AND
p.id = 1;

Recommendations

  • Rewrite the statement using ANSI Outer Join syntax.
  • Check for unfinished conditionals (AND, …) on the translated code.
  • For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.