# Factorial function in oracle?

 0 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 1.3k ● 82 ● 88 ● 92 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 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 665 ● 2 ● 4 ● 7 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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 1.5k ● 9 ● 11 ● 14 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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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 606 ● 54 ● 62 ● 66 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

By Email:

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

Topics:

x441
x60

asked: Dec 17, 2009 at 09:08 PM

Seen: 5452 times

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