Re: Filling up the results of a query

On Apr 14, 12:41 pm, Hans Mayr <mayr1..._at_gmx.de> wrote:
> 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

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 Received on Mon Apr 14 2008 - 13:29:02 CDT