Range lookup
Moderators: chulett, rschirm, roy
Range lookup
I am having difficulty working with the range lookup on Datastage 8.x. I have no issue when I do the range lookup with tax rate against from and to range value and get the tax code but throws me off when I introduce Country as a key in Input and reference link.
Input stream columns: seq_no, country, Tax Rate
Ex: 1 US 5.0
2 CA 15.0
3 US 50.0
4 CA 70.5
5 CA 99.5
Reference Link: county from_value to_value tax_code
US 0 20 I1
CA 0 50 C1
US 21 50 I2
CA 51 90 C2
US 51 100 I3
CA 91 100 C3
Output should be:
1 US I1
2 CA C1
3 US I2
4 CA C2
5 U2 C3
Any help is greatly appreciated.
Input stream columns: seq_no, country, Tax Rate
Ex: 1 US 5.0
2 CA 15.0
3 US 50.0
4 CA 70.5
5 CA 99.5
Reference Link: county from_value to_value tax_code
US 0 20 I1
CA 0 50 C1
US 21 50 I2
CA 51 90 C2
US 51 100 I3
CA 91 100 C3
Output should be:
1 US I1
2 CA C1
3 US I2
4 CA C2
5 U2 C3
Any help is greatly appreciated.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I'm guessing you mis-typed this as that doesn't look like much of a range.narsingrp wrote: 1. set the range on input stream
Input.TaxRate >= Refernce.TaxRate_From and Reference.TaxRate <= TaxrRate_To
You say you have put the range on the input stream which would suggest Input.TaxRate should be within a range but yet you only mention it once.
If it is actually on the reference stream, then on the assumption you just have a spelling mistake in your column names you have:
Refernce.TaxRate_From <= Input.TaxRate
and
Reference.TaxRate <= TaxrRate_To
what are you trying to apply the range to?
Which variation of Unix/OS are you using?
We had a similar problem where a range lookup with an additional reference key caused jobs to fail (error does look different). We were on a SunOS at the time and never obtained a resolution to it.
PS It's not the duplicate country that is causing the problem, how you have configured it is correct for the requirement, we have similar configs working fine (on our Linux machine)
We had a similar problem where a range lookup with an additional reference key caused jobs to fail (error does look different). We were on a SunOS at the time and never obtained a resolution to it.
PS It's not the duplicate country that is causing the problem, how you have configured it is correct for the requirement, we have similar configs working fine (on our Linux machine)
-
- Premium Member
- Posts: 17
- Joined: Mon Dec 13, 2004 5:01 am
- Location: Denmark
- Contact:
[quote="narsingrp"]
I was getting warning message as: Lookup_60,0: Ignoring duplicate entry; no further warnings will be issued for this table , Message ID : IIS-DSEE-TFKL-00055[/quote]
hi.
We are getting the exact same error i the project i am on.
We are running version 8.1 on unix and linux servers
We are about to create a simple job to IBM Support, showing the issue.
It looks like you cannot combine a range lookup with a 'equality' lookup. this is not mentioned in the documentation as an issue.
i am trying to find a windows environment to test the same simple job, will let you know if the same problem occurs there
regards
Peter Tilsted
I was getting warning message as: Lookup_60,0: Ignoring duplicate entry; no further warnings will be issued for this table , Message ID : IIS-DSEE-TFKL-00055[/quote]
hi.
We are getting the exact same error i the project i am on.
We are running version 8.1 on unix and linux servers
We are about to create a simple job to IBM Support, showing the issue.
It looks like you cannot combine a range lookup with a 'equality' lookup. this is not mentioned in the documentation as an issue.
i am trying to find a windows environment to test the same simple job, will let you know if the same problem occurs there
regards
Peter Tilsted
You can as we have done so on our current system but there does seem to be an issue... possibly with the compiler but sufficient people are having this problem so IBM need to take notedennisroser wrote: It looks like you cannot combine a range lookup with a 'equality' lookup. this is not mentioned in the documentation as an issue.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I wonder if it's the decimal data type. I recall that, when range lookup first became available, not all data types were supported. Would it be possible to represent, say, 100 * taxrate as an integer and see whether that works?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 17
- Joined: Mon Dec 13, 2004 5:01 am
- Location: Denmark
- Contact:
Range lookup
I have tested our little sample program on a Windows environment, exactly the same error
(and in our sample all columns for both the range and the equality lookup is integers)
we will create a support issue today
regards
Peter Tilsted
(and in our sample all columns for both the range and the equality lookup is integers)
we will create a support issue today
regards
Peter Tilsted
-
- Premium Member
- Posts: 17
- Joined: Mon Dec 13, 2004 5:01 am
- Location: Denmark
- Contact:
Range Lookup, i got it to work
Before shipping the complaint to IBM i gave it one more shot.
After thorough look at the documentation and trying everything i could think of, i got the range lookup to behave as i wanted.
in my case the solution was found in the lookup stage, constraints popup window, where there is an option to select 'Multible rows returned from' and selecting my reference link in the dropdown.
this is my simple testcase
Consider these two tables
Source
lookup
we do a combination of range and equality lookup on the stream input to the lookup stage:
Source.id = lookup.id and
source.value >= lookup.valuemin AND source.value <= lookup.valuemax
the output from the lookup was before the abovementioned change:
and these rows were rejected:
And in the job log we got this warning: LKP,0: Ignoring duplicate entry; no further warnings will be issued for this table
After the above mentioned change we get as expected this output from the lookup:
i hope that can help you to get yours solved as well
regards
Peter
After thorough look at the documentation and trying everything i could think of, i got the range lookup to behave as i wanted.
in my case the solution was found in the lookup stage, constraints popup window, where there is an option to select 'Multible rows returned from' and selecting my reference link in the dropdown.
this is my simple testcase
Consider these two tables
Source
Code: Select all
id Value
1 5
1 7
1 9
1 11
1 13
1 15
Code: Select all
id Valuemin Valuemax ValueDescription
1 5 9 00
1 10 14 1
Source.id = lookup.id and
source.value >= lookup.valuemin AND source.value <= lookup.valuemax
the output from the lookup was before the abovementioned change:
Code: Select all
id Valuemin Value Valuemax ValueDescription
1 5 5 9 00
1 5 7 9 1
1 5 9 9 2222
Code: Select all
id Value
1 11
1 13
1 14
After the above mentioned change we get as expected this output from the lookup:
Code: Select all
id Valuemin Value Valuemax ValueDescription
1 5 5 9 00
1 5 7 9 00
1 5 9 9 00
1 10 11 14 1
1 10 13 14 1
regards
Peter
Last edited by dennisroser on Tue Nov 24, 2009 8:53 am, edited 3 times in total.
![Idea :idea:](./images/smilies/icon_idea.gif)
Tip: you can use the 'code' tags to preserve white-space and make things line up. You'll need to make liberal use of the 'Preview' option as well as it doesn't always work like you think it will with a proportional font. And one can always edit one's own posts so it's not too late to go back and take a whack at that.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 17
- Joined: Mon Dec 13, 2004 5:01 am
- Location: Denmark
- Contact:
Re: Range Lookup, i got it to work
Ahh, if you read the full original post, you would have noted they had set the "Multiple Rows returned" option.dennisroser wrote:in my case the solution was found in the lookup stage, constraints popup window, where there is an option to select 'Multible rows returned from' and selecting my reference link in the dropdown.
Now if I had read your first post fully then I might have noted you had missed this... I saw the "exact same error" and was happy to assume it was the same issue as OP