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 
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 userdefined 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 #305450 is a reply to message #305353] 
Mon, 10 March 2008 16:22 

Barbara Boehmer
Messages: 8696 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>




Goto Forum:
Current Time: Wed Mar 29 14:08:50 CDT 2017
Total time taken to generate the page: 0.07484 seconds
