User Defined Aggregates

Until recently I had no use for User Defined Aggregates, that was until I was ridiculed by my MySQL counterparts over the fact that Informix does not have a group_concat aggregate function.

This will not stand, ya know, this aggression will not stand, man.

A quick search of the internets produced this Stack Overflow post which shows how to create a User Defined Aggregate in Informix that will duplicate the MySQL group_concat aggregate.


Oh, if you’re wondering what group_concat does, it concatenates the string fields of a table like so:

id                 value
1                  ABC
2                  DEF
1                  GHI
1                  JKL
2                  MNO
3                  PQR

select
   id,
   group_concat(value)
from
   tab
group by
   id;

1     ABC, GHI, JKL
2     DEF, MNO
3     PQR

Creating a User Defined Aggreate involves creating 4 support functions in SPL, C or Java.

  • Initialize – Initializes the aggregate
  • Iteration – Merges results of previous iterations of the aggregate with the current value
  • Combine – Combines results of 2 iterations when the a…

Auteur : noreply@blogger.com (Andrew Ford)

No comments yet.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.