Hello,

I could not find an answer for my following problem through Google,
maybe because I did not find the best keywords. So I hope that one of
you can help me.

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.

I could solve this through normal sql if I created an auxiliary table
t_numbers with a single column containing all the integer numbers I
needed and doing an outer join from t_numbers on t_rates. But I do not
like the idea of having such a dumb table t_numbers.

What is the best approach for my problem? Some form of "select from
dual"? Or PL / SQL?

Thanks.

Hans

On Apr 14, 12:41 pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:
Use an inline view like this:
select A from (select rownum A from all_objects ) tblA
where A <20 ;

I don't normally like rownum, but this seems like a decent application
of it.
Now you need to develop the cases to handle the subranges (0-3months,
3-6months, etc)
Ed

On Apr 14, 12:41 pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:
Use an inline view like this:
select A from (select rownum A from all_objects ) tblA
where A <20 ;

I don't normally like rownum, but this seems like a decent application
of it.
Now you need to develop the cases to handle the subranges (0-3months,
3-6months, etc)
Ed

On Apr 14, 12:41 pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:
Use an inline view like this:
select A from (select rownum A from all_objects ) tblA
where A <20 ;

I don't normally like rownum, but this seems like a decent application
of it.
Now you need to develop the cases to handle the subranges (0-3months,
3-6months, etc)
Ed

On Apr 14, 12:41 pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:
Use an inline view like this:
select A from (select rownum A from all_objects ) tblA
where A <20 ;

I don't normally like rownum, but this seems like a decent application
of it.
Now you need to develop the cases to handle the subranges (0-3months,
3-6months, etc)
Ed

Ed Prochak (edprochak (AT) gmail (DOT) com) wrote:
: On Apr 14, 12:41 pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

: > I need to create a view that "fills up" the entries of a table.


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


: Use an inline view like this:
: select A from (select rownum A from all_objects ) tblA
: where A <20 ;


Why an inner query, why not use all_objects directly?

select my,calculations,including,rownum from all_objects ;


ALSO, I don't know how many rows are needed, be sure to count all_objects
in the target database because there are not always as many as you need
for long periods (yep, I have had that happen, though only on a
development system).

Another technique could be to use a pipelined function (never done that
myself).

Ed Prochak (edprochak (AT) gmail (DOT) com) wrote:
: On Apr 14, 12:41 pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

: > I need to create a view that "fills up" the entries of a table.


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


: Use an inline view like this:
: select A from (select rownum A from all_objects ) tblA
: where A <20 ;


Why an inner query, why not use all_objects directly?

select my,calculations,including,rownum from all_objects ;


ALSO, I don't know how many rows are needed, be sure to count all_objects
in the target database because there are not always as many as you need
for long periods (yep, I have had that happen, though only on a
development system).

Another technique could be to use a pipelined function (never done that
myself).

Ed Prochak (edprochak (AT) gmail (DOT) com) wrote:
: On Apr 14, 12:41 pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

: > I need to create a view that "fills up" the entries of a table.


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


: Use an inline view like this:
: select A from (select rownum A from all_objects ) tblA
: where A <20 ;


Why an inner query, why not use all_objects directly?

select my,calculations,including,rownum from all_objects ;


ALSO, I don't know how many rows are needed, be sure to count all_objects
in the target database because there are not always as many as you need
for long periods (yep, I have had that happen, though only on a
development system).

Another technique could be to use a pipelined function (never done that
myself).

Ed Prochak (edprochak (AT) gmail (DOT) com) wrote:
: On Apr 14, 12:41 pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

: > I need to create a view that "fills up" the entries of a table.


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


: Use an inline view like this:
: select A from (select rownum A from all_objects ) tblA
: where A <20 ;


Why an inner query, why not use all_objects directly?

select my,calculations,including,rownum from all_objects ;


ALSO, I don't know how many rows are needed, be sure to count all_objects
in the target database because there are not always as many as you need
for long periods (yep, I have had that happen, though only on a
development system).

Another technique could be to use a pipelined function (never done that
myself).

On Apr 14, 8:04 pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
limit myself with using just SQL, but instead opting for PL/SQL to
solve this kind of problem.

Ed