1
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.

  • The answer has a 2nd part, where it deals with 0s. – ypercubeᵀᴹ Oct 9 '14 at 11:55
  • @ypercube: the proposed extension still does not work in Oracle. This question is in more than one respect not a duplicate at all. 1) Oracle allows user-defined aggregate-funcitons. 2) Your linked solution does not work in Oracle. To shed some light on the question why Oracle does not behave as expected, I posted a question on this site: dba.stackexchange.com/questions/78805/… – mruether Oct 9 '14 at 19:32
  • 1
    Yes, Oracle has a few quirks. The solution is valid, with only minor modifications. I agree thought that your solution, which creates a new aggregate function is good. (My link, corrected:) sqlfiddle.com/#!4/6f11a/29 – ypercubeᵀᴹ Oct 9 '14 at 20:02
2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.