Data Science & Machine Learning

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.

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 :)

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.