Scenario : How to remove duplicate in one table and unique value in another target(oracle tables)

In this scenario, We will consider source as a oracle table with few duplicate values.


Source table structure :

create table employees_duplicate(

emp_id number(5) ,

empname varchar2(10)

);


Insert few records in source table - employees_duplicate


insert into employees_duplicate values (100,'Robert');

insert into employees_duplicate values (100,'Robert');

insert into employees_duplicate values (101,'Simon');

insert into employees_duplicate values (101,'Simon');

insert into employees_duplicate values (102,'John');


Commit;


Now, Create two target tables

a) emp_dup_duplicate - This table holds duplicate emp_id values or having count of emp_id's greater than one.


b) emp_dup_unique - This table holds emp_id values whose count is one in source table.


Target table structures :


create table emp_dup_duplicate(

emp_id number(5) ,

empname varchar2(10)

);


create table emp_dup_unique(

emp_id number(5) ,

empname varchar2(10)

);


Now we will see mapping skeleton in Informatica designer client.



Now, I will explain you configuration at each transformation level.


Source qualifier transformation :

In Source qualifier transformation, We will add SQL override query which will list number of records based on emp_id.


Select emp_id ,empname ,

row_number() over (partition by emp_id order by empname) rank_1

from employees_duplicate ;


Expression transformation :

We have no configuration in this transformation. We can remove this transformation from data pipeline.


Router transformation :


We will create 2 groups in Router transformation, the configuration would looks like


Condition 1 > rank_1 =1

Condition 2 > rank_1 <> 1


First condition will get connected to target table emp_dup_unique to get unique values from source.

However, second condition would load data into target table emp_dup_duplicate.


Thank you !! I will keep you posted with latest scenarios in this series of Informatica Powercenter real time scenarios.



12 views0 comments

Recent Posts

See All