I would like to find a technique that would give the equivalent of doing a LOOKUP(field GE)in a table request:

Here is the basic scenario, why I need this

I have a table with the 50 States, for each state
we want to "lookup" mortgage rates and points and maybe other fields based on the State and the lending price desired.

Here is the typical scenario, (I could not display the table here because I can't paste it in here and have it look decent, so I am describing it in words instead)-
If the state is Arizoa and the selling price is from .01 to 10,000 then the Mortgage rate is .1 and the points are 1.

If the state is Arizona and the selling prices from 10,000.01 to 20,000 then Mortgage rate is .15
and the points are 2.
etc. for Arizona

If the state is Colorado and the Lending Amount is from 8,000 to 26,000 then the mortgage rate is .2 and the points are 2.5
If the state is Colorado and the Lending Amount is from 26,000.01 to 40,000 then the mortgage rate is .3 and the points are 1.9.
and so on for Colorado and other states.


The lending amount ranges are haphazard and follow no particular pattern, nor do the rates or points.
If I were using Modify I could create a table and do a LOOKUP(field GE) and get the desired Rate and points, where the field would be the concatenation of the State and the particular lending amount.

But I am not using Modify, I have to use Table.

I could use a bunch of If then Else statements but the number would so large it would be prohibitive. I also have to do this "Lookup" in a bunch of lines of code and don't want to repeat the exhaustive if then else logic over and over again.

Does anyone have any technique that would help to alleviate this problem? I thought of decode from a file, but I believe that only works if you are looking for an exact match.

Thank You.

Use a one-to-many join on State, with a WHERE clause in the TABLE request to yield a unique match.

JOIN state in file1 to ALL state in file2
...
where file1.amount ge file2.lower_bound
and file1.amount le file2.upper_bound;
...

(Note that, although you know that the condition ensures a unique match, Focus regards this as a nonunique join. That can result in "multiple path" messages.)

Thanks Jack, worked like a charm.