I was practicing ODI 12c and I came across this problem:
I have a mapping in ODI 12c that inserts the data from a staging table into a staging history table. The logical diagram is:
source: staging table -> FILTER -> EXPRESSION -> target: staging history table
In the physical tab of the mapping, I set:
EXPRESSION_AP: uses LKM SQL to Oracle (Built In).GLOBAL
staging history table: uses IKM Oracle Control Append for its IKM ( and CKM Oracle for its CKM
Let's say it's May 16th 2025 15:47:42.000, If I run the mapping once, a record will be inserted into the target table with its field DATE_CREATE = 2025-05-16 15:47:42.000
Then I run the mapping again at 15:50:05.000, the table will now contain 2 records, the 2nd one has its field DATE_CREATE = 2025-05-16 15:50:05.000.
These two rows have the same data on every column except the DATE_CREATE, where the only distinction is the hour:minute:second part. So I want to modify the mapping in some way that:
If it runs multiple times and the records are the same, keep the result from the latest running time.
If it runs multiple times and each of the records is unique (exception of the DATE_CREATE) then keep all of them.
I suspect it has something to do with the IKM I'm using on my staging history table but I don't know where to start and what should I add to the IKM.
I have found a solution: use IKM Oracle Control Append, add an option "Keep latest record" in the Options tab, then add one more task of the same name as the said option before the task Insert new rows in the Execution Unit Main. The task contains this code:
DELETE FROM <%=odiRef.getTable("L", "TARG_NAME", "A")%>
WHERE <%=odiRef.getColList("(", "[COL_NAME] ", ", ", ")", "UD1")%> = TO_DATE(#test.p_date,'YYYYMMDD')
The code above will generate this sql code:
DELETE FROM STG_HIST.NG_BRANCH_HIST
WHERE TRUNC((DATE_CREATE)) = TO_DATE( #test.p_date, 'YYYYMMDD')
When I run the mapping with this customed IKM in the target table, it deletes any record that has its DATE_CREATE matching the date I declare in p_date, before inserting the latest record.
Note that the DATE_CREATE needs to have its UD1 box checked in the logical tab of the mapping.