Lookup of the hashed file fails

chulett · Post by **chulett** » Fri May 25, 2012 9:30 am

First, NOTFOUND should only be used for hashed file lookups. And lookups fail when there is no key match - it is an equi-join and the data must match exactly and that includes any spaces, trailing or otherwise.

Seyed · Post by **Seyed** » Fri May 25, 2012 10:17 am

Hi Craig,
I appreciate your assistance. I checked the data in the key columns again and noticed that even though visually the key values between the driver table and lookup file (hashed file) were equal, but the values in the driver table were made of only 6 characters, and the same values in the hashed file consisted of 8 characters including 2 padded spaces. I modified the SQL code in the ODBC driver table to pad 2 spaces at end of each key column and now this problem is resolved and the lookup is successful. I even changed back the definition of the constraint to

Code: Select all

If VTBLUSER_lookup.NOTFOUND then 'X' else 'N'

Thanks as always,

Seyed