Range lookup

[narsingrp]

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.

[ray.wurlod]

Show us how - and where - you specified the range lookup.

[Kryt0n]

1. set the range on input stream
Input.TaxRate >= Refernce.TaxRate_From and Reference.TaxRate <= TaxrRate_To

I'm guessing you mis-typed this as that doesn't look like much of a range.

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?

[narsingrp]

I am sorry, my mistake .. Range condition is:
Input.TaxRate >= Reference.TaxRate_From AND
Input.TaxRate < = Reference.TaxRate_To

And Lookup key Condition: Refence.country_L = Input.country

[Kryt0n]

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)

[narsingrp]

Thanks for the info. We run of SuSE Linux, It appears I have to take this up with IBM.

[dennisroser]

[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

[Kryt0n]

It looks like you cannot combine a range lookup with a 'equality' lookup. this is not mentioned in the documentation as an issue.

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 note

[ray.wurlod]

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?

[dennisroser]

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

[dennisroser]

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

Code: Select all

id Value	
1    5	
1    7       
1    9       
1    11      
1    13    	
1    15

lookup

Code: Select all

id  Valuemin  Valuemax    ValueDescription
1        5        9               00
1       10        14               1

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:

Code: Select all

id  Valuemin  Value	Valuemax	ValueDescription
1         5      5         9                   00
1         5      7         9                    1
1         5      9         9                 2222

and these rows were rejected:

Code: Select all

id  Value	
1     11	
1     13	
1     14

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:

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

i hope that can help you to get yours solved as well

regards

Peter

[chulett]

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.

[dennisroser]

thanks for the tip, looks better now

[chulett]

I agree. Much more better now. :D

[Kryt0n]

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.

Ahh, if you read the full original post, you would have noted they had set the "Multiple Rows returned" option.

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