Home » Other » General » Puzzle n°05 - All the possible ways to get the factorial of a number *
Puzzle n°05 - All the possible ways to get the factorial of a number * Mon, 31 December 2007 16:03
 Michel Cadot Messages: 65153Registered: March 2007 Location: Nanterre, France, http://... Karma: 0 Senior MemberAccount Moderator
The factorial of a non-negative integer n, denoted by n!, is the product of all positive integers less than or equal to n. For example, 5! = 1*2*3*4*5 = 120 (from Wikipedia).

The purpose of this puzzle is to list all the ways you see to get N! from N in SQL or PL/SQL.

Enjoy!

Regards
Michel

Added MC: if possible, give the minimum Oracle version your solution is available from.

[Updated on: Tue, 01 January 2008 01:10]

Report message to a moderator

Re: Puzzle n°05 - All the possible ways to get the factorial of a number [message #290798 is a reply to message #290795] Mon, 31 December 2007 18:31
 BlackSwan Messages: 25578Registered: January 2009 Location: SoCal Karma: 0 Senior Member
```create or replace function factorial(val integer)
return  integer
as
minus1 integer;
product integer;
begin
dbms_output.enable(10000);
minus1 := val - 1;
if ( minus1 > 0 )
then
--dbms_output.put_line('Entering with = '||val);
product := val*factorial(minus1);
--dbms_output.put_line('Exiting with = '||product);
return product;
end if;
return val;
end factorial;
```
Re: Puzzle n°05 - All the possible ways to get the factorial of a number [message #290803 is a reply to message #290798] Mon, 31 December 2007 20:29
 rleishman Messages: 3727Registered: October 2005 Location: Melbourne, Australia Karma: 0 Senior Member
```select round(exp(sum(ln(n))))
from (
select level AS n
from dual
connect by level <= &n
)```
Re: Puzzle n°05 - All the possible ways to get the factorial of a number * [message #290833 is a reply to message #290795] Tue, 01 January 2008 06:13
 varu123 Messages: 754Registered: October 2007 Karma: 0 Senior Member
```select round(exp(sum(ln(n))))
from (
select level AS n
from dual
connect by level <= &n
)```

The following error has occurred:

ORA-01436: CONNECT BY loop in user data
Re: Puzzle n°05 - All the possible ways to get the factorial of a number * [message #290948 is a reply to message #290795] Wed, 02 January 2008 04:33
 rajavu1 Messages: 1574Registered: May 2005 Location: Bangalore , India Karma: 0 Senior Member
2 More variants

First one

```SQL>  CREATE OR REPLACE FUNCTION FACTORIAL_1(factstr varchar2 )
2   RETURN NUMBER AS
3     new_str VARCHAR2(4000) :=  factstr||'*' ;
4     fact number := 1 ;
5   BEGIN
6
7      WHILE new_str IS NOT NULL
8      LOOP
9        fact := fact * TO_NUMBER(SUBSTR(new_str,1,INSTR(new_str,'*')-1));
10        new_str := substr( new_str,INSTR(new_str,'*')+1);
11      END LOOP;
12
13      RETURN fact;
14
15   END;
16  /

Function created.

SQL> select  FACTORIAL_1(LTRIM(max(sys_connect_by_path(level,'*')),'*'))  AS FAC
TOR
2  from dual
3  connect by level <= 5;

FACTOR
----------
120```

Here I am just wondering any way to automatically give exact Numeric resut for

``` SQL> select LTRIM( max(sys_connect_by_path(level,'*')),'*') AS n
2    from dual
3    connect by level <= 5;

N
------------------------------
1*2*3*4*5

SQL>```

Second One

```SQL> CREATE OR REPLACE FUNCTION FACTORIAL_2(num NUMBER )
2  RETURN NUMBER AS
3    fact number := 1 ;
4    newnum  number := num ;
5  BEGIN
6
7     WHILE newnum >= 1
8     LOOP
9       fact    := fact * newnum;
10       newnum  :=newnum-1;
11     END LOOP;
12
13     RETURN fact;
14
15  END;
16  /

Function created.

SQL>  select FACTORIAL_2(5) FACTOR from DUAL;

FACTOR
----------
120

SQL>```

Rajuvan.

[Updated on: Wed, 02 January 2008 04:39]

Report message to a moderator

Re: Puzzle n°05 - All the possible ways to get the factorial of a number * [message #291054 is a reply to message #290833] Wed, 02 January 2008 20:57
 rleishman Messages: 3727Registered: October 2005 Location: Melbourne, Australia Karma: 0 Senior Member
 varu123 wrote on Tue, 01 January 2008 23:13 ```select round(exp(sum(ln(n)))) from ( select level AS n from dual connect by level <= &n )``` The following error has occurred: ORA-01436: CONNECT BY loop in user data

Try it on 10g

Ross Leishman
Re: Puzzle n°05 - All the possible ways to get the factorial of a number * [message #291118 is a reply to message #290795] Thu, 03 January 2008 01:27
 Michel Cadot Messages: 65153Registered: March 2007 Location: Nanterre, France, http://... Karma: 0 Senior MemberAccount Moderator
To answer Rajuvan question, you can use a SQL*Plus trick (sys_connect_by_path, so >= 9i):
```SQL> define N=5
SQL> col prod new_value prod
SQL> select substr(max(sys_connect_by_path(rownum,'*')),2) prod
2  from dual
3  connect by level <= &N
4  /
PROD
----------------------------------------------------------------
1*2*3*4*5

1 row selected.

SQL> select ' &N! = &prod = '||&prod "Factorial" from dual
2  /
Factorial
---------------------
5! = 1*2*3*4*5 = 120

1 row selected.```

Here's another way using MODEL clause (>= 10g):
```SQL> select ' &N! = '||val "Factorial"
2  from (select 1 rn from dual )
3  model
4    dimension by (rn)
5    measures (1 val)
6    rules
7      iterate (&N)
8      ( val[1] = val[1] * (iteration_number+1) )
9  /
Factorial
----------------------------------------------
5! = 120

1 row selected.```

You can also create your own aggregate function PROD like the standard SUM one (>= 9i):
```Create or replace type prod_type as object (
prod number,

static function ODCIAggregateInitialize (sctx IN OUT prod_type) return number,

member function ODCIAggregateIterate (
self  IN OUT prod_type,
value IN     number)
return number,

member function ODCIAggregateTerminate (
self        IN  prod_type,
returnValue OUT number,
flags       IN  number)
return number,

member function ODCIAggregateMerge (
self IN OUT prod_type,
ctx2 IN     prod_type)
return number
);
/

Create or replace type body prod_type is

static function ODCIAggregateInitialize (sctx IN OUT prod_type)
return number
is
begin
sctx := prod_type (null);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate (
self  IN OUT prod_type,
value IN     number)
return number
is
begin
self.prod := nvl(self.prod,1) * value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate (
self        IN  prod_type,
returnValue OUT number,
flags       IN  number)
return number
is
begin
returnValue := prod;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge (
self IN OUT prod_type,
ctx2 IN prod_type)
return number
is
begin
if ctx2 is not null and ctx2.prod is not null and self.prod is not null
then
self.prod := nvl(self.prod,1) * nvl(ctx2.prod,1);
end if;
return ODCIConst.Success;
end;

end;
/

Create or replace function prod (input number)
return number
PARALLEL_ENABLE AGGREGATE USING prod_type;
/

SQL> Select ' &N! = '||prod(rownum) "Factorial"
2  from dual
3  connect by level <= &N
4  /
Factorial
----------------------------------------------
5! = 120

1 row selected.```

Regards
Michel

[Updated on: Thu, 03 January 2008 01:28]

Report message to a moderator

Re: Puzzle n°05 - All the possible ways to get the factorial of a number * [message #291148 is a reply to message #290795] Thu, 03 January 2008 03:08
 rajavu1 Messages: 1574Registered: May 2005 Location: Bangalore , India Karma: 0 Senior Member
Nice One Michel .

Anyway , there is no 'Straight and Exlusive' SQL query to get the factorial from Oracle 9i .

Rajuvan.
Re: Puzzle n°05 - All the possible ways to get the factorial of a number * [message #354291 is a reply to message #290795] Fri, 17 October 2008 07:36
 rajavu1 Messages: 1574Registered: May 2005 Location: Bangalore , India Karma: 0 Senior Member

Got to know One more variant for the same ( by accident )

```SQL> var x number
SQL> define x =5
SQL> select count(*) FACTORIAL from
2     (
3  select level  b
4   from
5   (select level n from dual connect by level <= &x ) YourTable
6   connect by nocycle n != prior n
7  )where b = &x
8  ;
old   5:  (select level n from dual connect by level <= &x ) YourTable
new   5:  (select level n from dual connect by level <= 5 ) YourTable
old   7: )where b = &x
new   7: )where b = 5

FACTORIAL
----------
120

SQL>```

Rajuvan.
Re: Puzzle n°05 - All the possible ways to get the factorial of a number * [message #354292 is a reply to message #290795] Fri, 17 October 2008 07:37
 rajavu1 Messages: 1574Registered: May 2005 Location: Bangalore , India Karma: 0 Senior Member

Straight SQL method in Oracle 9i

Rajuvan.
 Previous Topic: Puzzle n°04 - Evenly share batches of articles into groups *** Next Topic: Puzzle n°06 - Right-handed helix of Numbers **
Goto Forum:

Current Time: Tue Aug 22 10:06:24 CDT 2017

Total time taken to generate the page: 0.01155 seconds