Filling up the results of a query

#21

Hans Mayr wrote:

Quote:

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

#22

Hello Peter,

Late answer better than no answer. Thanks. I definitely prefer your
"select level" to the rownum solution. And thanks for the full sql
example, I implemented it into my application and it works very
nicely. It is always nice to solve such things with SQL only. I also
used pipelined functions at another place, also a very smooth solution
and probably more easy to read.

Best,

Hans

#23

Hello Peter,

Late answer better than no answer. Thanks. I definitely prefer your
"select level" to the rownum solution. And thanks for the full sql
example, I implemented it into my application and it works very
nicely. It is always nice to solve such things with SQL only. I also
used pipelined functions at another place, also a very smooth solution
and probably more easy to read.

Best,

Hans

#24

Hello Peter,

Late answer better than no answer. Thanks. I definitely prefer your
"select level" to the rownum solution. And thanks for the full sql
example, I implemented it into my application and it works very
nicely. It is always nice to solve such things with SQL only. I also
used pipelined functions at another place, also a very smooth solution
and probably more easy to read.

Best,

Hans

#25

Hello Peter,

Late answer better than no answer. Thanks. I definitely prefer your
"select level" to the rownum solution. And thanks for the full sql
example, I implemented it into my application and it works very
nicely. It is always nice to solve such things with SQL only. I also
used pipelined functions at another place, also a very smooth solution
and probably more easy to read.

Best,

Hans

#26

On Jun 3, 4:26 am, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

Quote:

Hello Peter,

Late answer better than no answer. Thanks. I definitely prefer your
"select level" to the rownum solution. And thanks for the full sql
example, I implemented it into my application and it works very
nicely. It is always nice to solve such things with SQL only. I also
used pipelined functions at another place, also a very smooth solution
and probably more easy to read.

Best,

Hans

And thank you, Hans for the feedback to the group.
Ed

#27

On Jun 3, 4:26 am, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

Quote:

Hello Peter,

Late answer better than no answer. Thanks. I definitely prefer your
"select level" to the rownum solution. And thanks for the full sql
example, I implemented it into my application and it works very
nicely. It is always nice to solve such things with SQL only. I also
used pipelined functions at another place, also a very smooth solution
and probably more easy to read.

Best,

Hans

And thank you, Hans for the feedback to the group.
Ed

#28

On Jun 3, 4:26 am, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

Quote:

Hello Peter,

Late answer better than no answer. Thanks. I definitely prefer your
"select level" to the rownum solution. And thanks for the full sql
example, I implemented it into my application and it works very
nicely. It is always nice to solve such things with SQL only. I also
used pipelined functions at another place, also a very smooth solution
and probably more easy to read.

Best,

Hans

And thank you, Hans for the feedback to the group.
Ed

#29

On Jun 3, 4:26 am, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

Quote:

Hello Peter,

Late answer better than no answer. Thanks. I definitely prefer your
"select level" to the rownum solution. And thanks for the full sql
example, I implemented it into my application and it works very
nicely. It is always nice to solve such things with SQL only. I also
used pipelined functions at another place, also a very smooth solution
and probably more easy to read.

Best,

Hans

And thank you, Hans for the feedback to the group.
Ed