user defined custom aggregate function using plsql

Question

I have a problem.The tool i work on does not accept subqueries. So i have to write a user defined custom aggregate function that does the following.

Example: col1 and col2 has numeric datatype.

**col1**    **col2**
243         401
57489       400
2789        401
598         400

SELECT sum(MinValue) 
FROM
    (SELECT Min(col1) AS MinValue,col2
    FROM
    Table1
    GROUP BY col2)

Output:841

I want this query to work in a single step by using a single function rather than as the query shown above. Something like:

SELECT MyMinSum(col1, col2),col3 from table1 Group by col3;

I hope it makes sense. I will appreciate any input on this.

Answer 1

If you are looking for same output what you are getting with your select statement using sub query, you could achieve the same result by

         SELECT   SUM (MIN (col1)) AS minvalue
 FROM   Table1
GROUP BY   col2

Answer 2

The best way is probably to put your SQL in a view, assuming that works with your tool.

But if you really need a custom aggregate function, there are two main ways to do it. The typical way is to use Oracle Data Cartridge Interface, such as the popular STRAGG. But in my experience, Data Cartridge is confusing, full of bugs, and slow. Usually the COLLECT function works much better. See the example below, or this SQL Fiddle.

create table table1(col1 number, col2 number, col3 number)
/

insert into table1
select 243,   401, 1 from dual union all
select 57489, 400, 1 from dual union all
select 2789,  401, 1 from dual union all
select 598,   400, 1 from dual union all
select 598,   400, 2 from dual
/

create or replace type col1_col2_obj is object
(
  col1 number,
  col2 number
)
/

create or replace type col1_col2_nt is table of col1_col2_obj
/

create or replace function MyMinSum(p_col1_col2_nt in col1_col2_nt)
return number is
  v_result number;
begin
  SELECT sum(MinValue)
  INTO v_result
  FROM
  (
    SELECT Min(col1) AS MinValue,col2
    FROM
    table(p_col1_col2_nt)
    GROUP BY col2
  );

  return v_result;
end;
/

select
  MyMinSum(cast(collect(col1_col2_obj(col1, col2)) as col1_col2_nt)) test
  ,col3
from table1
group by col3
/


TEST    COL3
841     1
598     2