Is there a way to do a Factorial in SQL? 1

[djbjr]

Hi I am working on an HR data warehouse that requires a factorial in one of the aggregates.

If you don't know, 7 factorial = 7 * 6 * 5 * 4 * 3 * 2 * 1
It also can be be written as 7!

I wrote a function to do this but I would rather use SQL if this is possible.

The function looks like :

FUNCTION fac (n POSITIVE) RETURN INTEGER IS -- returns n!
BEGIN
IF n = 1 THEN -- terminating condition
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- recursive call
END IF;
END fac;


Thanks in advance for any suggestions

 

[SantaMufasa]

DJB,

Why are you hesitant to use a user-defined function versus an Oracle built-in function? If you create the user-defined function with "CREATE OR REPLACE FUNCTION FAC...", it is compiled and runs just as quickly as a built-in function.

Dave
Sandy, Utah, USA @ 18:26 GMT, 11:26 Mountain Time

 

[djbjr]

I was just afraid it would be slower.....I am under a lot of pressure to reduce load times because we spent mucho $$$ on new hardware/software.

I will test times and see how it works out.

thanks,

dj

 

[SantaMufasa]

DJ,

If you are going to create your own user-defined, stored procedure, I believe this code will run much faster that your original code (since there is no function "re-invocation&quot and avoid possible recursive-execution limits for highter values:

create or replace function fac (x number) return number is
result number;
begin
result := x;
for i in reverse 2..x-1 loop
result := result *i;
end loop;
return result;
end;
/
select fac(7) from dual;
FAC(7)
----------
5040

Dave
Sandy, Utah, USA @ 19:18 GMT, 12:18 Mountain Time