Re: Re[2]: Instead of SUM() I require MULTIPLY
- From: Yongping Yao <yaoyongping@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Tue, 13 Dec 2005 14:04:00 +0800
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;
Other related posts:
