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 im****tant 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
|
