## Forum Stats

• 3,815,622 Users
• 2,259,062 Discussions

Discussions

# factorial of a number

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

• 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 user-defined 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 user-defined funciton in PL/SQL might be appropriate.
Starting in in Oracle 9, you can write user-defined 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:
hey I gave a shout out to the model No need to iterate and no need for CASE:
```SELECT  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.