How do I modify an IKM in ODI 12c to prevent duplicates in a target table?

Ngoc Phung

Hi everyone,

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 (exception of the DATE_CREATE), 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.

Gianni Ceresa

Hi,

ODI is not an Oracle Analytics product. It is partially covered in this forum when it's about the ODI mappings included in OBIA.

Yours seems to be a purely ODI question, without an Oracle Analytics connection.

You could try visiting one of these 2 other forums and see if your question would fit better there:

https://forums.oracle.com/ords/apexds/domain/dev-community/category/analytics-software?tags=odi

https://community.oracle.com/customerconnect/categories/oci-data-integration