Post Upgrade from ODI 11g to ODI 12c - Incorrect SQL Code is Generated for Filters that Implement Hard Coded Outer Joins (+)
(Doc ID 2623335.1)
Last updated on JUNE 25, 2023
Applies to:
Oracle Data Integrator - Version 12.1.2.0.0 to 12.2.1.3.0 [Release 12c]Information in this document applies to any platform.
Symptoms
After having upgraded Oracle Data Integrator (ODI) from 11g to 12c, the SQL code generated loses the (+) outer join symbol in Mappings Filters.
For example, if an ODI 11g Integration Interface / 12c Mapping defines:
- an outer join between Datastore A and B
- another outer join between Datastore C and A
- and a filter on Datastore C
.. in ODI 11g, the SQL code generated uses TABLE_B filter first, then outer join with TABLE_A, and TABLE_C outer join TABLE_A:
insert into <I$ TABLE NAME>
(
...
)
select
TABLE_A.COLUMN1,
TABLE_A.COLUMN2,
TABLE_C.COLUMN3
...
from <TABLE A> TABLE_A, <TABLE B> TABLE_B, <TABLE C> TABLE_C
where
(1=1)
and (TABLE_A.COLUMN1 = TABLE_B.COLUMN1 and TABLE_A.COLUMN2 = TABLE_B.COLUMN2(+))
and (TABLE_A.COLUMN3 = TABLE_C.COLUMN3(+))
and (TABLE_B.COLUMN4(+) = <A VALUE>
... while in ODI 12c, the code generated has two issues:
- The SQL code generated uses (TAB_B outer join to TAB_A) as INLINE_VIEW, then TAB_C outer join INLINE_VIEW... and
- The outer join (+) sign in the Filter is not generated:
insert into <I$ TABLE NAME>
(
...
)
select
...
from (
select
INLINE_VIEW.COLUMN1,
INLINE_VIEW.COLUMN2,
TABLE_C.COLUMN3
from
(
select
TABLE_A.COLUMN1,
TABLE_B.COLUMN2,
TABLE_A.COLUMN3,
TABLE_B.COLUMN4
from <TABLE A> TABLE_A, <TABLE B> TABLE_B
where
( TABLE_A.COLUMN1 = TABLE_B.COLUMN1(+) and TABLE_A.COLUMN2 = TABLE_B.COLUMN2(+)
)
) INLINE_VIEW, TABLE_C TABLE_C
where
(INLINE_VIEW.COLUMN3 = TABLE_C.COLUMN3(+)
)and (INLINE_VIEW.COLUMN4 = <A VALUE>
)
) DIST_COMP
where
(1=1)
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |