Re: [Q]: Weird SQL

From: Bill Altmann <billa_at_iaccess.com.au>
Date: 20 Mar 1995 12:05:08 GMT
Message-ID: <3kjr1k$kur_at_sleipnir.iaccess.com.au>


In article <3jouol$svr_at_news.starnet.net>, Neil Greene <neil_at_bMD.com> says:
>
>I have the following tables:
>
>SOURCE_TABLE
> position varchar(2),
> percentage number(7,2)
>
> values:
> 1, 102
> 1, 110
> 1, 90
>
>DEST_TABLE
> postion varchar(2),
> percentage number(7,2),
> dollar_amt number(7,2),
>
> values:
> 1, 100, 1000
> 1, 101, 1200
> 1, 102, 1400
> 1, 103, 1500
> 1, 104, 2000
>
>I want to take the value of percentage from the SOURCE table and
>find the dollar_amt from the DEST_TABLE with the closest
>percentage, taking the minimum value.
>
>102 should return $1,400 since it is an exact match.
>90 should return $1,000 since 90 is not in the table and 100 is
> the next lowest value.
>
>Neil Greene
>benchMark Developments, Inc.
>

Assuming ORACLE has BETWEEN. Add another column to DEST_TABLE percentage0 with the same attributes as percentage. Use percentage and percentage0 to specify a range of the lowest and highest percentages. So your first row would have percentage0 set 0.00. You might need another row after your last row. then the select statement is
Select dollar_amt where Source_table.percentage between dest_table.PErcentage0 and percentage. There is redundant data but the code is simple. Received on Mon Mar 20 1995 - 13:05:08 CET

Original text of this message