Re: Aggregation function and rows order

Adrian Billington wrote:
> Patrick,
>
> As you are grouping by ID, surely you will only have one aggregated
> string, so there will be nothing to order by ?
>
> If you actually want to order the aggregated string, then I've shown
> you how to do it below. As you didn't add your ordering rules, I've
> gone for alphabetical order. I've used a function instead of a type
> that breaks down the aggregated string and re-orders it. A function is
> used because types cannot have associative array attributes.

[snip very interesting code]

Ok, instead of relying on rows order to perform the aggregation, you aggregate out-of-order then re-order the string. I thought about that but I could not figure how to write it correctly. Actually, even if you did not really give the answer I was looking for, I do thank you for the code sample. I learnt many things from it (table objets, etc...), and the use of the index like in a radix sort is pretty clever.

As you said, this is probably expensive, but that was not my main concern. Another problem with this method is you suppose that you can aggregate then split the string based on the separating string only. I tried to avoid this condition because I cannot guarantee the aggregated strings do not contain the separator. For instance, it could be useful to aggregate quoted-strings with commas, and commas may appear between quotes (eg. in very simple CSV format). And I am not fond of PL/SQL coding, not talking about parsing stuff in PL/SQL... But I did not give any details, and your solution would work for my practical case.

Thank you

Patrick Mézard Received on Fri Nov 26 2004 - 12:44:50 CST