I have a job where i have an oracle source Tbl1 and i need to do an lookup with another oracle table Tbl2. But instead of using a hash file,i
would like to use a custom sql and join them.
so i am doing an left outer join.But i see that i get more no of records than the no.of records in Tbl1.Since i am doing an left outer join,i should be replicating the functionality of a Lookup.Am i right.
Lookup
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 73
- Joined: Wed Jun 28, 2006 3:27 pm
- Location: NJ
As you are doing Left outer join its going to take all records of left table i.e TBL1 and only matching records from TBL2 based upon the key.
Instead as you want to do look up you can use TBL2 for lookup directly instead of hashed file, no need to join.
Instead as you want to do look up you can use TBL2 for lookup directly instead of hashed file, no need to join.
"Nobody is expert in Everything,
But Everybody is expert in Something."
But Everybody is expert in Something."
-
- Participant
- Posts: 73
- Joined: Wed Jun 28, 2006 3:27 pm
- Location: NJ
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you WANT the duplicates, do nothing. A simple left outer join will deliver all the duplicates from Tbl1, and mimic the behaviour of DataStage lookup. If you want to remove the duplicates, then an approach such as an inner self-join with the distinct key values would suffice.
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.