I have question. Already i have avg_new function, which include nulls (as 0) in the result. I have start from linkedin link. Code:
select avg(a),avg_new(nvl(a,-9999)) from
(select 'test' h, 2 a from dual
union all
select 'test' h, null a from dual
union all
select 'test' h, 2 a from dual
union all
select 'test' h ,2 a from dual)
the results are
2; 1,5
I would like to extend avg_new function by adding denominator parameter ex.:
avg_new(nvl(a,-9999),10)
Te result should be then 0.6
Default value of the parameter would be null, then function works as previous example. If the parameter would be >0 then I would divide sum of 'a' by value of this parameter. How i could do this? I would like to pass this parameter to used type object and to perform further calculations there. Is it possible?
create or replace FUNCTION avg_new (input NUMBER , denominator NUMBER DEFAULT NULL) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING T_avg_new;
Right now the type could proper read only the first parameter. After adding i have errors:
ORA-29925: cannot execute T_avg_new.ODCIAGGREGATEINITIALIZE
ORA-06553: PLS-306: wrong number or types of arguments in call to "ODCIAGGREGATEINITIALIZE"
- 00000 - "cannot execute %s"
*Cause: The specified function does not exist or does not have an appropriate signature.
*Action: Implement the function with the appropriate signature.