Range lookup Problem
Posted: Wed Dec 21, 2005 9:19 am
Good Morning DataStage Gurus,
I have a requirement like this:
Source file : 2 cols Number,Address
Lookup file: 3 cols Low Number,High Number,Value
I want to do lookup for Number in between Low number and High Number
like 450 in between 1 and 500 and get the value from lookup table.
What i did is :
I created hash file from lookup file with dummy field as key in one job and used that hash file as UV table with same metadata of hash file but changed keys to Low Number and High Number and used it as a lookup file in another job.
while doing lookup with source file i mapped Low Number with Number and High Number with Number also.
But i am not getting any matched records.
Here is my UV Userdefined SQL:
SELECT narasalow1.dummy, narasalow1.col1, narasalow1.col2, narasalow1.col3 FROM "narasalow1" WHERE (narasalow1.col1 >= ? AND narasalow1.col2 <= ?);
narasalow1 is hash file that i created with dummy filed as a key.
I mapped Sourece Number with Lookup narasalow1.col1 and narasalow1.col2
Please correct me what i did wrong.
Thanks for help.
Reddy
I have a requirement like this:
Source file : 2 cols Number,Address
Lookup file: 3 cols Low Number,High Number,Value
I want to do lookup for Number in between Low number and High Number
like 450 in between 1 and 500 and get the value from lookup table.
What i did is :
I created hash file from lookup file with dummy field as key in one job and used that hash file as UV table with same metadata of hash file but changed keys to Low Number and High Number and used it as a lookup file in another job.
while doing lookup with source file i mapped Low Number with Number and High Number with Number also.
But i am not getting any matched records.
Here is my UV Userdefined SQL:
SELECT narasalow1.dummy, narasalow1.col1, narasalow1.col2, narasalow1.col3 FROM "narasalow1" WHERE (narasalow1.col1 >= ? AND narasalow1.col2 <= ?);
narasalow1 is hash file that i created with dummy filed as a key.
I mapped Sourece Number with Lookup narasalow1.col1 and narasalow1.col2
Please correct me what i did wrong.
Thanks for help.
Reddy