An interesting scenario of using a Hashed File

[vnspn]

Hi,

Here, this is our scenario. We have a lookup logic to be done. We use a first Job just to load a Hashed file. And then in the second Job, we use this Hashed File through a UniVerse stage to do the lookup.

There are 2 reason for using UniVerse stage here - 1) To do a range lookup and 2) To get multi row result set from the reference link.

In the first Job, where I load the Hashed file, the source Oracle table has key 'X'. So, I load it to the Hashed file with key as 'X'. In the second Job, I access this Hashed File through a UniVerse stage. Here I need to do a range lookup. There is a date field 'Y' from the source link. We have 'Start_Date' and 'End_Date' in this Hashed file. So, we would need to fetch all the records that fall in this range ('Y' > 'Start_Date' and 'Y' < 'End_Date'). This is the only condition based on which I would need to fetch the records from the reference link. So because of this matching that I would need to do in the transformer, I would need to specify the Keys for the Hashed File (in UV stage) in this second Job as 'Start_Date' and 'End_Date' (and not 'X').

I know the concept that Hashed Files should be used with sames Keys for both writing to it and reading from it. But to meet out requirement, we write the the Hashed File with Key 'X' and do the lookup by giving Keys as 'Start_Date' and 'End_Date'. Please share your thoughts on whether is it okay to do this or is there a better way to achieve the logic.

Thanks.

[DSguru2B]

Thats fine. YOu can do it this way. If performance starts to bite, put idices on the date fields. If thats still not enough. Load your source and reference data into work tables and perform the logic at the database level.

[chulett]

Perfectly fine if done properly. While you're here, learn from other's woes to cut down on the frustrations this path could bring.

[vnspn]

Thank you DSguru2B and Craig.

I haven't had any problem (in terms of performance) so far on making use of this technique.