Re: [Q]: Weird SQL

In article <3kjr1k$kur_at_sleipnir.iaccess.com.au>, billa_at_iaccess.com.au (Bill Altmann) wrote:

>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.

come on, it isn't that hard. what's the key to DEST? i'll assume position & percentage:

  select s.position, s.percentage, d.percentage, d.dollar_amount     from source_table s, dest_table d
   where d.position = s.position and

         d.percentage =
          ( select min(percentage) from dest_table
             where position = s.position and
                   d.percentage >= s.percentage );

write me if this isn't what you want. Received on Wed Mar 22 1995 - 02:17:16 CET