Nested cos() calculation in Oracle 10

Question

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 ?

Answer 1

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.