Whats the differnce between a lkp and join
what does a lkp do...is it a inner join or right outer join or left outer join
any help will be appreciated
what does it mean COndition Not met and Lookup Failure
Thanks
lkp and join
Moderators: chulett, rschirm, roy
Lookup does the outer join based on your input stream. The other conditions are to state how to the lookup stage can react with the records when it doesn't find a match in the lookup stream.
Do a search, you will find some related post..
Do a search, you will find some related post..
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Lookup stage supports conditional lookup. That is, some condition that has to be met before the lookup is even attempted. Therefore there are separate rules available for handling the "condition not met" situation, where the lookup was not even attempted, and the "lookup failed" situation, where the lookup was attempted but the key value(s) not found.
Whether this is an inner or left outer join depends on your lookup failed rule. If it's "Continue" then the Lookup stage is performing a left outer join (and any output column derived from the reference link needs to be nullable). If it's "Drop" or "Reject", the you've got the result of an inner join going onto the Lookup stage output link. And if it's "Fail", and a lookup fails, then the job aborts and you don't have any kind of join.
Whether this is an inner or left outer join depends on your lookup failed rule. If it's "Continue" then the Lookup stage is performing a left outer join (and any output column derived from the reference link needs to be nullable). If it's "Drop" or "Reject", the you've got the result of an inner join going onto the Lookup stage output link. And if it's "Fail", and a lookup fails, then the job aborts and you don't have any kind of join.
Last edited by ray.wurlod on Wed Jan 03, 2007 2:59 am, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 1
- Joined: Tue Jan 02, 2007 11:56 pm
- Location: Bamgalore
Re: lkp and join
[quote="vsi"]Whats the differnce between a lkp and join
The main difference between lkp and join is the performance issue.when the size of reference file is more than the primary file u can go for join as it allocates more memory than lkp.
The main difference between lkp and join is the performance issue.when the size of reference file is more than the primary file u can go for join as it allocates more memory than lkp.
KARTHEEK.M
-
- Participant
- Posts: 54
- Joined: Wed Oct 25, 2006 11:07 pm
- Location: Hyderabad
Re: lkp and join
Hi, a lookup is basicaaly an Inner join. However the major difference which I can think of is the memory issue. What a lookup(Normal, not sparse) does is, it executes the query only once in the reference table and brings the whole bulk of data to the main memory and then performs the inner join on them. On the other hand Join queries the database each time and does not bring anything to the main memory. Though you might feel that Joini is a bit slow but thing is when there is huge number of data available as a result of querying the reference link, then using a lookup the job may crash due to memory constraint, which is not a case in Join. We prefer lookup when the ratio between source link and reference link is 100:1 or a very complex query is getting executed at the reference side.
Thanks n Regards
Sudeep
Thanks n Regards
Sudeep