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.

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_UPDATEfrom 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))
)

I am pretty sure that you are smart enough to understand the difference in all approaches (If not, then do some research in join and correlated subquery, I stated above. Most of the time, if you are looking for value or a single row in another table, you can go to lookup. But join is more generic like, what kind of result set you want to produce by joining multiple table with certain join conditions. So it depends on you to decide the best fit for your requirement.
Okay buddy, that’s all for today. Let me know if you have got doubts.
Cheers 😀

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page.
If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

I am using the lookup with Lookup Type : SQL left-outer join in the from clause. But when I see the query generated, there is no outer join on the lookup table. I also want to filter the lookup table like Dept_Name not like ‘%HR%’ and then apply the lookup. Could you please tell me how this would be possible?

Can we also apply a condition on the target column ie. if no matches are found from the lookup table then populate with source table column else populate with lookup table column.?

Hi Jeby, when you click on the look up table you can see the join condition. You just have to add additional condition there. For example EMP.DEPTNO=DEPT.DEPTNO and DEPT.DNAME NOT LIKE ‘%SALES%’
. Regarding condition on target, use case or decode in the target expression.
For example
case when DEPT.DNAME is null then ‘NA OR ANYSOURCE COLUMN’ else DEPT.DNAME end

I have for eg. depts – Sales, HR, Finance etc but want to perform the lookup only for HR. hence I used the join condition like you mentioned and used Lookup Type : SQL left-outer join in the from clause.

But what happens is, the SQL generated shows a normal join instead of left outer and hence only HR dept’s records are getting loaded in the target table.

I think it would be better to do an outer join on the
lookup table instead of lookup.

Hi Bhabani,
I am using lookup in one temperory interface and trying to use this as a source in the main interface. But, the subselect option is disabled. Can you please suggest me how to use the lookup in temp interface. If there is no lookup then subselect option is enabled..

Hi Yaswanth,
Sorry for the delayed response. You can definitely select the subselect check box of temp interface. However you can not use the temp interface as look up as it would be the driving table. This driving table will be used to look up from another table. Let me know if you didn’t get it.

I have read posted ODI notes with comments that is very useful.I am very luck to read this.I have a one question for odi.

1).Can I use unconnected Lookup T/R concept in odi.is it posible if it is posible then how to create and use.
2).How can load 10 xml files into one oracle table at a time? could you please let me know. Thank you very much for posted odi materials.

Disclosure

The views expressed on this blog are those of the author and do not necessarily reflect the views of Oracle. All content and s/w code on this site are offered without any warranty, or promise of operational quality or functionality.