How to get the smallest number

[tomengers]

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

[kiran_kom]

Use the aggregator stage. Pretty easy to use.

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

[chulett]

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.

[ray.wurlod]

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.

[tomengers]

Kiran - Craig - Ray ...

Many thanks for your input. Damn, now I've got 3 choices ... :D

... tom