Coding Your Own Aggregate Functions

To analyze a set of rows and compute a result value, you can code your own aggregate function that works the same as a built-in aggregate like SUM:

• Define an ADT that defines these member functions:

  • ODCIAggregateInitialize

  • ODCIAggregateIterate

  • ODCIAggregateMerge

  • ODCIAggregateTerminate

• Code the member functions. In particular, ODCIAggregateIterate accumulates the result as it is invoked once for each row that is processed. Store any intermediate results using the attributes of the ADT.

• Create the aggregate function, and associate it with the ADT.

• Call the aggregate function from SQL queries, DML statements, or other places that you might use the built-in aggregates. You can include typical options such as DISTINCT and ALL in the invocation of the aggregate function.