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 * [message #290795] 
Mon, 31 December 2007 16:03 

Michel Cadot
Messages: 63911 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


The factorial of a nonnegative 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 #290948 is a reply to message #290795] 
Wed, 02 January 2008 04:33 

rajavu1
Messages: 1574 Registered: May 2005 Location: Bangalore , India

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 :=newnum1;
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 #291118 is a reply to message #290795] 
Thu, 03 January 2008 01:27 

Michel Cadot
Messages: 63911 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account 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






Goto Forum:
Current Time: Sat Oct 22 15:29:38 CDT 2016
Total time taken to generate the page: 0.10112 seconds
