x

Factorial function in oracle?

Do we have any function which calulates Factorial value.

Factorial definition: n* n-1 * n-2 * ...... * 1 For example: 5! = 5*4*3*2*1

I didn't find any function for this. I am planning to create a PL/SQL function for this.

Any ideas are welcome.

more ▼

asked Dec 17, 2009 at 09:08 PM in Default

OraLearner gravatar image

OraLearner
1.3k 78 86 88

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

SELECT ROWNUM, EXP(SUM(LN(ROWNUM)) OVER (ORDER BY ROWNUM)) FACTORIAL FROM USER_OBJECTS WHERE ROWNUM < 10

more here

more ▼

answered Dec 17, 2009 at 09:35 PM

Tariq Rahiman gravatar image

Tariq Rahiman
665 2 2 4

(comments are locked)
10|1200 characters needed characters left

Don Burleson dives into factorials:

A Zillion Ways to Do Factorials

He mentions that the exp(sum(ln(rownum))) technique doesn't work in all cases.

Ah, I see he also mentions that his information is drawn from this book:

SQL Design Patterns by Vadim Tropashko

Probably the best way is just to create your own function. I found one at:

Club Oracle

create or replace function factorial_calc(value integer)
  return  integer
  as
  minus1 pls_integer;
  product pls_integer;
  begin
     dbms_output.enable(999999);
     minus1 := value - 1;
     if ( minus1 > 0 )
     then
        product := value*factorial_calc(minus1);
        return product;
     end if;
     return value;
end factorial_calc;

Caution! Not tested. They also show a way to do it in SQL.

more ▼

answered Dec 17, 2009 at 10:48 PM

KillerDBA gravatar image

KillerDBA
1.5k 8 9 10

This question reads like a college level course homework assignment :)
Dec 17, 2009 at 11:37 PM HillbillyToad
You know, I think you're right. It's probably because of the factorials. College programming (I go back before "Computer Science" degrees) homework always involved some unrealistic problem that involved some sort of math that we've never even seen since college... like factorials!
Dec 18, 2009 at 02:08 AM KillerDBA
Not exactly Kind of college work. But I needed to do this for my project requriement. I know that we can do this by definig PL/SQL procedure. But I thought of finding some better ways to do this. So I found few good ones here. Thanks to everyone.
Dec 18, 2009 at 05:10 PM OraLearner
(comments are locked)
10|1200 characters needed characters left

Check out this query. I found the query over internet.

select count(*) FACTORIAL from ( select level b from (select level n from dual connect by level <= &x ) YourTable connect by nocycle n != prior n )where b = &x

Replace &x with factorial value.

But this query seems to be very slow. Only to show another way of doing this.

more ▼

answered Dec 18, 2009 at 08:30 PM

BI DWH BALA gravatar image

BI DWH BALA
606 43 60 62

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x50

asked: Dec 17, 2009 at 09:08 PM

Seen: 4184 times

Last Updated: Dec 17, 2009 at 09:08 PM