I have table with some positive integer numbers
n
----
1
2
5
10
For each row of this table I want values cos(cos(...cos(0)..)) (cos is applied n times) to be calculated by means of SQL statement (PL/SQL stored procedures and functions are not allowed):
n coscos
--- --------
1 1
2 0.540302305868
5 0.793480358743
10 0.731404042423
I can do this in Oracle 11g by using recursive queries.
Is it possible to do the same in Oracle 10g ?
The MODEL clause can solve this:
Test data:
create table test1(n number unique);
insert into test1 select * from table(sys.odcinumberlist(1,2,5,10));
commit;
Query:
--The last row for each N has the final coscos value.
select n, coscos
from
(
--Set each value to the cos() of the previous value.
select * from
(
--Each value of N has N rows, with value rownumber from 1 to N.
select n, rownumber
from
(
--Maximum number of rows needed (the largest number in the table)
select level rownumber
from dual
connect by level <= (select max(n) from test1)
) max_rows
cross join test1
where max_rows.rownumber <= test1.n
order by n, rownumber
) n_to_rows
model
partition by (n)
dimension by (rownumber)
measures (0 as coscos)
(
coscos[1] = cos(0),
coscos[rownumber > 1] = cos(coscos[cv(rownumber)-1])
)
)
where n = rownumber
order by n;
Results:
N COSCOS
1 1
2 0.54030230586814
5 0.793480358742566
10 0.73140404242251
Let the holy wars begin:
Is this query a good idea? I wouldn't run this query in production, but hopefully it is a useful demonstration that any problem can be solved with SQL.
I've seen literally thousands of hours wasted because people are afraid to use SQL. If you're heavily using a database it is foolish to not use SQL as your primary programming language. It's good to occasionally spend a few hours to test the limits of SQL. A few strange queries is a small price to pay to avoid the disastrous row-by-row processing mindset that infects many database programmers.