hash lookup in a range

acool · Post by **acool** » Wed Sep 15, 2004 10:22 am

Hi everybody,

I want to have a special hash lookup, in stead of matching the key, I will math the key within a range, that is, if the input file's key equals the key in the lookup hasfile, or if the input files' key - a key in the lookup file <3, then match.

How can I do it?

KeithM · Post by **KeithM** » Wed Sep 15, 2004 11:16 am

You can only use a hash file stage to do an equal lookup. To do a range lookup you would have to use a different stage like an ODBC stage or the UV stage. You will have to change the SQL to be user-defined and then replace the equal join generated with a greater than/less than join to fit your needs.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 15, 2004 4:36 pm

If you can "math the range" down to a constant for each possible range, then you can look up this constant as the key value in the hashed file.

If you can't, then you can't.

Contemplate how you might build the math, and therefore how you might populate the hashed file.

chucksmith · Post by **chucksmith** » Wed Sep 15, 2004 5:42 pm

You may have to resort to writing a looping hash lookup routine if you can, as Ray suggested, "math the range" not necessarily to a single value, but at least to a predictable list of values.

You are creating a potential I/O bottleneck, but what are ETL tools for?

Look at the sdk / Utility / UtilityHashLookup routine.