Hi All ...
I've been trying to fit this requirement into a hash file without success. I know I can solve this with a temporary table, but I'd rather do a hash: I will create a series of rows out of my Transform. These rows will contain a column named smallest_nbr which is a value achieved by subtracting two dates. I need to identify the row with the smallest value (and yes, I will have to deal with potential duplicates). Is sorting this file the only way to deal with this issue?
thanks ...
... tom
How to get the smallest number
Moderators: chulett, rschirm, roy
Re: How to get the smallest number
Use the aggregator stage. Pretty easy to use.
tomengers wrote:Hi All ...
I've been trying to fit this requirement into a hash file without success. I know I can solve this with a temporary table, but I'd rather do a hash: I will create a series of rows out of my Transform. These rows will contain a column named smallest_nbr which is a value achieved by subtracting two dates. I need to identify the row with the smallest value (and yes, I will have to deal with potential duplicates). Is sorting this file the only way to deal with this issue?
thanks ...
... tom
You could also do this with a hash if that's the way you want to go.
Create one with a fixed key so it only has one record - like a "1" - and a data field to hold your number. Setup your job to do the lookup from and also write to this hash file. On each row, compute the number, do the lookup and compare the two. If your new number is smaller than your old number, write it to the hash. When you are done... smallest number.
![Wink :wink:](./images/smilies/icon_wink.gif)
Create one with a fixed key so it only has one record - like a "1" - and a data field to hold your number. Setup your job to do the lookup from and also write to this hash file. On each row, compute the number, do the lookup and compare the two. If your new number is smaller than your old number, write it to the hash. When you are done... smallest number.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Provided you disable write cache, and set the read cache to "disabled, lock for updates", Craig's solution will work.
The hashed file stage is not suited to what you are trying to do. Because of the way a hashed file works, calculating the actual (physical) location of the record based on the key value (zero I/O cost to find the key) rather than using an index or a table scan, the only kind of lookup you can do on a hashed file is the equivalent of WHERE key = ?
However, since a hashed file is how a UV table is implemented, you can use a UV stage to refer to the hashed file, and use regular SQL to extract the minimum value. SELECT MIN(columnname) FROM hashedfilename;
This will probably not be as fast as using an Aggregator stage.
The hashed file stage is not suited to what you are trying to do. Because of the way a hashed file works, calculating the actual (physical) location of the record based on the key value (zero I/O cost to find the key) rather than using an index or a table scan, the only kind of lookup you can do on a hashed file is the equivalent of WHERE key = ?
However, since a hashed file is how a UV table is implemented, you can use a UV stage to refer to the hashed file, and use regular SQL to extract the minimum value. SELECT MIN(columnname) FROM hashedfilename;
This will probably not be as fast as using an Aggregator stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.