Lookup in a Range

[jo8712]

I'm trying to load a table with a particular acct_nbr and I need to find(lookup) the acct number in a table that has a range of numbers. The table actually has begin and end range columns and the acc_nbr I have will fall in a range.

I tried using an IF statement to do my join(lookup) in the transformer:

If ACCT.ACC >= LKUP.ACCT_BEGIN AND ACCT.ACC <= LKUP.ACCT_END Then ACCT.ACC Else 'NOT FOUND'

but it is not working.....any suggestions.

[1stpoint]

One alternative is to make a hash file of all the possible account numbers between the ranges and make it a key, e.g.

Account Number Range Returned Account
123-145 145

Key Returned Account
123 145
124 145
125 145
...

The other alternative would be to use the UV stage, which may be a tad slower on the lookup side depending on the volume of transactions.

[ray.wurlod]

This exact question has been answered a number of times on the forum.
Try a search for "BETWEEN JOIN" as a start point.