two contact aggregate function not same order

From: NOVA <nova1427_at_gmail.com>
Date: Sun, 24 May 2009 02:50:33 -0700 (PDT)
Message-ID: <953915a2-e9fa-4b31-8d65-a8882893526a_at_h28g2000yqd.googlegroups.com>

Good day to everyone.

I'm try to make aggregate function for contact the the rows as you see below.

create or replace type agg_t as object
(

    str_agg varchar2(4000),
    static function ODCIAggregateInitialize(sctx in out agg_t) return number,

    member function ODCIAggregateIterate(self in out agg_t, value in varchar2 ) return number,

    member function ODCIAggregateTerminate(self in agg_t, return_value out varchar2, flags in number) return number,

    member function ODCIAggregateMerge(self in out agg_t, ctx2 in agg_t) return number
);
/

create or replace type body agg_t is

    static function ODCIAggregateInitialize(sctx in out agg_t) return number is

    begin

        sctx := agg_t(null);
        return ODCIConst.Success;

    end;

    member function ODCIAggregateIterate(self in out agg_t, value in varchar2) return number is

    begin

        if(str_agg is not null)
            then str_agg := str_agg || ',' || value;
            else str_agg := value;
        end if;
        return ODCIConst.Success;

    end;

    member function ODCIAggregateTerminate(self in agg_t, return_value out varchar2, flags in number) return number is

    begin

        return_value := str_agg;
        return ODCIConst.Success;

    end;

    member function ODCIAggregateMerge(self in out agg_t, ctx2 in agg_t) return number is

    begin

        str_agg := str_agg || ctx2.str_agg;
        return ODCIConst.Success;

    end;

end;
/

create or replace function agg_concat (input varchar2) return varchar2

    parallel_enable aggregate using agg_t;
/

create table agg_test (
  a number,
  b varchar2(5)
);

insert into agg_test values (1,'a','aa');
insert into agg_test values (2,'A','AA');
insert into agg_test values (1,'b','bb');
insert into agg_test values (1,'c','cc');
insert into agg_test values (2,'B','BB');
insert into agg_test values (2,'C','CC');
insert into agg_test values (1,'d','dd');
insert into agg_test values (2,'D','DD');

select a, agg_concat(b) bb, agg_concat(c) cc from AGG_TEST
group by a

the problem is: coloumn bb and cc not same order, it give me this result:

A	BB	CC
1	"a,b,d,c"	"aa,bb,dd,cc"
2	"A,B,D,C"	"AA,CC,BB,DD"

it should by like this

A	BB	CC
1	"a,b,d,c"	"aa,bb,dd,cc"
2	"A,B,D,C"	"AA,BB,DD,CC"

And I want to understand what is the benefit of "ODCIAggregateMerge".

anyone can help please. Received on Sun May 24 2009 - 04:50:33 CDT