MSCEWI1060

Non-Ansi Outer Join to multiple tables is not supported in Snowflake

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

This EWI will be deprecated in the future.

Severity

High

Description

Snowflake does not support using the same table on two Non-Ansi Outer Joins.

Example Code

Input Code (Oracle):

SELECT
e.id, e.name,
p.id, p.productname, p.eid,
l.id, l.location, l.pid
FROM
EMPLOYEE e, PRODUCT p, LOCATION l WHERE
e.id(+) = p.eid AND
e.id(+) = l.id AND
p.id(+) = l.pid;

Output Code:

SELECT
e.id, e.name,
p.id, p.productname, p.eid,
l.id, l.location, l.pid
/*** 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 - MSCEWI1060 - TABLE 'E' WAS USED IN A NON-ANSI OUTER JOIN BEFORE. NON-ANSI OUTER JOIN TO MULTIPLE TABLES IS NOT SUPPORTED IN SNOWFLAKE **
--e.id(+) = l.id
AND p.id(+) = l.pid;

Recommendations

• Rewrite the statement using ANSI Outer Join syntax.

The best way to avoid this EWI would be to rewrite the query, here is the previous example code written using only ANSI outer joins:

SELECT
e.id, e.name,
p.id, p.productname, p.eid,
l.id, l.location, l.pid
FROM
LOCATION l LEFT OUTER JOIN PRODUCT p
ON p.id = l.pid
LEFT OUTER JOIN EMPLOYEE e
ON e.id = p.eid AND e.id = l.id;

Using 'SELECT *' may affect the order of how the columns are displayed.

• Check for unfinished conditionals (AND, …) on the translated code.

• If you need more support, you can email us at [email protected]