Range Lookup Strange Behavior

Sreedhar · Post by **Sreedhar** » Tue Jul 21, 2009 4:45 pm

I have the following data in the Lookup file.

Code varChar 5
Name varChar 20

the Data in the look up file is as .

00120 Sreedhar
0120 Molgara

Data some from the input is

0120 when the lookup is done I have getting Sreedhar as output. Instead of Molgara

Can some one help me what is that I am doing wrong.

Thanks

SM

ray.wurlod · Post by **ray.wurlod** » Tue Jul 21, 2009 4:53 pm

What are the data types of the key matching columns from the main input (I am assuming this is a "range lookup on stream input")?

Sreedhar · Post by **Sreedhar** » Tue Jul 21, 2009 5:16 pm

Yes it's a Range lookup on the Stream Input. Both the Stream Input data type and lookup file data type are Varchar.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 21, 2009 7:36 pm

Can you identify both boundary values for the range? For example if the lower bound is 0120 but the upper bound is 999999 then it is possible that 00120 might be the first or last key found and that then the row that is returned from the reference input.

Sreedhar · Post by **Sreedhar** » Wed Jul 22, 2009 12:00 am

ray.wurlod wrote:Can you identify both boundary values for the range? For example if the lower bound is 0120 but the upper bound is 999999 then it is possible that 00120 might be the first or last key found and that ...

The Range is as follow

0120 - 0200 Molgara
00120- 01999 Sreedhar

Regards,
SM

Sreedhar · Post by **Sreedhar** » Wed Jul 22, 2009 12:05 am

Sreedhar wrote:
ray.wurlod wrote:Can you identify both boundary values for the range? For example if the lower bound is 0120 but the upper bound is 999999 then it is possible that 00120 might be the first or last key found and that ...
The Range is as follow

Defination :

Code_from Char 5
Code_to Char 5
Name Varchar 20

the Data in the look up file is as .

0120 - 0200 Molgara
00120- 01999 Sreedhar

Data some from the input is

0120 (varchar 10 I am doing a Trim on this) when the lookup is done I am getting Sreedhar as output. Instead of Molgara

Can some one help me what is that I am doing wrong.

Regards,
SM

ray.wurlod · Post by **ray.wurlod** » Wed Jul 22, 2009 12:33 am

Give some thought to how "00120", "0120", "01999" and "0200" sort as strings - you DID say that the data type is VarChar.

Sreedhar · Post by **Sreedhar** » Wed Jul 22, 2009 10:05 am

I don't know that is the a root cause. Bez I have some other values I am getting the right output.

namely

0120 - 0200 Molgara
0250-0260 NewInput
00120- 01999 Sreedhar

When I send the input as 0252 I get NewInput, but the problem come only with 0120 or 0121 where insted of Molgara I am getting Sreedhar. don't know what's going wrong.

Regards,

chulett · Post by **chulett** » Wed Jul 22, 2009 10:15 am

Put your six string values in a simple text file in the order shown and then 'sort' it - the result should help illustrate your issue. For example:

0120
0200
0250
0260
00120
01999

Sorted:

00120
0120
01999
0200
0250
0260

Sreedhar · Post by **Sreedhar** » Wed Jul 22, 2009 12:19 pm

I am sorry If I have confused you.

The lookup file is like this.
Code_from Code_to Name
0120 0200 Molgara
0250 0260 NewInput
00120 01999 Sreedhar

I didn't sort while loading into the Lookup file set.

Should I be sorting it before I load into the Lookup file set?

as said above based on input
"When I send the input as 0252 I get NewInput, but the problem come only with 0120 or 0121 where insted of Molgara I am getting Sreedhar. don't know what's going wrong.
"

Let me know if more info is needed.

chulett · Post by **chulett** » Wed Jul 22, 2009 12:38 pm

Sorry, but I'm not the one that is confused here.

I wasn't suggesting that you sort or needed to sort anything, *I* sorted your range values to illustrate your problem and to reinforce Ray's comment of "Give some thought to how...". These are string values so the "ranges" are not quite a straight-forward as you seem to think they should be. The sorted output I posted shows how your ranges overlap.