E-QR: PS Query Incorrectly Builds the RunControl Table and Left Outer Join as a Standard Join, the Resulted SQL is Missing the ON Criteria Logic and Creates a Standard Join Logic with WHERE Criteria (Doc ID 3073882.1)

Last updated on FEBRUARY 25, 2025

Applies to:

Symptoms

When attempting to use left outer join, the SQL produced is missing the ON criteria logic and creates a standard join logic with WHERE criteria.

The issue can be reproduced at will with the following steps:
1. Navigate to Reporting Tools > Query > Query Manager
2. Create a new PS Query (e.g. SB_TEST_LOJ)
3. Records used:
4. Select "Join to get additional fields only (Left outer join)" to left outer join the records
5. View Query SQL
Query SQL

SELECT A.OPRID, A.RUN_CNTL_ID, B.SCC_VFA_MESSAGE_CL
FROM (PS_PRCSRUNCNTL A LEFT OUTER JOIN PS_SCC_RUNCNTL_VOC B ON A.OPRID = B.OPRID AND A.RUN_CNTL_ID = B.RUN_CNTL_ID )
WHERE ( A.OPRID = 'PS'
AND B.OPRID = 'PS')
6. Go to Criteria tab
7. Click on Edit on the affected criteria
8. Noticed that the "ON clause of outer join B" under "This criteria belongs to" section is correctly set by default.
9. In the Query SQL, observe that the resulted SQL adds the criteria to the WHERE clause instead of the ON clause.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.