Home » SQL & PL/SQL » SQL & PL/SQL » User Defined Aggregate with Multiple Inputs?
User Defined Aggregate with Multiple Inputs? [message #305353] Mon, 10 March 2008 09:03 Go to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Hi,

We have many scenarios where we need to round values, which introduces rounding errors on number values.

I have written a user-defined aggregate which I can call to get the rounding_error amount for a window of data. However, I dont know how to have 2 inputs on the aggregate function so I can set the rounding precision to give the rounded error amount on.

Example below:-

create or replace type rounding_difference as object
(
  unrounded_total NUMBER, -- total of all numbers
  rounded_total NUMBER, -- total of all iterations
  static function ODCIAggregateInitialize(sctx IN OUT rounding_difference)
    return number,
  member function ODCIAggregateIterate(self IN OUT rounding_difference,
    value IN number) return number,
  member function ODCIAggregateTerminate(self IN rounding_difference,
    returnValue OUT number, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT rounding_difference,
   ctx2 IN rounding_difference) return number
);
/

CREATE OR REPLACE Type Body rounding_difference Is
  Static Function odciaggregateinitialize(sctx In Out rounding_difference)
    Return Number Is
  Begin
    sctx := rounding_difference(0, 0);
    Return odciconst.success;
  End;

  Member Function odciaggregateiterate
  (
    Self  In Out rounding_difference,
    Value In Number
  ) Return Number Is
  Begin
    Self.unrounded_total := Self.unrounded_total + Value;
    Self.rounded_total := Self.rounded_total + round(value,5); --HARDCODED ROUNDING
    Return odciconst.success;
  End;

  Member Function odciaggregateterminate
  (
    Self        In rounding_difference,
    returnvalue Out Number,
    flags       In Number
  ) Return Number Is
  Begin
    returnvalue := Self.unrounded_total - Self.rounded_total;
    Return odciconst.success;
  End;

  Member Function odciaggregatemerge
  (
    Self In Out rounding_difference,
    ctx2 In rounding_difference
  ) Return Number Is
  Begin
    Return odciconst.success;
  End;
End;
/


Suppose I have 3 numbers, but want to total after rounding to remain the same. I have hardcoded round,5 inthe user defined aggregate.

Then I can call:-

with data as
(
select 0.999999 as val from dual
union all
select 0.000001 from dual
union all
select 0.000001 from dual
)
select a.*,
       sum(round(val,5)) over (partition by val order by val) as rounded_sum,
       sum(val) over (partition by null) as total_sum,
       rounding_diff(val) over (partition by null) as myround
from data a;


I get the right answer, but if I want to change the precision, I need to change the code in the user defined aggregate.

Does anyone know of a way I can accept 2 parameters to the user defined aggregate? One which is the value im working out the rounding offset from, and another which is the rounding precision, eg:-

with data as
(
select 0.999999 as val from dual
union all
select 0.000001 from dual
union all
select 0.000001 from dual
)
select a.*,
       sum(round(val,5)) over (partition by val order by val) as rounded_sum,
       sum(val) over (partition by null) as total_sum,
       rounding_diff(val,5) over (partition by null) as myround -- pass rounding precision into function
from data a;
Re: User Defined Aggregate with Multiple Inputs? [message #305361 is a reply to message #305353] Mon, 10 March 2008 09:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
check this out.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:250238800346872505

Regards

Raj
Re: User Defined Aggregate with Multiple Inputs? [message #305374 is a reply to message #305353] Mon, 10 March 2008 09:48 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Ok thanks. So its an inherent restriction.

wonder how they code lag and lead then, both of which have 2 input parameters
Re: User Defined Aggregate with Multiple Inputs? [message #305376 is a reply to message #305374] Mon, 10 March 2008 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
wonder how they code lag and lead then, both of which have 2 input parameters

In C.
They are internal not PL/SQL.

Regards
Michel

[Updated on: Mon, 10 March 2008 09:57]

Report message to a moderator

Re: User Defined Aggregate with Multiple Inputs? [message #305450 is a reply to message #305353] Mon, 10 March 2008 16:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The following modification uses an object type as an input parameter in order to allow both the value and precision to be specified as input.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE round_expr AS OBJECT
  2    (re_value      NUMBER,
  3  	re_precision  NUMBER);
  4  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> create or replace type rounding_difference as object
  2  (
  3    unrounded_total NUMBER, -- total of all numbers
  4    rounded_total NUMBER, -- total of all iterations
  5    static function ODCIAggregateInitialize(sctx IN OUT rounding_difference)
  6  	 return number,
  7    member function ODCIAggregateIterate
  8  	 (self	IN OUT rounding_difference,
  9  	  value IN     round_expr)
 10  	 return number,
 11    member function ODCIAggregateTerminate(self IN rounding_difference,
 12  	 returnValue OUT number, flags IN number) return number,
 13    member function ODCIAggregateMerge(self IN OUT rounding_difference,
 14  	ctx2 IN rounding_difference) return number
 15  );
 16  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE Type Body rounding_difference Is
  2    Static Function odciaggregateinitialize(sctx In Out rounding_difference)
  3  	 Return Number Is
  4    Begin
  5  	 sctx := rounding_difference(0, 0);
  6  	 Return odciconst.success;
  7    End;
  8  
  9    member function ODCIAggregateIterate
 10  	 (self	IN OUT rounding_difference,
 11  	  value IN     round_expr)
 12  	 return number
 13    Is
 14    Begin
 15  	 Self.unrounded_total := Self.unrounded_total + Value.re_value;
 16  	 Self.rounded_total := Self.rounded_total + round(value.re_value, value.re_precision);
 17  	 Return odciconst.success;
 18    End;
 19  
 20    Member Function odciaggregateterminate
 21    (
 22  	 Self	     In rounding_difference,
 23  	 returnvalue Out Number,
 24  	 flags	     In Number
 25    ) Return Number Is
 26    Begin
 27  	 returnvalue := Self.unrounded_total - Self.rounded_total;
 28  	 Return odciconst.success;
 29    End;
 30  
 31    Member Function odciaggregatemerge
 32    (
 33  	 Self In Out rounding_difference,
 34  	 ctx2 In rounding_difference
 35    ) Return Number Is
 36    Begin
 37  	 Return odciconst.success;
 38    End;
 39  End;
 40  /

Type body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION rounding_diff
  2    (p_expr IN round_expr)
  3    RETURN NUMBER
  4    PARALLEL_ENABLE AGGREGATE USING rounding_difference;
  5  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> 
SCOTT@orcl_11g> with data as
  2  (
  3  select 0.999999 as val from dual
  4  union all
  5  select 0.000001 from dual
  6  union all
  7  select 0.000001 from dual
  8  )
  9  select a.*,
 10  	    sum(round(val,5)) over (partition by val order by val) as rounded_sum,
 11  	    sum(val) over (partition by null) as total_sum,
 12  	    rounding_diff(round_expr(val,5)) over (partition by null) as myround
 13  from data a
 14  /

       VAL ROUNDED_SUM  TOTAL_SUM    MYROUND
---------- ----------- ---------- ----------
   .000001           0   1.000001    .000001
   .000001           0   1.000001    .000001
   .999999           1   1.000001    .000001

SCOTT@orcl_11g> 


Re: User Defined Aggregate with Multiple Inputs? [message #305743 is a reply to message #305353] Tue, 11 March 2008 16:51 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Ok, thanks for the info Smile
Previous Topic: How to convert many row data to columns?
Next Topic: Multiple updates of same table
Goto Forum:
  


Current Time: Sat Dec 10 20:27:54 CST 2016

Total time taken to generate the page: 0.05660 seconds