DSXchange

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

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

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.

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.

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.