Recursive SQL

One of the most common school exercice about recursion is the factorial. Guess what, I am going to do it in sql with hierarchies!

I use the following ln property :
x1*…*xn = exp(ln(x1)+..+ln(xn)))

Ok, here it is

SQL> select n, (select exp(sum(ln(level))) 
     from dual 
     connect by level<=n) "N!" from
     (select rownum n from dual connect by level<7);

   N   N!
---- ----
   1    1
   2    2
   3    6
   4   24
   5  120
   6  720

By Laurent Schneider

Oracle Certified Master

Leave a comment

Your email address will not be published.