LookUp In ODI11g

Source table is EMP and the lookup table will be DEPT. Just click on the lens in your interface. A new window will come up.
I am selecting the DEPT as the lookup table just right to the pane.

Select columns from both side and click on Join.

keep in mind that the Lookup Type is SQL Left-Outer Join in the from clause.  Once you will click on finish you can see the lookup table in the source pane (Parrot Color)

Lets run the interface to see the loaded data and code generated in operator.

Hmm data looks fine. Here is the code generated.
Lookup Type: SQL left outer Join in the from clause
/* DETECTION_STRATEGY = NOT_EXISTS */
insert /*+ append */ into SCOTT.I$_EMP_LKUP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
DNAME,
IND_UPDATE
)
select
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
DNAME,
IND_UPDATE
from (
select
EMP.EMPNO EMPNO,
EMP.ENAME ENAME,
EMP.JOB JOB,
EMP.MGR MGR,
EMP.HIREDATE HIREDATE,
EMP.SAL SAL,
EMP.COMM COMM,
DEPT.DEPTNO DEPTNO,
DEPT.DNAME DNAME,
‘I’ IND_UPDATE
from SCOTT.EMP    EMP LEFT OUTER JOIN SCOTT.DEPT    DEPT ON EMP.DEPTNO=DEPT.DEPTNO
where (1=1)
) S
where NOT EXISTS
( select 1 from SCOTT.EMP_LKUP T
where T.EMPNO = S.EMPNO
and ((T.ENAME = S.ENAME) or (T.ENAME IS NULL and S.ENAME IS NULL)) and
((T.JOB = S.JOB) or (T.JOB IS NULL and S.JOB IS NULL)) and
((T.MGR = S.MGR) or (T.MGR IS NULL and S.MGR IS NULL)) and
((T.HIREDATE = S.HIREDATE) or (T.HIREDATE IS NULL and S.HIREDATE IS NULL)) and
((T.SAL = S.SAL) or (T.SAL IS NULL and S.SAL IS NULL)) and
((T.COMM = S.COMM) or (T.COMM IS NULL and S.COMM IS NULL)) and
((T.DEPTNO = S.DEPTNO) or (T.DEPTNO IS NULL and S.DEPTNO IS NULL)) and
((T.DNAME = S.DNAME) or (T.DNAME IS NULL and S.DNAME IS NULL))
)
Now if you change the lookup type to SQL Expression in the select clause, you will get the same result but the generated code will be little different. Here it goes.
/* DETECTION_STRATEGY = NOT_EXISTS */
insert /*+ append */ into SCOTT.I$_EMP_LKUP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
DNAME,
IND_UPDATE
)
select
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
DNAME,
IND_UPDATE
from (
select
EMP.EMPNO EMPNO,
EMP.ENAME ENAME,
EMP.JOB JOB,
EMP.MGR MGR,
EMP.HIREDATE HIREDATE,
EMP.SAL SAL,
EMP.COMM COMM,
(Select DEPT.DEPTNO From SCOTT.DEPT DEPT where EMP.DEPTNO=DEPT.DEPTNO) DEPTNO,
(Select DEPT.DNAME From SCOTT.DEPT DEPT where EMP.DEPTNO=DEPT.DEPTNO) DNAME,
‘I’ IND_UPDATE
from SCOTT.EMP EMP
where (1=1)
) S
where NOT EXISTS
( select 1 from SCOTT.EMP_LKUP T
where T.EMPNO = S.EMPNO
and ((T.ENAME = S.ENAME) or (T.ENAME IS NULL and S.ENAME IS NULL)) and
((T.JOB = S.JOB) or (T.JOB IS NULL and S.JOB IS NULL)) and
((T.MGR = S.MGR) or (T.MGR IS NULL and S.MGR IS NULL)) and
((T.HIREDATE = S.HIREDATE) or (T.HIREDATE IS NULL and S.HIREDATE IS NULL)) and
((T.SAL = S.SAL) or (T.SAL IS NULL and S.SAL IS NULL)) and
((T.COMM = S.COMM) or (T.COMM IS NULL and S.COMM IS NULL)) and
((T.DEPTNO = S.DEPTNO) or (T.DEPTNO IS NULL and S.DEPTNO IS NULL)) and
((T.DNAME = S.DNAME) or (T.DNAME IS NULL and S.DNAME IS NULL))
)
Okay, if we will do the direct join then what will be the generated code ? Interesting Right ? So delete the lookup table and do the direct join with DEPT table.

and the generated code in operator goes right here
/* DETECTION_STRATEGY = NOT_EXISTS */
insert /*+ append */ into SCOTT.I$_EMP_LKUP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
DNAME,
IND_UPDATE
)
select
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
DNAME,
IND_UPDATE
from (
select
EMP.EMPNO EMPNO,
EMP.ENAME ENAME,
EMP.JOB JOB,
EMP.MGR MGR,
EMP.HIREDATE HIREDATE,
EMP.SAL SAL,
EMP.COMM COMM,
DEPT.DEPTNO DEPTNO,
DEPT.DNAME DNAME,
‘I’ IND_UPDATE
from SCOTT.EMP EMP, SCOTT.DEPT DEPT
where (1=1)
And (EMP.DEPTNO=DEPT.DEPTNO)
) S
where NOT EXISTS
( select 1 from SCOTT.EMP_LKUP T
where T.EMPNO = S.EMPNO
and ((T.ENAME = S.ENAME) or (T.ENAME IS NULL and S.ENAME IS NULL)) and
((T.JOB = S.JOB) or (T.JOB IS NULL and S.JOB IS NULL)) and
((T.MGR = S.MGR) or (T.MGR IS NULL and S.MGR IS NULL)) and
((T.HIREDATE = S.HIREDATE) or (T.HIREDATE IS NULL and S.HIREDATE IS NULL)) and
((T.SAL = S.SAL) or (T.SAL IS NULL and S.SAL IS NULL)) and
((T.COMM = S.COMM) or (T.COMM IS NULL and S.COMM IS NULL)) and
((T.DEPTNO = S.DEPTNO) or (T.DEPTNO IS NULL and S.DEPTNO IS NULL)) and
((T.DNAME = S.DNAME) or (T.DNAME IS NULL and S.DNAME IS NULL))
)