How to dump duplicate records into one table and Non-Duplicate records into one table in ODI 12c

Pre-requisites:

Scripts:

CREATE TABLE STUDENT

(SID NUMBER(2),

NAME VARCHAR2(50),

MARKS NUMBER(3));

/

INSERT INTO STUDENT VALUES(1,'Trinesh',99);

INSERT INTO STUDENT VALUES(2,'Tejesh',98);

INSERT INTO STUDENT VALUES(3,'Bapiraju',100);

INSERT INTO STUDENT VALUES(4,'Seetharamaraju',99);

INSERT INTO STUDENT VALUES(4,'Raju',100);

INSERT INTO STUDENT VALUES(5,'Dinesh',98);

INSERT INTO STUDENT VALUES(5,'Sai',100);

INSERT INTO STUDENT VALUES(6,'Asim',100);

/

COMMIT;

/

CREATE TABLE DUP_STUDENT

AS

SELECT * FROM STUDENT WHERE 1<>1;

/

CREATE TABLE NONDUP_STUDENT

AS

SELECT * FROM STUDENT WHERE 1<>1;

Process:

1.      Reverse Engineer above datastores and make sure those are available in your model

2.      Create new mapping

3.      Drag and drop STUDENT, DUP_STUDENT and NONDUP_STUDENT datastore

4.      Drag and drop  2 Aggregate component from component palette

5.      Connect output port of Source datastore (STUDENT) to input port of aggregate component. It will pop-up as follows. Leave it as it and click ok

6.      Select Aggregate Component and go to property inspector à General à In Side having clause mention as “count(*) >1” and in side Manual group by clause mention as “STUDENT.SID” and remove all attributes except SID.

As shown in below screen

7.      Drag and drop the STUDENT datastore once again and drag and drop Join component into mapping editor from component palette

8.      Join Aggregate component 1 to STUDENT Datastore as “AGGREGATE.SID = STUDENT1.SID”

9.      Connect output port of Join component to input port for Target datastore (DUP_STUDENT). It will pop-up then disable “Create attribute On source” and click ok

10.  Now connect output port of Source Datastore (STUDENT) to input port of Aggregate component 2. It will pop-up, leave it as it and click ok

11.  Remove all attributes except “SID” from distinct Component

12.  Select Aggregate Component and go to property inspector à General à In Side having clause mention as “count(*) = 1” and in side Manual group by clause mention as “STUDENT.SID” and remove all attributes except SID

13.  Drag and drop Join Component again into mapping editor.

14.  Join Aggregate component and STUDENT1 datastore as above diagram as “AGGREGATE1.SID = STUDENT1.SID

15.  Connect output port of join to the input port of target datastore (NONDUP_STUDENT) and click ok

Completed !!!!!