MSCEWI3087

Ordering of the Outer Joins failed

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

Severity

Low

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:

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:

SELECT
l.location_id, l.state_province,
r.region_id, r.region_name,
c.country_id, c.country_name
/*** MSC-WARNING - MSCEWI3003 - NON-ANSI OUTER JOIN SYNTAX CONVERTED TO ANSI SYNTAX ***/
FROM /*** MSC-WARNING - MSCEWI3087 - 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;

Recommendations

• 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 [e-mail]