Hi,
I would like to know is Hash table can be used as left outer join.
If yes, please give a small example.
Thanks
Suresh
Hash Table as left outer join
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
A hash file as a reference lookup is automatically a left outer join. If a matching row is not found in the hash file then null values are returned in the transformer fields for the hash file. It is up to the developer to handle input rows that do not have a lookup match using constraints and derivations.
Use hashfilelinkname.NOTFOUND within your transformer to work out when you don't have a match.
Use hashfilelinkname.NOTFOUND within your transformer to work out when you don't have a match.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Hi Suresh,
In Datastage its upto in what way you want to process. Main dataset you call it as main stream and the lookup is called as reference. If you have your main from database or from any source the lookup can also be any source(hash or database). All your main dataset passes from source to target if you haven't mentioned any constraint in your transformation. If the join mataches from the lookup then you will get the relavent information from your lookup hash file. The way it behaves is it outer joins the hash lookup and puts all the records from your main stream. If you dont want outer join then you have conditions in your constraint in the transformation saying NOT(Lookup.NOTFOUND).
Best of luck
Rasi
In Datastage its upto in what way you want to process. Main dataset you call it as main stream and the lookup is called as reference. If you have your main from database or from any source the lookup can also be any source(hash or database). All your main dataset passes from source to target if you haven't mentioned any constraint in your transformation. If the join mataches from the lookup then you will get the relavent information from your lookup hash file. The way it behaves is it outer joins the hash lookup and puts all the records from your main stream. If you dont want outer join then you have conditions in your constraint in the transformation saying NOT(Lookup.NOTFOUND).
Best of luck
Rasi