May be user-defined aggregate functions are not among the most frequent hits in the life on a PL/SQL or APEX developer, but today I wanted to find an elegant solution for a problem submitted by a customer without reinventing the wheel:
given a report containing the labor expressed as hours and minutes in two separate column containing simple integers, how can I calculate the corresponding total?
The first thing that came off the top to my head was to convert this pair of values into a datatype that has been created exactly for this type of problems: interval day to second.
For instance, given two values representing 4 hours and 20 minutes, Oracle can represent this piece of information as a single value as follows:
select NUMTODSINTERVAL(4,'HOUR') + NUMTODSINTERVAL(20,'MINUTE') as hm from dual; HM ------------------- +00 04:20:00.000000
Of course the interesting property of Oracle's native time intervals lies in the fact that you can perform arithmetic operations with lesser effort than you would otherwise spend if you were doing it on your own (the famous wheel reinvented with square corners usually), so for instance, adding or subtracting two intervals is just a matter of performing '+' or '-' between two interval values.
In a perfect world, given these assumptions, it would mean that the built-in SUM aggregate function applied on interval values would return the total interval calculated on a bunch of rows, but in case you didn't notice up until now, we don't live in a perfect world, so how can we work around this?
It turns out that Oracle provides all the information necessary to create a user-defined aggregate function that with tons of imagination we are going to call SUMINTERVAL.
Below you can find the complete code for creating the function, which is very similar to the example given in the documentation and possibly more useful.
create or replace type SumIntervalImpl as object
(
total INTERVAL DAY TO SECOND, -- store total interval
static function ODCIAggregateInitialize(sctx IN OUT SumIntervalImpl)
return NUMBER,
member function ODCIAggregateIterate(self IN OUT SumIntervalImpl,
value IN INTERVAL DAY TO SECOND) return NUMBER,
member function ODCIAggregateTerminate(self IN SumIntervalImpl,
returnValue OUT INTERVAL DAY TO SECOND, flags IN NUMBER) return NUMBER,
member function ODCIAggregateMerge(self IN OUT SumIntervalImpl,
ctx2 IN SumIntervalImpl) return NUMBER
);
/
create or replace type body SumIntervalImpl is
static function ODCIAggregateInitialize(
sctx IN OUT SumIntervalImpl)
return NUMBER is
begin
sctx := SumIntervalImpl(NUMTODSINTERVAL(0,'SECOND'));
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(
self IN OUT SumIntervalImpl
, value IN INTERVAL DAY TO SECOND)
return NUMBER is
begin
self.total := self.total + value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
self IN SumIntervalImpl,
returnValue OUT INTERVAL DAY TO SECOND
, flags IN NUMBER)
return NUMBER is
begin
returnValue := self.total;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
self IN OUT SumIntervalImpl
, ctx2 IN SumIntervalImpl)
return NUMBER is
begin
self.total := self.total + ctx2.total;
return ODCIConst.Success;
end;
end;
/
create or replace function SumInterval (
input INTERVAL DAY TO SECOND)
RETURN INTERVAL DAY TO SECOND
PARALLEL_ENABLE AGGREGATE USING SumIntervalImpl;
-- let's run our aggregate function for the first time
select suminterval(hm) as total from (
select NUMTODSINTERVAL(4,'HOUR') + NUMTODSINTERVAL(20,'MINUTE') as hm
from dual
union
select NUMTODSINTERVAL(3,'HOUR') + NUMTODSINTERVAL(50,'MINUTE') as hm
from dual
);
TOTAL ----------- 0 8:10:0.0
Once I get the total labor in terms of days, hours and minute, I can easily pull out the single bits one at a time and do whatever I need to do. Indeed the function to accomplish this is called EXTRACT.
select extract(DAY from total) dt
, extract(HOUR from total) ht
, extract(MINUTE from total) mt
from (
select suminterval(hm) as total from (
select NUMTODSINTERVAL(14,'HOUR') + NUMTODSINTERVAL(20,'MINUTE') as hm
from dual
union
select NUMTODSINTERVAL(13,'HOUR') + NUMTODSINTERVAL(50,'MINUTE') as hm
from dual
)
);
DT HT MT
---------- ---------- ----------
1 4 10
Now with plain integers to work with, it becomes really trivial to perform any further calculations if you prefer to represent the total labor as pure minutes or hours.
Everything ok?
Almost.
Interval datatypes come with variable precision but by default a datatype declared as follows allows for 2 digits in the DAY field and 6 digits in the SECONDS field.
While the number of seconds in the context of our aggregate function hold little importance, the number of digits for the days can quickly become insufficient if we process enough values.
Theoretically one can increase the precision up to 9 digits, but interestingly enough it seems there is no way to compile the various objects with non-default precision indicators and it's a glorious mistery why Oracle decided to set 2 as the default instead of 9 (the maximum allowed value).
When you hit this problem, you'll get the following error message:
ORA-01873: the leading precision of the interval is too small
So, how do we remedy to this further annoyance?
The best solution I could come up with was to use varchar2 strings instead of intervals and perform the conversions inside the code, which led me to modify the code above as follows:
create or replace type SumIntervalImpl as object
(
total INTERVAL DAY(9) TO SECOND, -- store total interval
static function ODCIAggregateInitialize(sctx IN OUT SumIntervalImpl)
return NUMBER,
member function ODCIAggregateIterate(self IN OUT SumIntervalImpl,
value IN VARCHAR2) return NUMBER,
member function ODCIAggregateTerminate(self IN SumIntervalImpl,
returnValue OUT VARCHAR2, flags IN NUMBER) return NUMBER,
member function ODCIAggregateMerge(self IN OUT SumIntervalImpl,
ctx2 IN SumIntervalImpl) return NUMBER
);
/
create or replace type body SumIntervalImpl is
static function ODCIAggregateInitialize(
sctx IN OUT SumIntervalImpl)
return NUMBER is
begin
sctx := SumIntervalImpl('+000000000 00:00:00.000000000');
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(
self IN OUT SumIntervalImpl
, value IN VARCHAR2)
return NUMBER is
begin
self.total := self.total + to_dsinterval(value);
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
self IN SumIntervalImpl,
returnValue OUT VARCHAR2
, flags IN NUMBER)
return NUMBER is
begin
returnValue := to_char(self.total);
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
self IN OUT SumIntervalImpl
, ctx2 IN SumIntervalImpl)
return NUMBER is
begin
self.total := self.total + to_dsinterval(ctx2.total);
return ODCIConst.Success;
end;
end;
/
create or replace FUNCTION SumInterval (input VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING SumIntervalImpl;
Now verify that the function can handle large figures.
select extract(DAY from total) dt
, extract(HOUR from total) ht
, extract(MINUTE from total) mt from (
select to_dsinterval(suminterval(hm)) as total from (
select NUMTODSINTERVAL(36000,'HOUR') + NUMTODSINTERVAL(20,'MINUTE') as hm
from dual
union all
select NUMTODSINTERVAL(36000,'HOUR') + NUMTODSINTERVAL(50,'MINUTE') as hm
from dual
union all
select NUMTODSINTERVAL(36000,'HOUR') + NUMTODSINTERVAL(20,'MINUTE') as hm
from dual
)
);
DT HT MT
---------- ---------- ----------
4500 1 30
In conclusion, now I can easily compute total labor costs expressed as a function of days, hours and minutes, using plain SQL in my Apex reports without worrying about precision overflow.
See message translations for ORA-01873 and search additional resources.
No comments:
Post a Comment