Problem with the oracle stage lookup
Posted: Fri Mar 28, 2008 10:09 am
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,
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,