hi,
I'm trying to write a temporary (yellow) interface that will act as a source for a standard (blue) interface.
However when I run the standard interface, I'm getting the following error message on Step 8 - Integration: Insert flow into I$ table
ODI-1228: Task CR_2 (Integration) fails on the target ORACLE connection ORACLE_Local.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "T"."R_ID": invalid identifier
Target Code:
/* DETECTION_STRATEGY = NOT_EXISTS */
insert /*+ append */ into THE_SCHEMA.I$_CR_TABLE
(
CNUMBER,
R_ID,
IND_UPDATE
)
select
CNUMBER,
R_ID,
IND_UPDATE
from (
select
C1_CNUMBER CNUMBER,
C2_R_ID R_ID,
'I' IND_UPDATE
from THE_SCHEMA.C$_0CR_TABLE
where (1=1)
) S
where NOT EXISTS
( select 1 from THE_SCHEMA.CR_TABLE T
where T.R_ID = S.R_ID
and ((T.CNUMBER = S.CNUMBER) or (T.CNUMBER IS NULL and S.CNUMBER IS NULL))
)
Source : DB2 v9.1.0.7,
Target : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
ODI v11.1.1
On the standard interface:
I've set the check box for "use temporary interface as derived table (sub-select)".
The R_ID field on the target table is the key field. It is mapped to one of the fields from the temp interface and it set to "Execute on: Source" with both Insert and Update checkboxes set.
I'm using the LKM SQL to Oracle component to get the data from the temp interface to the target.
On the temp interface:
No key field set for either field in the target
Using IKM DB2 Incremental Update, with FLOW_CONTROL and STATIC_CONTROL set to false.
Any assistance would be greatly appreciated!