Re: Re[2]: Instead of SUM() I require MULTIPLY

From: Yongping Yao <yaoyongping_at_gmail.com>
Date: Tue, 13 Dec 2005 14:04:00 +0800
Message-ID: <706468990512122204q3709148ewb02f700880176b88@mail.gmail.com>

Hi all,
  I try the suggestions from Lex de Haan and get the following codes. It works perfect.
  Thanks Haan!

CREATE OR REPLACE TYPE concatstrbycomma AS OBJECT (

   RESULT VARCHAR2
(4000), --The Result

   BEFORE VARCHAR2
(4000), --Temp String

   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT concatstrbycomma)

      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateiterate (SELF IN OUT concatstrbycomma, VALUE IN VARCHAR)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateterminate (

      SELF        IN OUT   concatstrbycomma,
      returnval   OUT      VARCHAR,
      flags       IN       NUMBER
   )
      RETURN NUMBER,

   MEMBER FUNCTION odciaggregatemerge (SELF IN OUT concatstrbycomma, ctx2 IN concatstrbycomma)

      RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY concatstrbycomma IS

   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT concatstrbycomma)

      RETURN NUMBER
   IS
   BEGIN

      sctx := concatstrbycomma ('', '');
      RETURN odciconst.success;

   END;
   MEMBER FUNCTION odciaggregateiterate (SELF IN OUT concatstrbycomma, VALUE IN VARCHAR)
      RETURN NUMBER
   IS
   BEGIN

      IF NVL (LENGTH (SELF.RESULT), 0) + LENGTH (VALUE) <= 4000 THEN
         IF NVL (LENGTH (SELF.RESULT), 0) > 0 THEN
            SELF.RESULT := SELF.RESULT || ',' || VALUE;
         ELSE
            SELF.RESULT := VALUE;
         END IF;
      ELSE
         SELF.RESULT := 'Strings Too Long.';
      END IF;
      RETURN odciconst.success;

   END;
   MEMBER FUNCTION odciaggregateterminate (

      SELF        IN OUT   concatstrbycomma,
      returnval   OUT      VARCHAR,
      flags       IN       NUMBER
   )
      RETURN NUMBER

   IS
   BEGIN

      returnval := SELF.RESULT;
      RETURN odciconst.success;

   END;
   MEMBER FUNCTION odciaggregatemerge (SELF IN OUT concatstrbycomma, ctx2 IN concatstrbycomma)

      RETURN NUMBER
   IS
   BEGIN
      IF NVL (LENGTH (SELF.RESULT), 0) + NVL (LENGTH (ctx2.RESULT), 0) <= 4000 THEN

         IF NVL (LENGTH (SELF.RESULT), 0) > 0 THEN
            SELF.RESULT := SELF.RESULT || ',' || ctx2.RESULT;
         ELSE
            SELF.RESULT := ctx2.RESULT;
         END IF;
      ELSE
         SELF.RESULT := 'Strings Too Long.';
      END IF;
      RETURN odciconst.success;

   END;
END;
/

CREATE OR REPLACE FUNCTION concatbycomma (input VARCHAR)

   RETURN VARCHAR PARALLEL_ENABLE
   AGGREGATE USING concatstrbycomma;

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 13 2005 - 00:04:11 CST