Problem with the oracle stage lookup

[mouthou]

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,

[mikegohl]

Can you post the SQL that got generated?

[sachin1]

putting a constraint could also help

[satya99]

If Isnull(trimf(trimb("you column"))) or trimf(trimb("you column")) = '' Then @True Else @false

[mouthou]

I guess you are looking for the generated sql in look up.


SELECT "CLI_COV_CODE","CLI_LMT_DED_CSL_IND","CLI_CLM_NO" FROM "T_CLAIM_LIMIT_DW" WHERE "CLI_COV_CODE"=:1 AND "CLI_LMT_DED_CSL_IND"=:2 AND "CLI_CLM_NO"=:3

[chulett]

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.