OBIA 11g: ODI Lookup Generating INNER JOIN Instead Of LEFT OUTER JOIN (Doc ID 2266752.1)

Last updated on JUNE 13, 2017

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.10.1 and later
Information in this document applies to any platform.
Oracle Data Integrator 11.1.1.9.0
Oracle DB 12c
Data Source and version PSFT 9.2

Symptoms

ODI Lookup is generating INNER JOIN instead of LEFT OUTER JOIN

Customer is changing SIL_GLOtherFact.W_GL_OTHER_F Interface. It's
located under the BI Apps Project, Mappings Folder, SILOS folder, then
SIL_GLOtherFact_PERF, then Interfaces.

Open that Interface, then open the Quick-Edit Tab.
Go To the Lookups Section
Under the Lookup Expression:
SQ_W_GL_OTHER_FS.GL_SEGMENT2_ID=LKP_W_GL_SEGMENT_D2.INTEGRATION_ID
AND
SQ_W_GL_OTHER_FS.DATASOURCE_NUM_ID=LKP_W_GL_SEGMENT_D2.DATASOURCE_NUM_IDLKP_W_
GL_SEGMENT_D1 (General Ledger Segment Dimension), he is setting all these
"GL_SEGMENT_D2 through _D10" to "SQL Left-outer join in the from clause"

In the code produced in the Operator Session 15594520 -
SILOS_SIL_GLOTHERFACT, under Step 6 "Run SIL_GLOTHERFACT.W_GL_OTHER_F", then
Step 53, you will see the code where the code is actually using Inner Joins,
and not the Left Outer Joins as specified in the Interface.

Here's the Code:

 

Changes

 Customer has applied  patch BUG #18711839 to improve the performance.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms