Page 1 of 1
Range Lookup Strange Behavior
Posted: Tue Jul 21, 2009 4:45 pm
by Sreedhar
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
Posted: Tue Jul 21, 2009 4:53 pm
by ray.wurlod
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")?
Posted: Tue Jul 21, 2009 5:16 pm
by Sreedhar
Yes it's a Range lookup on the Stream Input. Both the Stream Input data type and lookup file data type are Varchar.
Posted: Tue Jul 21, 2009 7:36 pm
by ray.wurlod
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.
Posted: Wed Jul 22, 2009 12:00 am
by Sreedhar
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
Posted: Wed Jul 22, 2009 12:05 am
by Sreedhar
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
Posted: Wed Jul 22, 2009 12:33 am
by ray.wurlod
Give some thought to how "00120", "0120", "01999" and "0200" sort as strings - you DID say that the data type is VarChar.
Posted: Wed Jul 22, 2009 10:05 am
by Sreedhar
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,
Posted: Wed Jul 22, 2009 10:15 am
by chulett
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
Posted: Wed Jul 22, 2009 12:19 pm
by Sreedhar
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.
Posted: Wed Jul 22, 2009 12:38 pm
by chulett
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.