Lookup data in hashed file using a routine?

sbass1 · Post by **sbass1** » Sun Mar 22, 2009 7:27 pm

Sorry if this has been discussed before. I did search on "hashed file basic" and "hashed file routine" before posting.

Is it possible to do a lookup against a hashed file in a routine?

What I want to do is:

SK = LookupDateSK(pInputDate,HashedFilePath)

where pInputDate is derived in a stage variable.

I'm thinking a combination of OPENSEQ, READSEQ, and CLOSESEQ???

If you've done this before or know where I can get an example, that would be great.

Hopefully the code would be performant (i.e. READSEQ would use the key of the hash for the lookup), as it would be called 3 times for every record in the source file.

Thanks,
Scott

DS_SUPPORT · Post by **DS_SUPPORT** » Sun Mar 22, 2009 8:32 pm

Take a look on the datastage supplied routine UtilityHashLookup, it will do the required things. I hope it works only for account based hashed files,
So you might need to create an entry to your hashed file in the VOC using SETFILE. In case you have more than one key for doing a lookup combine them like

Code: Select all

KEY1 : @TM : KEY2 : @TM : KEY3

chulett · Post by **chulett** » Sun Mar 22, 2009 9:42 pm

From memory - make a copy of the routine mentioned and then change the Open statement to OpenPath. No need for the VOC entry then, simply pass in the full path to the hashed file rather than just the name.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 23, 2009 12:24 am

It would never be the ...SEQ statements. These are for accessing text files (etc.) in a sequential fashion.

Read using the hashing algorithm uses READ statement. The hashed file needs to have been opened with OPEN or OPENPATH and closed (if required) with CLOSE. For better performance, though, open it only once by having the file variable in COMMON - this means that the file is not opened and closed on every call to the routine.

sbass1 · Post by **sbass1** » Mon Mar 23, 2009 4:50 am

chulett wrote:From memory - make a copy of the routine mentioned and then change the Open statement to OpenPath. No need for the VOC entry then, simply pass in the full path to the hashed file rather than just the name.

Thanks Craig, this worked great.