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
Conditional Lookup to Hash File
Moderators: chulett, rschirm, roy
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.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
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 thisNow, 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.
Code: Select all
SELECT START_DATE, END_DATE FROM YourHashedFileName WHERE START_DATE <=? AND END_DATE >=?;
HTH
Also, set the datatypes in the UVStage as varchar for every column.
Come back if you have any questions.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Participant
- Posts: 20
- Joined: Thu Oct 06, 2005 12:23 pm