Lookup Failing when data should match

jpr196 · Post by **jpr196** » Wed Mar 07, 2007 3:02 pm

Hi,

Maybe someone can keep me from going insane here. My lookup fails for all rows when I know there are definitely rows that match between the source and lookup.

The source is a drs stage connecting to DB2. My lookup connects to db2 via drs first and passes the dataset to a hashed file then to the transformer. There are about 90000 rows in the source and approximately the same in the lookup.

When I run the job, I can see that the 90000 rows go to the hashed file from the lookup, yet nothing is matching my 90000 rows from the source. When I look at the target table, I can see that there are values that match my lookup. Does anyone have an idea as to why my lookup and source might not match correctly?

Thanks in advance for the help!

DaleK · Post by **DaleK** » Wed Mar 07, 2007 3:15 pm

Take a close look at the key field in the hash file and in the source. My guess is that one of them has a leading or trailing space or Zero.

If the key is a number and it is the hash file that has the leading zero, I normally run the data through a trasformer before the hash file and just add 0 (zero) to the key then out to the hash file. This will strip the leading zeros off. If it is the source data hat has the leading zeros then add zero to the value in the key field of the look up.

If it is spaces trailing or leading then do a trim on the correct field or both if you want to be safe.

Hope this helps.

michaeld · Post by **michaeld** » Wed Mar 07, 2007 3:33 pm

-check that the data types are the same ... example varchar is not like char becuase char adds padded characters.

-check if your partitioning is set correctly.

-double check if your lookup file actuallyhas data in it.

jpr196 · Post by **jpr196** » Wed Mar 07, 2007 3:57 pm

All the data types are correct. I'm unsure of what you mean by check if your partitioning is correct? Sorry, but can you explain that please?
The lookup file actually has data in the input and oddly enough output. But for whatever reason, it won't match the source.

Also, I tried to run just the source and this one lookup to a stripped down job and the job works. I should have mentioned that there are appr. 30 lookups altogether on this job spread out among 5 transformers. The other lookups seem to be working fine however.

Another thing I noticed is that when I eliminate the hash file and just connect the drs stage to the transformer, the lookup also works.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 07, 2007 4:25 pm

What ARE the data types?
Make sure to TRIM() any VarChar columns when populating the hashed file, and to apply TRIM() in the reference key expression when generating the lookup key, so that neither has unnecessary white space characters.

jpr196 · Post by **jpr196** » Wed Mar 07, 2007 4:51 pm

There are 5 keys with a char, date, smallint, smallint, and char datataypes. I am using LTRIM(RTRIM in the derivation in drs stage and TrimF(TrimB( on the key expression in the transformer.

Something else I forgot to mention. I am joining 2 tables in the drs stage to populate the hash file. Will this cause a problem? Thanks.

us1aslam1us · Post by **us1aslam1us** » Wed Mar 07, 2007 6:00 pm

jpr196 wrote: Something else I forgot to mention. I am joining 2 tables in the drs stage to populate the hash file. Will this cause a problem? Thanks.

It might be. Are you able to view data in the DRS stage after making the join? Also as suggested, Have you verified the data types and length of key fields in your source and lookup are same? Do not complicate the thing use TRIM() when populating the hashed file then while performing a lookup use TRIM() in reference key expression.

ganesh123 · Post by **ganesh123** » Wed Mar 07, 2007 6:20 pm

Also very minor yet very important, check the CASE. Lookup is case-sensitive.

jpr196 · Post by **jpr196** » Wed Mar 07, 2007 7:11 pm

Well, you guys were right. There was a data mismatch in the hashed file. I needed to place a transformer between the drs and hashed file to truncate the seconds from my timestamp. When I did this, the lookup worked fine. Thanks for the help everyone!