Lookup in ODI 11g

Lookup in ODI 11g:

Many people have confusion about Lookup in ODI (Specially Newbie) which is not a big deal in reality. Today I am going to explain itstep by step and will try my best to clear this confusion.

Okay I will consider that you have already created an interface. 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.

lookup0

I am selecting the DEPT as the lookup table just right to the pane.
lookupstep1
Select columns from both side and click on Join.
lookupstep2

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

Lets run the interface to see the loaded data and code generated in operator.
sqlleftouter
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.

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

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  😀

0

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.

Similar Posts

13 comments

Thank you for this post on look up tables. Could you please explain the additional purpose of this feature when you have the facility to drag more than one table in the interface directly.

Hi Sir…………

look up is possible on file system in oracle data integrator

Thanks&Regards
Manohar

Yes it is possible on filesystem we have to select LKM and IKM execute on staging bcoz driving table and source are different database schemas.hope this helps
let me know if any wrong

hi
what is advantage of lookup.what is the main difference between lookup and direct join.i which scenario we can use them.can anybody explain briefly

Hi Bhabani,

Thanks for the post.
One thing: you should perhaps mention which Knowledge module you are using in your example, as this has some consequences. As well important is the detection strategy.

Cheers,
Andreas

Thank You Andreas for your feedback. I will update today once my office is over.
Thanks Again.

Bhabani

Hi Bhabani,

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.?

Look forward to hearing from you. Thanks.

Cheers.
Jeby

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

Make sense? Feel free to ask any doubts in ODI.

Hi Bhabani,

Thank you for your reply. I had done exactly as you mentioned above.

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.

Thanks.
Jeby

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..

Many Thanks,
Yaswanth

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.

Thanks

Hi sir,

Its very helpfull for us..

Thanks you very much

Hi Bhabani,

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.

Regards,
Bhageerath Mishra

Leave a replay to yaswanth Cancel reply

required*

Are you a human? *