Hello,
I am using a lookup on a hash table with a transform. It is a straighforward 3 character country table. However, one of the fields in the source table is 2 characters 'CH ' and is failing to find the entry in the hash table even though it is there.
I have tried putting a trim around the key expression and converting the source and lookup fields to varchar from char but still no luck.
Does anyone have any bright ideas ?
BTW great forum. I have already used a few solutions from the great Arnd and Ray.
Lookup not functioning as expected
Moderators: chulett, rschirm, roy
Morning Clarcombe,
your 2-charactervalue-in-a-3-char-field is going to be padded with either a space or some other value. Hash files are always varchar, so the third option exists with the field having been trimmed when you created it. This, of course, depends upon how you created the hashed file. I recommend always to use a TRIM() when working with CHAR fields to make sure those pesky invisible spaces are gone. You could write a short DS job to read you lookup into a quoted, comma-separated file and then look at the file in the editor to see if there are spaces in there.
If you do a LEN(TRIM(<StringWithCH>)) on your lookup string and get some value other than 2 you have some other non-displayable 3rd character that needs to be removed.
your 2-charactervalue-in-a-3-char-field is going to be padded with either a space or some other value. Hash files are always varchar, so the third option exists with the field having been trimmed when you created it. This, of course, depends upon how you created the hashed file. I recommend always to use a TRIM() when working with CHAR fields to make sure those pesky invisible spaces are gone. You could write a short DS job to read you lookup into a quoted, comma-separated file and then look at the file in the editor to see if there are spaces in there.
If you do a LEN(TRIM(<StringWithCH>)) on your lookup string and get some value other than 2 you have some other non-displayable 3rd character that needs to be removed.
</a>I went through all of the code replacing char with varchar and putting trims around everything. However I was still getting the rejections.ArndW wrote: If you do a LEN(TRIM(<StringWithCH>)) on your lookup string and get some value other than 2 you have some other non-displayable 3rd character that needs to be removed.
The customer source text file using the lookup was created from a DB source as fixed width. I changed it to ; separated and re-extracted. The lookup then worked fine.
What I don't understand is why it works like this. Surely once the data has been read in from any source to a varchar field, it should be trimmable and match ok.