character range lookup in lookupstage

spuli · Post by **spuli** » Mon Dec 28, 2015 9:01 pm

Hello everyone,

I have a question on range lookup in lookup stage. I have got a requirement to do a range lookup for one column (like value between '291' and '29209'). I used range expression on the source link column. It is not giving the correct output.

Here is the scenario. Source value is '291K0' which is between '291' and '29209' as per character comparison. Is it allows only integer and date columns for range comparison?

Please advice. Thanks in advance.

ArndW · Post by **ArndW** » Tue Dec 29, 2015 7:25 am

Are the columns all defined as varchar() and without leading or trailing blanks? I can't check right now, but that between should work.

spuli · Post by **spuli** » Tue Dec 29, 2015 8:51 am

Yes. All the columns are varchar columns and the values are trimmed too. It is not working.

ArndW · Post by **ArndW** » Tue Dec 29, 2015 9:18 am

I just created a test job with exactly the values you specified for the range lookup and it works as it should. I used varchar() columns (not char()) and it works like a charm.

spuli · Post by **spuli** » Tue Dec 29, 2015 9:27 am

Thanks for your reply.
I am running it on Datastage 8.5.
May I know the datastage version that you have used to test?

Here is one more odd scenario : '21000' between '29100' and '29209'. Please let me know the result of this scenario.

Thanks for your time.

roy · Post by **roy** » Tue Dec 29, 2015 9:57 am

I had a similar issue with 8.7
where range lookup faild to work
I had a single column with ID + a ranged timestamp column
that didn't work as expected.
Due to short time to resolve I ended up checking the TimestampToDecimal() values in a transformer.
The down side is that is made multiple of the rows since so many were joined by the ID column, but it worked.

IHTH (I Hope This Helps),

ArndW · Post by **ArndW** » Tue Dec 29, 2015 10:13 am

I'm using Version 11. I don't think that this behavior has changed though and think that there is some other problem involved here. I made a simple test job that I think you should try as well to make a small example which, if it doesn't work, you can send to your service provider.

A row generator for your lookup value, with a single row and just that one value, '291K0'. Another row generator which make column "from" and "to" with values '291' and '29209' respectively. Then a lookup for the range and output to a PEEK stage. All datatypes are "varchar".

roy · Post by **roy** » Tue Dec 29, 2015 11:39 am

good to hear in V.11 it works
I also had to get the records not in the range lookup for another flow
so processed so many records twice
So There is the "wrok around" that I implemented

spuli · Post by **spuli** » Wed Dec 30, 2015 10:09 am

I am doing a mistake here. I was trying to do a lookup on one column and range look up on other column at the same time which is not supposed to be done so.

Thanks for your valuable responses and time.