Hi,
When oracle stage is used as the look up, it does not try matching all the look up/key columns.
E.g
Source:
Key1 Key2 Key3
1 A X
2 B Y
3 C Z
Look up:
Key1 Key2 Key3
1 A P
2 B Q
3 C Z
I am expecting only the last row to be matched (all the 3 columns are linked in the transformer) but instead it is taking the first column and finding all of them as the match. Also is there a problem in using Lookup.NOTFOUND variable. I tried getting the value of it for NOT(NOTFOUND). It always say the match, which is also surprising.
So as a work around, I had to hash the keys in a hash file and use the hash file as the look up. Is that a known issue in datastage.
Thanks for your help,
Problem with the oracle stage lookup
Moderators: chulett, rschirm, roy
Re: Problem with the oracle stage lookup
putting a constraint could also help
The NOTFOUND Link Variables are known to not work with Database reference lookups. At least I can vouch for the fact that it doesn't work with the OCI stage. So you need to go Old Skool and check a Key field for null to indicate a failed lookup.
It is not only using the first column for the lookup if you have all three fields marked as Key fields. You can see that in the SQL you posted. Your issue there lies elsewhere.
It is not only using the first column for the lookup if you have all three fields marked as Key fields. You can see that in the SQL you posted. Your issue there lies elsewhere.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers