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.