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 Go to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
BlackSwan
Messages: 22844
Registered: January 2009
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 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
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 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
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 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

2 More variants Smile

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>


Thumbs Up
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 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
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

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 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Nice One Michel .

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

Thumbs Up
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 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
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>



Thumbs Up
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 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



Straight SQL method in Oracle 9i Smile

Thumbs Up
Rajuvan.
Previous Topic: how to connect two databases which are in network
Next Topic: pl/sql library code
Goto Forum:
  


Current Time: Wed Oct 01 13:20:59 CDT 2014

Total time taken to generate the page: 0.11459 seconds