SSC-EWI-OR0087

Ordering of the Outer Joins failed

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Severity

Low

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

Description

This issue happens when an error occurred while reordering the new ANSI JOIN clauses in a query that previously had outer joins with the (+) operator. A query with a cycle of tables joining each other in the WHERE clause can provoke this issue.

When this EWI is present, the JOIN clauses may not work properly due to their order.

Example Code

Input Code Oracle:

IN -> Oracle_01.sql

SELECT
l.location_id, l.state_province,
r.region_id, r.region_name,
c.country_id, c.country_name
FROM 
hr.countries c,  hr.regions r,  hr.locations l, hr.departments d WHERE 
l.location_id (+) = c.region_id AND 
c.region_id (+) = r.region_id AND 
r.region_id (+) = c.region_id AND
l.location_id (+) = d.location_id;

Output Code:

OUT -> Oracle_01.sql

SELECT
l.location_id, l.state_province,
r.region_id, r.region_name,
c.country_id, c.country_name
FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0087 - ORDERING THE OUTER JOINS FAILED. QUERY MAY NOT BEHAVE CORRECTLY ***/!!!
hr.departments d
LEFT OUTER JOIN
hr.locations l
ON
l.location_id = c.region_id
AND
l.location_id = d.location_id
LEFT OUTER JOIN
hr.countries c
ON
c.region_id = r.region_id
LEFT OUTER JOIN
hr.regions r
ON
r.region_id = c.region_id;

• Make sure the query is valid and does not have tables that are being joined to each other.

• If the issue still occurs, try qualifying the name of each column in the WHERE clause with the name of the table.

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