Custom custom aggregation function using plsql

Question

I have a problem. The tool I'm working on does not accept subqueries. So I have to write a custom custom aggregation function that does the following.

Example: col1 and col2 have a numeric data type.

**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 request to work in one step using one function, and not as the request shown above. Sort of:

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

Hope this makes sense. I would appreciate any input on this.

Answer 1

If you are looking for the same result as you get with the select statement using a subquery, you can achieve the same result with

  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 it works with your tool.

But if you really need a custom aggregate function, there are two main ways to do this. A typical way is to use the Oracle Data Cartridge Interface , for example, the popular STRAGG . But, in my experience, Data Cartridge is confusing, full of errors and slowing down. Normally, the COLLECT function works much better. See the example below or 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