An interesting scenario of using a Hashed File
Posted: Thu Apr 26, 2007 8:27 am
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.
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.