I'm trying to validate the records...so the records that are NOT in the hashed_file get inserted into the second OCI Stage but when I run the job, nothing is getting inserted....
I have tried with isnull(link_name.field) and with (link_name.NOTFOUND)
If your source link count is the same as your lookup link count then that means that there are no inserts, if they are not the same then you should have inserts and if you see a count of zero on your output link then you need to peek in your log as you might have warnings.
If all to no avail then make sure your hashed file name is right. Even your metadata for the hashed file should be identical to the metadata used while creating the hashed file.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
There's only one output link from the transformer, so it doesn't really make sense to have it as a reject link. As noted, first double-check everything - that you are referencing the right hashed file, using a proper key value, etc etc.
Then let us know what counts show in the monitor when you run the job. Specifically you have X rows into the transformer and 0 rows out but I'm curious what does it show for the number of rows on your reference link. That number is the number of successful lookups, for an 'all inserts' scenario (all hash lookup failures) that number should be zero.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Whoops - misread it. I saw the hashed file as an output, not an input.
So, If I am understanding both the problem and Craig's explanation, the count on the hashed file link plus the count from the output link should equal the input count.
Input Count = Hashed Reads + Output Count
As long as that relation is holding true, the stage is functioning correctly.
If it is know that the input data includes records that should be output and are not, the troubleshooting needs to begin looking at how the inputs are being joined.
jcthornton wrote:So, If I am understanding both the problem and Craig's explanation, the count on the hashed file link plus the count from the output link should equal the input count.
If my understanding of the design is correct as well - that a reference hashed file of existing keys is checked and only new keys are allowed past to be inserted into the target - then yes, those counts should 'foot' as you've noted.
-craig
"You can never have too many knives" -- Logan Nine Fingers
You might have already checked this....but can you verify that the
a.) keys with which the hashed file is created contains the same key you expect in the reference
b.) the key in the current job under discussion is correct
Also it will be helpful if you can locate few values in hashed file which are going to target - which you think is happening. That way we are certain and have ratified the assumption.
