select e.user_id,decode(to_char(e.datatype),'3',max(e.cell_value),'2',f_sum(e.cell_value)) cell_value
from e_1099310300000000002 e
group by e.user_id,atd.datatype
这样的话就不用管sum(e.cell_value)是字符就不会报ora-01722错了,当然也可以合并,这里需求是取max不是合并
create or replace type t_sum as object (
--author lidongbo
var varchar2(999),
static function ODCIAggregateInitialize(sctx In Out t_sum) return number,
member function ODCIAggregateIterate(self In Out t_sum, value In varchar2) return number,
member function ODCIAggregateMerge(self In Out t_sum, ctx2 In t_sum) return number,
member function ODCIAggregateTerminate(self In Out t_sum, returnValue Out varchar2, flags In varchar2) return number
)
create or replace type body t_sum is
--author lidongbo
static function ODCIAggregateInitialize (sctx In Out t_sum)
return number is
begin
sctx := t_sum(0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate (self In Out t_sum, value In varchar2)
return number is
begin
self.var := self.var + value;
return ODCIConst.Success;
EXCEPTION
WHEN OTHERS THEN
IF SELF.var < VALUE THEN
SELF.var := VALUE;
END IF;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self In Out t_sum, ctx2 In t_sum)
return number is
BEGIN
self.var := self.var;
return ODCIconst.Success;
end;
member function ODCIAggregateTerminate(self In Out t_sum, returnValue Out varchar2, flags In varchar2)
return number is
begin
returnValue := self.var;
return ODCIConst.Success;
end;
end;
CREATE OR REPLACE FUNCTION f_sum(input varchar2 )
--author lidongbo
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING t_sum;