Looking up a Space in a Hash file

KV · Post by KV » Mon Jul 26, 2004 5:00 pm

Hi

I have a simple job that transfers data from the source to the target and uses a look up to a hash file. Now the source data has a space as a value in 8 of its columns. Not null but a space. The hash file has also been popluated from the source and also has a space in the same fields. However on looking up it retrieves all the rows except for which the space is there. Is there a way i can look up against the space? How can I retrieve these 8 rows?

Let me know
KV

chulett · Post by **chulett** » Mon Jul 26, 2004 5:11 pm

Are any of these 'space' fields keys in your hash file? I'm guessing that's the case if they are involved in the success or failure of your lookup.

Are you sure you've trimmed or not trimmed or done whatever needs to be done to ensure that the 'space-ness' of the fields is not changed so the lookup can find a match? And don't forget - one space <> two spaces - are you sure they are all a single space?

All things being equal - a single space in your key field and a single space in the data field being used to fill the key - the lookup should work fine.

rasi · Post by **rasi** » Mon Jul 26, 2004 9:36 pm

We had the same problem with version 7 while handling the space in the key fields. We had modified the job which creates the hash file to check if it is space. If so we send some special character to the column and in the lookup job we had the transforms to check from the source if it has space then change to the special character and do the lookup. It worked for us.

Thanks
Rasi

mleroux · Post by **mleroux** » Tue Jul 27, 2004 12:03 am

If you're still battling, how about trying to use a char(32) instead of " " to do the lookup? Or seq(" ") and lookup on an ASCII code 32?