DSXchange

Apologies for such a beginners question, no DS experience:

How do I perform a hashed file lookup. Specifically how do I set up my constraints..

I've created a transformer stage with two inputs. The format of both inputs is the same, one is my hashed file for lookup (lkReferenceLookup), the other comes from a previous stage with my data (lkValidRecords)

I've got two decimal columns, both columns are keys.

when the lookup doesn't succeed I want to send it out to my oracle link lkNewRecords, and went it does to a sequential file of duplicates, lkDuplicates.

in the transformer stage, I dragged both the columns from the lkValidRecords to the lkReferenceLookup, connecting the two.

(I read something about multi-column lookup, but I couldn't get any checkbox option to appear)

Then I dragged the columns from my input src (lkValidRecords) to both of the outputs.

This was sending all records down both branches. I read a post that I need to set up some constraints to prevent

I'm not sure whether the lookup is actually taking place... is there a way that I can check this.

so I have created two stage variables DuplicateRecord, NewRecord. I wrote expressions that looked like this

(lkValidRecords.column_1 = lkReferenceLookup.column_1) and (lkValidRecords.column_2 = lkReferenceLookup.column_2)

replacing = with NE for the reverse.

I used these as constraints on the outputs

This meant that nothing was sent down either branch.

Finally I tried. lkReferenceLookup.NOTFOUND on one of my stage variables and this made the hang..

thanks

i've changed the constraints on my outputs to lkReferenceLookup.NOTFOUND and not(lkReferenceLookup.NOTFOUND), but its sending the wrong record to the wrong output and its hangs after the ora-0001

to me it looks like its not doing the lookup

all my records are being sent down the didn't find a match link, I've viewed the job in designer and once I@ve run the job there it reports that 0 rows were looked up.

I've viewed the hashed file and the records are in there.. am just wondering if I need to define multirow lookup...

maybe its not possible to have both columns specified as the lookup key in a hashed file...

yes you can have two key colums in lookup.

1) make sure the column definitions are the same for the source and hash file.

2) try If ( IsNull(lkReferenceLookup.column_1) or IsNull(lkReferenceLookup.column_2)) as the constraint for the failed lookup stream.