-- On October 5, 2005, someone asked the following question on www.orafaq.com

-- Hi
-- 
-- Is there any function avaialble in SQL that can return the highest 
-- common factor among a bunch of numbers. For example
-- 10,20,25 have a highest common factor of 5. Thanks

-- I was tempted to create my own example of a function, just to see if I could do it, 
-- but it was the end of my work day, and I went home and forgot about it. 
-- The next day, I found Art Metzer's answer to the question:

CREATE OR REPLACE FUNCTION find_gcd (
      p_n1    IN  POSITIVE
  ,   p_n2    IN  POSITIVE
  )
  RETURN POSITIVE
  IS
      l_n1    POSITIVE := p_n1;
      l_n2    POSITIVE := p_n2;
  BEGIN
      WHILE NOT (l_n1 = l_n2)
      LOOP
          CASE SIGN(l_n1 - l_n2)
          WHEN +1
          THEN l_n1 := l_n1 - l_n2;
          ELSE l_n2 := l_n2 - l_n1;
          END CASE;
      END LOOP;
      RETURN (l_n1);
  END find_gcd;
  /
 
SELECT find_gcd(find_gcd(10,15),25) gcd
FROM   DUAL;
       GCD
----------
         5
		 
-- Nice work! Which caused me to ask:
-- Is it possible to create an aggregate function to do the same thing?
-- To which Mr. Metzer cordially replied:

-- Yes, it can be done, using Oracle's user-defined aggregate functions capabilities.

-- Here's an example of a custom GCD aggregate function. Note, it's not production safe; 
-- anyone implementing this solution would have to determine how to handle negatives, zeroes 
-- and non-integers in their particular situation.

CREATE OR REPLACE TYPE gcd_agg AS OBJECT (
   factor   NUMBER,
   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT gcd_agg)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateiterate (SELF IN OUT gcd_agg, VALUE IN NUMBER)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       gcd_agg,
      returnvalue   OUT      NUMBER,
      flags         IN       NUMBER
   )
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregatemerge (SELF IN OUT gcd_agg, ctx2 IN gcd_agg)
      RETURN NUMBER
);
/


CREATE OR REPLACE TYPE BODY gcd_agg
AS
   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT gcd_agg)
      RETURN NUMBER
   IS
   BEGIN
      sctx := gcd_agg (TO_NUMBER (NULL));
      RETURN (odciconst.success);
   END odciaggregateinitialize;
   MEMBER FUNCTION odciaggregateiterate (SELF IN OUT gcd_agg, VALUE IN NUMBER)
      RETURN NUMBER
   IS
      l_value          POSITIVE;
      l_return_value   NUMBER;
   BEGIN
      BEGIN
         l_value := ABS (TRUNC (VALUE));

         IF (SELF.factor IS NULL OR SELF.factor <= 0)
         THEN
            SELF.factor := l_value;
         ELSE
            WHILE NOT (SELF.factor = l_value)
            LOOP
               CASE SIGN (SELF.factor - l_value)
                  WHEN +1
                  THEN
                     SELF.factor := SELF.factor - l_value;
                  ELSE
                     l_value := l_value - SELF.factor;
               END CASE;
            END LOOP;
         END IF;

         l_return_value := odciconst.success;
      EXCEPTION
         WHEN VALUE_ERROR
         THEN
            l_return_value := odciconst.error;
      END;

      RETURN (l_return_value);
   END odciaggregateiterate;
   MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       gcd_agg,
      returnvalue   OUT      NUMBER,
      flags         IN       NUMBER
   )
      RETURN NUMBER
   IS
   BEGIN
      returnvalue := SELF.factor;
      RETURN (odciconst.success);
   END odciaggregateterminate;
   MEMBER FUNCTION odciaggregatemerge (SELF IN OUT gcd_agg, ctx2 IN gcd_agg)
      RETURN NUMBER
   IS
      l_value_1   POSITIVE := SELF.factor;
      l_value_2   POSITIVE := ctx2.factor;
   BEGIN
      IF (l_value_1 IS NULL OR l_value_1 <= 0)
      THEN
         l_value_1 := l_value_2;
      ELSE
         WHILE NOT (l_value_1 = l_value_2)
         LOOP
            CASE SIGN (l_value_1 - l_value_2)
               WHEN +1
               THEN
                  l_value_1 := l_value_1 - l_value_2;
               ELSE
                  l_value_2 := l_value_2 - l_value_1;
            END CASE;
         END LOOP;
      END IF;

      RETURN (odciconst.success);
   END odciaggregatemerge;
END;
/


CREATE OR REPLACE FUNCTION gcd (input NUMBER)
   RETURN NUMBER PARALLEL_ENABLE
   AGGREGATE USING gcd_agg;
/
 CREATE TABLE t (id VARCHAR2(2), n INT);
 INSERT INTO t VALUES ('A',25);
 INSERT INTO t VALUES ('A',30);
 INSERT INTO t VALUES ('A',55);
 INSERT INTO t VALUES ('B',77);
 INSERT INTO t VALUES ('C',7176);
 INSERT INTO t VALUES ('C',5428);
 INSERT INTO t VALUES ('C',7820);
 
SELECT id, gcd(t.n) FROM t GROUP BY id;
ID   GCD(T.N)
-- ----------
A           5
B          77
C          92
3 rows selected


-- Wow! I don't know what is more amazing... Oracle's flexibility to create a
-- user defined aggregate, or the completeness of Mr. Metzer's answer!
-- Thank you Art Metzer!