Hash Lookups and Transformers

[jpr196]

Hi all,

Maybe someone can help answer this question. Is it possible to return data from a Hash File where the keys are equal except the date where it is less than or equal to the date of the source? Or is my only solution to use bind variables and use user defined sql?

Thanks

[DSguru2B]

Is this date field part of the key? If no, then you can certainly do it by setting a constraint in a stage variable.
Something like

Code: Select all

IF NOT(REF.NOTFOUND) AND ref.DATE <= src.DATE then @TRUE else @FALSE

[ray.wurlod]

Hi all,

Maybe someone can help answer this question. Is it possible to return data from a Hash File where the keys are equal except the date where it is less than or equal to the date of the source? Or is my only solution to use bind variables and use user defined sql?

Thanks

Not using a Hashed File stage.

Hashed File stage does not access the hashed file using SQL; instead it applies an algorithm to the exact key value. From that function it obtains the address of the record in question, and can retrieve it directly. This is very fast.

You can use SQL to access the contents of a hashed file if you use a UniVerse stage. Without any indexes on the hashed file, this promises to be very slow, particularly for other than "=" as your lookup condition.

[jpr196]

Thanks for the input. The Date is part of the key so I'm guessing my best solution would be to use a drs stage directly for the lookup and compare the dates with user defined sql.

[ray.wurlod]

It will still be slow unless the column(s) named in the WHERE clause are indexed.

[WoMaWil]

If you have only one or a few dates it may be wise to put the one date into a non-key-field (or the few dates as array into a non-keyfield) and make the compare in the next step.