Discussions
Categories
 196.7K All Categories
 2.2K Data
 235 Big Data Appliance
 1.9K Data Science
 449.8K Databases
 221.5K General Database Discussions
 3.8K Java and JavaScript in the Database
 31 Multilingual Engine
 549 MySQL Community Space
 477 NoSQL Database
 7.9K Oracle Database Express Edition (XE)
 3K ORDS, SODA & JSON in the Database
 532 SQLcl
 4K SQL Developer Data Modeler
 186.8K SQL & PL/SQL
 21.3K SQL Developer
 295.4K Development
 17 Developer Projects
 138 Programming Languages
 292.1K Development Tools
 104 DevOps
 3.1K QA/Testing
 645.9K Java
 28 Java Learning Subscription
 37K Database Connectivity
 153 Java Community Process
 105 Java 25
 22.1K Java APIs
 138.1K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 17 Java Essentials
 158 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 203 Java User Groups
 24 JavaScript  Nashorn
 Programs
 394 LiveLabs
 37 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.6K Other Languages
 2.3K Chinese
 170 Deutsche Oracle Community
 1.1K Español
 1.9K Japanese
 230 Portuguese
factorial of a number
Mac_Freak_Rahul
Member Posts: 427
Hi all,
I am trying to write a sql that can calculate the factorial from a single sql.with this query I can reach uptill :
with t as
(select 10 num from dual)
select ltrim(max(sys_connect_by_path(rownum,'*')),'*') factorial
from t
connect by rownum <= num
Output: 1*2*3*4*5*6*7*8*9*10
Now my requirements are as follows:
1) great if some1 could help me in writing an outer query on top of mine to get the desired result
2) suggest some other way to make me understand how to calculate factorial of a number from scratch.
Thanks
Rahul
I am trying to write a sql that can calculate the factorial from a single sql.with this query I can reach uptill :
with t as
(select 10 num from dual)
select ltrim(max(sys_connect_by_path(rownum,'*')),'*') factorial
from t
connect by rownum <= num
Output: 1*2*3*4*5*6*7*8*9*10
Now my requirements are as follows:
1) great if some1 could help me in writing an outer query on top of mine to get the desired result
2) suggest some other way to make me understand how to calculate factorial of a number from scratch.
Thanks
Rahul
Best Answer

Try this,
SQL> WITH T 2 AS (SELECT LEVEL num 3 FROM DUAL 4 CONNECT BY LEVEL < = 10) 5 SELECT num, EXP (SUM (LN (lvl))) factorial 6 FROM (SELECT LEVEL lvl 7 FROM (SELECT MAX (num) mx FROM T) 8 CONNECT BY LEVEL <= mx), 9 T 10 WHERE lvl<=num 11 GROUP BY num; NUM FACTORIAL   1 1 2 2 3 6 4 24 5 120 6 720 7 5040 8 40320 9 362880 10 3628800 10 rows selected. SQL>
G.
Answers

