Re: Filling up the results of a query

From: Peter Nilsson <airia_at_acay.com.au>
Date: Mon, 21 Apr 2008 20:48:35 -0700 (PDT)
Message-ID: <f1f33e40-dadf-46b3-beee-b680073dcacd@y22g2000prd.googlegroups.com>

Hans Mayr wrote:
> I need to create a view that "fills up" the entries of a table.
> Example: Let's consider a table t_rates with bank rates for borrowing
> money for a certain duration:
>
> Duration_Months; Rate
> 3; 4.0%
> 6; 4.5%
> 12; 4.8%
>
> The problem is that I need a value for all durations, not only the
> ones I have. So I would like to create a view that calculates (e.g.
> interpolates, extrapolates) the rates. Result:
>
> Duration_Months; Rate
> 1; 4.0%
> 2; 4.0%
> 3; 4.0%
> 4; 4.2%
> 5; 4.4%
> 6; 4.5%
> ...
>
> Whatever the rates will be, that is not important for me right now.
> What I need is the sequence of all durations.

Not pretty, but...

with
  durn_rate as
  (

    select 3  m, 4.0 r from dual union all
    select 6  m, 4.5 r from dual union all
    select 12 m, 4.8 r from dual

  )
  , piv as
  (
    select level n from dual
    connect by level <= 18
  )
  , durn_rate_12 as
  (
    select

      p.n,
      nvl(max(case when dr.m <= p.n then dr.m end), min(dr.m)) m1,
      nvl(min(case when dr.m >= p.n then dr.m end), max(dr.m)) m2

    from piv p, durn_rate dr
    group by p.n
  )
select
  n,
  trunc(dr1.r + nvl( (dr2.r - dr1.r) * (dr.n - dr1.m) /

                         nullif(dr2.m - dr1.m, 0)       , 0), 1) r

from

  durn_rate_12 dr,
  durn_rate dr1,
  durn_rate dr2

where 1=1
  and dr1.m = dr.m1
  and dr2.m = dr.m2
order by 1

--
Peter

Received on Mon Apr 21 2008 - 22:48:35 CDT