Hash Table as left outer join

suri · Post by **suri** » Wed Jul 14, 2004 4:12 pm

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

vmcburney · Post by **vmcburney** » Wed Jul 14, 2004 5:25 pm

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.

rasi · Post by **rasi** » Wed Jul 14, 2004 5:30 pm

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

suri · Post by **suri** » Thu Jul 15, 2004 4:22 pm

Thanks for the replies.

Suresh