Re: custom aggregate function in oracle 10g

Jürg Schaufelberger wrote:
>
> Hello
> I need a aggregate function for concatenate a list of strings. I
> created a custom aggregate function, here only the body of the
> aggregate function:
>
> MEMBER FUNCTION ODCIAggregateIterate
> ( self IN OUT StringChain,
> val IN VARCHAR2
> ) RETURN NUMBER IS
> BEGIN
> DBMS_OUTPUT.PUT_LINE('Iterate ' || TO_CHAR(val));
> IF val IS NULL THEN
> /* Will never happen */
> DBMS_OUTPUT.PUT_LINE('Null on iterate');
> ELSE
> IF self.runningLength = 0 THEN
> self.runningString := val;
> self.runningLength := LENGTH(val);
> ELSE
> self.runningString := self.runningString || ',' || val;
> self.runningLength := self.runningLength + LENGTH(val) + 1;
> END IF;
> END IF;
> RETURN ODCIConst.Success;
> END;
>
> This function works, but if I want to have the items of the string
> chain in the same order as in the list, I realize it does not work.
> Here the example
>
> SQL> select v.fid_ls_liegenschaf, to_number(f.nummer) nummer
> from av_vs_plan_lieg v, av_pe_plan f
> where v.deleted = 0 and v.fid_pe_plan = f.fid and fid_ls_liegenschaf =
> 140
> order by 2
>
> FID_LS_LIEGENSCHAF NUMMER
> ------------------ ------
> 140 53
> 140 54
> 140 55
> 140 56
> 140 57
> 140 71
> 140 72
> 7 Zeilen ausgewählt.
>
> SQL> select fid_ls_liegenschaf, chainstring(h.nummer)
> from (
> select v.fid_ls_liegenschaf, to_number(f.nummer) nummer
> from av_vs_plan_lieg v, av_pe_plan f
> where v.deleted = 0 and v.fid_pe_plan = f.fid
> order by 2) h
> where fid_ls_liegenschaf = 140
> group by fid_ls_liegenschaf
>
> FID_LS_LIEGENSCHAF CHAINSTRING(H.NUMMER)
> ------------------ ---------------------
> 140 53,54,55,57,72,71,56
> 1 Zeilen ausgewählt.
>
> Have anybody an idea what's the raeson in this case? Thanks for any
> help
>
> Juerg

order is not guaranteed in the aggregate (in the same way that "group by" does not guarantee order in standard SQL).

Take a look at

http://forums.oracle.com/forums/thread.jspa?messageID=1229525&#1229525

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------