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.
Lookup in a Range
Moderators: chulett, rschirm, roy
Alternative
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: