Conditional Lookup to Hash File

[achhabra]

I need to fetch from a Hash file based on an expression other than the equi condition. I am trying to fetch based on DATE >Start_Date AND DATE <=End_Date, where Start_Date & End_Date are key columns from the HASH file and DATE is an input variable.

I there an easy way to do this? I am trying to skip writing a routine for this.

Any help will be appreciated.

-Ashish

[kris007]

You will need to create a hashed file in a seperate job and load all the data you want in it. Then in your Job you will need to use a UVStage as a lookup instead of hashed file, load the columns and write the SQL as neccessary where the table name in the sql will be the name of the hashed file created. You could also use ODBC stage in place of UVStage but I have not used ODBC for range lookups earlier.

[achhabra]

My problem is that the DATE variable is comming from a previous transformer and I don't see a way to pass it to my user-defined SQL for the lookup.

Any suggestions?

-Ashish

[kris007]

That's not what I meant. You need to load your start_date and end_date into a hashed file in a seperate job. Now, in your current Job pass your input date into the transformer stage through the primary input link. Now, use a UV(Universe) stage instead of hashed file for lookup. In the UV stage give the name of the Data source as "localuv". Now, load the metadata i.e. your start date and end date in the columns. Select the "user-defined sql query" in the General tab. Now go to SQL query tab. You will see a sql for primary inputs and reference inputs. Now put your sql something like this

Code: Select all

SELECT START_DATE, END_DATE FROM YourHashedFileName  WHERE START_DATE <=? AND END_DATE >=?;

Now, in the Transformer Stage map your input date column to both these date columns(set them as keys) and you are all set to get your desired ouput.

HTH

Also, set the datatypes in the UVStage as varchar for every column.

Come back if you have any questions.

[samit_9999]

Hi Kris,

I had a similar case ... and was looking at your suggestions.

However the job is not able to read the hash file and gives the following error

SQLSTATE=S0002, DBMS.CODE=950390
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: Table "hshfilename" does not exist.

[chulett]

That's because the UV stage requires an Account based, not a Pathed file. So you'll need to create a VOC record for it so the stage can find the hashed file you've named. Search for SETFILE to see examples of the syntax.