SSC-EWI-OR0087

Ordering of the Outer Joins failed

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