Prev: Increase your Oracle security knowledge
Next: Problem with Unicode Strings
From: Hans Mayr on 14 Apr 2008 12:41
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
From: Ed Prochak on 14 Apr 2008 14:29
On Apr 14, 12:41 pm, Hans Mayr <mayr1...(a)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
From: Malcolm Dew-Jones on 14 Apr 2008 20:04
Ed Prochak (edprochak(a)gmail.com) wrote:
: On Apr 14, 12:41 pm, Hans Mayr <mayr1...(a)gmx.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).


From: Ed Prochak on 15 Apr 2008 08:21
On Apr 14, 8:04 pm, yf...(a)vtn1.victoria.tc.ca (Malcolm Dew-Jones)
wrote:
> Ed Prochak (edproc...(a)gmail.com) wrote:
>
> : On Apr 14, 12:41 pm, Hans Mayr <mayr1...(a)gmx.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 ;
>
You're right. Obviously I don't use this technique often. I would not
limit myself with using just SQL, but instead opting for PL/SQL to
solve this kind of problem.

> 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
From: Hans Mayr on 16 Apr 2008 10:26
Hello Ed, Hello Malcom,

Thanks for both possibilities that you pointed out (using rownum or
pipelined functions). Both will solve my problem and are interesting
concepts.

Best,

Hans
 |  Next  |  Last
Pages: 1 2
Prev: Increase your Oracle security knowledge
Next: Problem with Unicode Strings