Hi, Rahul,
Starting in Oracle 11.2, you can use a recursive WITH clause (instead of CONNCT BY) to do that easily.
For earlier versions, see this thread:
4092659
n! is a very special case; it can be done without CONNECT BY, XML functions or PL/SQL, like this:SELECT ROUND (EXP (SUM (LN (LEVEL)))) AS f FROM dual CONNECT BY LEVEL <= 10  or any positive integer ;
Edited by: Frank Kulash on Apr 18, 2011 11:53 AM
Edited by: Frank Kulash on Apr 18, 2011 11:54 AM
Solomon posted the same ROUND(EXP (SUM( LN ... solution independently, whicle I was posting mine. 
select round(exp(sum(ln(level)))) factorial from dual connect by level <= 10 / FACTORIAL  3628800 SQL>
SY. 
Try this,
SQL> WITH T 2 AS (SELECT LEVEL num 3 FROM DUAL 4 CONNECT BY LEVEL < = 10) 5 SELECT num, EXP (SUM (LN (lvl))) factorial 6 FROM (SELECT LEVEL lvl 7 FROM (SELECT MAX (num) mx FROM T) 8 CONNECT BY LEVEL <= mx), 9 T 10 WHERE lvl<=num 11 GROUP BY num; NUM FACTORIAL   1 1 2 2 3 6 4 24 5 120 6 720 7 5040 8 40320 9 362880 10 3628800 10 rows selected. SQL>
G. 
Thanks Ganesh , SY ,Frank for providing me the answer
Regards
Rahul 
here is another way
/* Formatted on 10/22/2010 8:28:21 AM (QP5 v5.149.1003.31008) */ SELECT id num, running_total FROM DUAL MODEL RETURN UPDATED ROWS DIMENSION BY (0 d) MEASURES (CAST (NULL AS NUMBER) id, 1 running_total) RULES ITERATE (10) (id [ITERATION_NUMBER] = ITERATION_NUMBER + 1, running_total[ITERATION_NUMBER] = case when iteration_number > 1 then id[cv()] * running_total[cv()  1]else iteration_number+1 end )
NUM RUNNING_TOTAL 1 1 2 2 3 6 4 24 5 120 6 720 7 5040 8 40320 9 362880 10 3628800

Hi, Rahul,
Here's an Oracle 11.2 solution, using a recursive WITH clause:WITH f (n, n_factorial) AS ( SELECT 1 AS n , 1 AS n_factorial FROM dual  UNION ALL  SELECT n + 1 AS n , n_factorial * (n + 1) AS n_factorial FROM f WHERE n < 10 ) SELECT * FROM f ;
Output:` N N_FACTORIAL   1 1 2 2 3 6 4 24 5 120 6 720 7 5040 8 40320 9 362880 10 3628800
To summarize what (I believe) everyone is saying:
Multiplying a list of numbers (including consecutive integers, generated by CONNECT BY) can be done by the SUM function, together with EXP and LN.
This has been known since the time of the deluge. When Noah brought all the animals out of the ark, and told them to "be fruitful and multiply" (see Genesis 8.17), some of the snakes objected, saying that they were only adders, and couldn't multiply. Noah showed them how to cut tree limbs into pieces, stick four of the peices upright in the ground, and build a horizontal platform on top with the other pieces; then they were all set, because even adders can multiply if they have a log table.
However, multiplying is a special case. When you have a recursive relationship, where the value on one level is based on the value at a previous level, then you can use a recursive WITH clause (starting in Oracle 11.2) or XMLQUERY (in Oracle 10, and up), or write a userdefined function that will take a string such as '1*2*3*4' and return the result of that computation.
MODEL is another option that nobody has mentioned yet. It's probably more work in most cases. (Pollywog did mention it, while I was posting this.)
If the data is not really coming from a table (as is the case here), a userdefined funciton in PL/SQL might be appropriate.
Starting in in Oracle 9, you can write userdefined aggregate/analytic functions.
Edited by: Frank Kulash on Apr 18, 2011 12:32 PM 
hey I gave a shout out to the model

pollywog wrote:No need to iterate and no need for CASE:
hey I gave a shout out to the modelSELECT num, factorial FROM DUAL MODEL DIMENSION BY (1 num) MEASURES (1 factorial) RULES ( factorial[for num from 2 to 10 increment 1] = cv(num) * factorial[cv()  1] ) / NUM FACTORIAL   1 1 2 2 3 6 4 24 5 120 6 720 7 5040 8 40320 9 362880 10 3628800 10 rows selected. SQL>
SY. 
One more for the record:
SQL> select rownum  1 n, to_number(column_value) factorial from xmltable('declare function local:fac ($i) { if ($i = 0) then 1 else $i * local:fac($i  1) }; (::) for $i in 0 to 10 return local:fac($i)') / N FACTORIAL   0 1 1 1 2 2 3 6 4 24 5 120 6 720 7 5040 8 40320 9 362880 10 3628800 11 rows selected.
This discussion has been closed.