Oracle custom aggregate

aggregateoracle

Year    Value
2012     10
2013     20
2014      0

I need 10 * 20 * 0 for multiple rows.
If I use this function EXP (SUM (LN (Value))), I get an ORA-01428 error:

argument '0' is out of range.

I have seen How do I create a user-defined aggregate function? but that is for MySQL, not Oracle and my requirement is to include zeroes.

Best Answer

In Oracle you can have your own aggregation function. To write one, we need to implement an interface which looks like this:

create /*or replace*/ type mult_agg_type as object (
    totalAggValue    number,
    static function ODCIAggregateInitialize(sctx IN OUT mult_agg_type) return number,
    member function ODCIAggregateIterate(self IN OUT mult_agg_type, value IN number) return number,
    member function ODCIAggregateTerminate(self IN mult_agg_type, returnValue OUT number, flags IN number) return number,
    member function ODCIAggregateMerge(self IN OUT mult_agg_type, ctx2 IN mult_agg_type) return number
);

The above is just the type definition introducing the type with the required type interface amended by a field definition which stores the value aggregated so far.

This interface can be implemented as follows:

create /*or replace*/ type body mult_agg_type is

    static function ODCIAggregateInitialize(sctx IN OUT mult_agg_type) return number is
    begin
        sctx := mult_agg_type (null);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(self IN OUT mult_agg_type, value IN number) return number is
    begin
        if self.totalAggValue is null then
            self.totalAggValue := value;
        else
            self.totalAggValue := self.totalAggValue*value;
        end if;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self IN mult_agg_type, returnValue OUT number, flags IN number) return number is
    begin
        returnValue := self.totalAggValue;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self IN OUT mult_agg_type, ctx2 IN mult_agg_type) return number is
    begin
        self.totalAggValue := self.totalAggValue*ctx2.totalAggValue;
        return ODCIConst.Success;
    end;

end;

Now that the datatype is defined completely, we need to generate a stub function which can be used as an aggregating function in a select-statement:

create /*or replace*/ function agg_product(input number) RETURN number
    PARALLEL_ENABLE AGGREGATE using mult_agg_type;

I chose the name agg_product, but feel free to choose any name you prefer or like best. As an example of how this can be used in your case, try:

select agg_product(value) as prod from your_table_name;
Related Question