ODI Tip: How to make sure a “Select distinct” is issued and an ODI interface returns a unique dataset with no duplicates

PROBLEM

 

As a developer I do have a need to make sure that the subset of columns I am mapping through from source to target on my ODI interface is unique, in other words, I want ODI to include a DISTINCT clause on the SELECT statement that will be issued on the source database.

 

SOLUTION

  • Open my interface on the ODI Interface designer
  • Click on the Flow tab on the bottom
  • Click on the Target object
  • On the Property Inspector, click on the “Distinct Rows” checkbox

    image

ODI Error: ora-00001–unique constraint ( pk_pop ) violated

SYMPTOM:

 

image_thumb1

odi-10182: Uncategorized exception during repository access

ora-00001: unique constraint ( pk_pop ) violated

 

CAUSE:

 

There is another interface with the same name on the repository.  Interface names are unique and independent of their folder location. In my case I had an interface with the same on a different folder.

 

SOLUTION:

 

Rename or delete your duplicate interface.