ODI 12c: Lookup two table data and insert into one table using Oracle Data Integrator 12c

In here, I am going to express how to lookup two table and and insert both table’s data into one table by combining. for that you have to create given below two tables and insert sample data for source table which we are going lookup.
Department table and sample data.

CREATE TABLE "SYSTEM"."DEPARTMENT" (
"DEPTNO" NUMBER(4,0),
"DEPARTMENT_NAME" VARCHAR2(10 BYTE),
PRIMARY KEY ("DEPTNO"));

Insert into DEPARTMENT (DEPTNO,DEPARTMENT_NAME) values (10,'HR');
Insert into DEPARTMENT (DEPTNO,DEPARTMENT_NAME) values (20,'Finance');
Insert into DEPARTMENT (DEPTNO,DEPARTMENT_NAME) values (30,'IT');

Employee_SRC table and sample data.

CREATE TABLE "SYSTEM"."EMPLOYEE_SRC" (
"EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
PRIMARY KEY ("EMPNO"));

Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (1,'KING','PRESIDENT',null,to_timestamp('17-NOV-81','DD-MON-RR HH.MI.SSXFF AM'),5000,null,10);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_timestamp('01-MAY-81','DD-MON-RR HH.MI.SSXFF AM'),2850,null,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_timestamp('09-JUN-81','DD-MON-RR HH.MI.SSXFF AM'),2450,null,10);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('02-APR-81','DD-MON-RR HH.MI.SSXFF AM'),2975,null,20);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_timestamp('19-APR-87','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('03-DEC-81','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH.MI.SSXFF AM'),800,null,20);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_timestamp('20-FEB-81','DD-MON-RR HH.MI.SSXFF AM'),1600,300,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_timestamp('22-FEB-81','DD-MON-RR HH.MI.SSXFF AM'),1250,500,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_timestamp('28-SEP-81','DD-MON-RR HH.MI.SSXFF AM'),1250,1400,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_timestamp('08-SEP-81','DD-MON-RR HH.MI.SSXFF AM'),1500,0,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_timestamp('23-MAY-87','DD-MON-RR HH.MI.SSXFF AM'),1100,null,20);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_timestamp('03-DEC-81','DD-MON-RR HH.MI.SSXFF AM'),950,null,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_timestamp('23-JAN-82','DD-MON-RR HH.MI.SSXFF AM'),1300,null,10);

Then, Create a destination table using below script. using Oracle data integrator we load data from above two table to below table.

CREATE TABLE "SYSTEM"."EMPLOYEE_DEPARTMENT" (
"EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPT_NAME" VARCHAR2(20 BYTE),
PRIMARY KEY ("EMPNO"));

In the Topology you have to create Physical Architecture, Logical Architecture as given below. for the Physical architecture give correct connection details of the schema user where the DEPARTMENT, Employee_SRC and EMPLOYEE_DEPARTMENT are avaialable. If you need step by step to create logical architecture and physicl architecture please refer the my previouse post(ODI 12c: Data loading from Database table to Table using Oracle Data Integrator 12c).

Secondly, have to create Model folder and Model in the Designer tab to Reverse Engineer the required table(DEPARTMENT, EMPLOYEE_SRC and EMPLOYEE_DEPARTMENT ). If you need step by step to create Model folder and model and how to do the Revers Engineer process, please refer the my previouse post(ODI 12c: Data loading from Database table to Table using Oracle Data Integrator 12c). Once you done the Reverse Engineering process, you can see the table as below and you can view data also.
EMPLOYEE_SRC Table with Data.

DEPARTMENT Table with Data.

Create Project called ‘R & D’ in the Designer tab and create a new mapping (Lookup Two Table )under mapping folder by slecting the ‘New Mapping’ manu.

Then you can see created mapping sheet as below.

Drag and drop the source table (DEPARTMENT, EMPLOYEE_SRC) to Source area.

Drag and drop the Destination table to canverse as below.

We are going to lookup from EMPLOYEE_SRC table’s DEPTNO column to DEPARTMENT Table DEPTNO column. So drag and drop DEPTNO column from EMPLOYEE_SRC table to DEPARTMENT table DEPDNO column.thenwill appear Create Look up or Join window.at the movement we are doing Look up operation , so select the look up radio button and click ok.