Hash file lookup fails - double quotes

gomez · Post by **gomez** » Thu Apr 19, 2007 5:30 pm

Hi
I have created a hash file with 6 columns as keys, 5 of them are of Varchar and 1 SmallInt.

When I try to do the lookup using the hashfile, the lookup doesnt return any records even though there is a match from the source.

I compared the data between the source sequential file and hash file. The data in them seems to match except that all fields in the hash file are enclosed by double quotes. I tried changing NLS to ISO-8859-1, doesnt seem to help

Any clue on how I can avoid double quotes in the hash file data?

Thanks

DSguru2B · Post by **DSguru2B** » Thu Apr 19, 2007 5:38 pm

How are you creating the hashed file? If from a flat file, make sure you have quote character as (") so that they dont get transfered to the hashed file.

gomez · Post by **gomez** » Thu Apr 19, 2007 5:44 pm

Thanks for the quick response!

I am creating the hash file from a SQL 2000 table. There are a couple of active stages inbetween - Row merger and transformer.

In the transformer, I use CRC32 function on the incoming record and load the keys and output of CRC32 function into the hash file.
CRC32 column is of type Varchar too. This is not enclosed in double quotes however !!!

Thanks again

chulett · Post by **chulett** » Thu Apr 19, 2007 5:57 pm

I'm curious how you 'compared the data between the source sequential file and hash file'. Are you saying that you actually see double-qoutes in the data fields when you click View Data in the hashed file stage? Or did you do something else to compare the two?

gomez · Post by **gomez** » Thu Apr 19, 2007 6:47 pm

Yes, I compared using View Data option and I could see double quotes enclosing the varchar data (key) fields

chulett · Post by **chulett** » Thu Apr 19, 2007 8:08 pm

I don't know of any mechanism - outside of something deliberate - that would quote strings like that. Usually it takes a miss-configured stage, like a Sequential stage, to do so. Meaning, data is written out with 'normal' quots around the character fields and then the file is read back in either with '000' specified as the quote character or if it reads the entire record as one long string. Then you'd see something like that where the quotes now are considered to be part of the data.

However, you've said that the process that actually creates the hashed file is from a 'SQL 2000' table. Can you describe the exact stages used?

gomez · Post by **gomez** » Thu Apr 19, 2007 8:15 pm

Thanks chulett!
I am using a Row merger stage in the job. Figured out just now that this is where the quote character was getting inserted.

Instead of the default quote character " in the Format tab of Row merger, I now gave 000. And the hash file does not get any double quotes in the data.

Thank you all for the help!!

chulett · Post by **chulett** » Thu Apr 19, 2007 8:18 pm

Interesting... haven't had a need to use it at all yet, so wasn't aware it could pull that little trick on a person. Good to know.