Re: factorial
Date: 15 Jan 2002 17:55:52 -0800
Message-ID: <bdf69bdf.0201151755.34c609fb_at_posting.google.com>
I'm not sure I understand section #3 in your message. Don't you want to create a factorial table with _two_ columns? Didn't you also forget to "group by"?
Table of integers is so important that I use table function for it. It has 2 advantages: it is not materialized, but still can be pipelined into any query, and it doesn't have artificial boundary. Overall, the solution is:
CREATE TYPE IntSet AS TABLE OF Integer;
/
CREATE or replace FUNCTION GEN_INT_SET
RETURN IntSet PIPELINED IS
i INTEGER;
BEGIN
i := 1;
loop
PIPE ROW(i);
i:=i+1;
end loop;
END;
/
select power(10,sum(log(10,column_value))) from TABLE(GEN_INT_SET) where column_value <= 5;
Note that the loop inside table function never breaks. Naive programmer would add a parameter to the function that tells to "generate all integer values less than n", but I prefer to have it as a predicate of the query, and think of TABLE(GEN_INT_SET) as infinite integers set. Now in order for the query to work, it must stop pipelined function somehow from generating new rows when it doesn't need any more integers. Therefore, pushing the predicate "column_value<=5" into the table function is a problem very similar to pushing predicate into a view definition in recursive approach.
In short, you are correct, here we deal with iteration rather than
recursion:
"God created Integers, the rest is work of men"
Kronecker
"Codd created Databases, but forgot about integers"
Unknown Author
71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0201151149.61acc632_at_posting.google.com>...
> Another thought and two handy tricks:
>
> 1) One of the standard auxillary tables yuou build when you get a new
> data base is a simple sequence of Integers. It is handy for a lot of
> queries
>
> CREATE TABLE Sequence (seq INTEGER NOT NULL PRIMARY KEY);
> INSERT INTO Sequence VALUES (1), (2),... (n);
>
> 2) Here is a version of the aggregate product function in SQL. You
> will need to have the logorithm and exponential functions. They are
> not standards, but they are very common.
>
> The idea is that there are three special cases - all positive numbers,
> one or more zeroes, and some negative numbers in the set. You can
> find out what your situation is with a quick test on the sign() of the
> minimum value in the set.
>
> Within the case where you have negative numbers, there are two
> sub-cases: (1) an even number of negatives or (2) an odd number of
> negatives. You then need to apply some High School algebra to
> determine the sign of the final result.
>
> SELECT CASE MIN (SIGN(nbr))
> WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers
> WHEN 0 THEN 0.00 -- some zeroes
> WHEN -1 -- some negative numbers
> THEN (EXP(SUM(LN(ABS(nbr))))
> * (CASE WHEN
> MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1
> THEN -1.00 ELSE 1.00 END)
> ELSE NULL END AS big_pi
> FROM NumberTable;
>
> 3) we put the two tricks together, withthe assumption that we have
> only positive numbers.
>
> CREATE TABLE Factorials (fact INTEGER NOT NULL PRIMARY KEY);
>
> INSERT INTO Factorials
> SELECT seq FROM Sequence;
>
> INSERT INTO Factorials
> SELECT CAST (EXP(SUM(LN(seq))AS INTEGER) -- all positive numbers
> FROM Sequence AS S1, Factorial AS F1
> WHERE S1.seq <= F1.fact;
>
> This is not recursive, nor is it procedural -- it is a set oriented
> solution.
Received on Wed Jan 16 2002 - 02:55:52 CET