Range lookup Problem

[reddy]

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

[ray.wurlod]

You have to lie in your DataStage job. You have to specify col1 and col2 as keys (search keys) for the purposes of the reference lookup. Only in this way will they be passed to the WHERE clause.

[reddy]

Hi,

Thanks for reply.

I couldn't understand your explanation.please elaborate it.

I made 2 keys for lookup file (UV) as low and high fileds.

I have one Source key filed.

How can do linking on Source key filed against 2 lookup key fileds.

Thansk
Reddy

[kduke]

Use the same column twice except rename it.

[reddy]

Use the same column twice except rename it.

Hi Kim,

Thanks for reply.I linked source filed with 2 lookup cols but i couldn't get any matchings.

Here i am attching UV stage user defined query:

SELECT narasalow2.col1, narasalow2.col2, narasalow2.col3 FROM "narasalow2" WHERE (narasalow2.col1 >= ? AND narasalow2.col2 <= ?);

First i created hash file with keys col1 and col2 and used that hash file name and metadata for UV table.

Please help me out.

Thanks
Reddy