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.
Using WITH FUNCTION(Oracle 12c):
WITH FUNCTION coscos(n INT) RETURN NUMBER IS
BEGIN
IF n > 1
THEN RETURN cos(coscos(n-1));
ELSE RETURN cos(0);
END IF;
END;
SELECT n, coscos(n)
FROM t;
Output:
+-----+-------------------------------------------+
| N | COSCOS(N) |
+-----+-------------------------------------------+
| 1 | 1 |
| 2 | .5403023058681397174009366074429766037354 |
| 5 | .793480358742565591826054230990284002387 |
| 10 | .7314040424225098582924268769524825209688 |
+-----+-------------------------------------------+
