Archive for November, 2009

Why would you want a feature like this?

Scenarios:
- You want to group by but treat a specific value to overrule no matter what else is specified it would look like:

select id, strpref(string_prefered_pars_type(status, status, ‘active’)) from tablename group by id;
(prefer the ‘active’ value above all else)

- The value has a dependency on another column which looks like:

select id, strpref(string_prefered_pars_type(return_column, pref_dependency_column, ‘active’)) from tablename group by id;
(prefer the value from a record which has its pref_dependency_column set to ‘active’)

Naturally with some code changes it enables all kind of fun aggregation features!

Everything below the line is Oracle PL/SQL example code which demonstrates how a custom aggregate function can be created which accepts multiple parameters by encapsulating the parameters in a custom type.

/*
quote:
There is no support for writing custom aggregate functions that accept multiple parameters.

source:
Build Custom Aggregate Functions
By Jonathan Gennick

http://www.oracle.com/technology/oramag/oracle/06-jul/o46sql.html

This example uses a custom type to use a custom aggregate function that accepts multiple parameters through parameter encapsulation!

more references:

Oracle® Database Data Cartridge Developer's Guide, 10g Release 2 (10.2)
11 User-Defined Aggregate Functions

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm

*/

create or replace type STRING_PREFERED_PARS_TYPE as object
(
-- Author  : O. Lissenberg
-- Created : 4-11-2009
-- Purpose : Passes parameters to the STRING_PREFERED_TYPE's ODCIAggregateIterate function

-- Attributes
strvalue varchar2(100),
pref varchar2(10),
prefvalue varchar2(10)

-- Member functions and procedures
-- N.a.
)
/
create or replace type string_prefered_type as object
(
retvalue varchar2(4000),

static function ODCIAggregateInitialize(sctx IN OUT string_prefered_type) return number,
member function ODCIAggregateIterate(self IN OUT string_prefered_type, value IN STRING_PREFERED_PARS_TYPE) return number,
member function ODCIAggregateTerminate(self IN string_prefered_type, returnValue OUT varchar2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT string_prefered_type, ctx2 IN string_prefered_type) return number
);
/
create or replace type body string_prefered_type is
static function ODCIAggregateInitialize(sctx IN OUT string_prefered_type)
return number is
begin
sctx := string_prefered_type(null);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_prefered_type, value IN STRING_PREFERED_PARS_TYPE)
return number is
begin

IF self.retvalue is null THEN
self.retvalue := value.strvalue;
ELSIF value.pref is not null AND lower(value.pref) = lower(value.prefvalue) THEN
-- only change the return value if the prefered value is found
-- hence otherwise the behavior is "the first non null value is accepted and does not change", which
-- might be nice to change into a max() or min() feature.
self.retvalue := value.strvalue;
END IF;

return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_prefered_type, returnValue OUT varchar2, flags IN number)
return number is
begin
-- just return the set value.
returnValue := self.retvalue;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_prefered_type, ctx2 IN string_prefered_type)
return number is
begin
-- this might prove to be difficult, it's easier to disable PARALLEL execution
-- to enable though, a state needs to be added to enable the merge, the state
-- would flag if the current or other context value was set with a "prefered" value.

return ODCIConst.Success;
end;
end;
/
CREATE or replace
FUNCTION strpref(input STRING_PREFERED_PARS_TYPE)
RETURN varchar2
AGGREGATE USING string_prefered_type;
-- PARALLEL_ENABLE (to enable: implement the merge function)
/
select t.id ,strpref(STRING_PREFERED_PARS_TYPE(t.name, t.pref, 'pickme'))
from testgroupby t group by t.id;

/*
CREATE TABLE "TESTGROUPBY"
(  "ID" NUMBER,
"NAME" NVARCHAR2(10),
"PREF" NVARCHAR2(10) );

insert into TESTGROUPBY (id,name,pref) values (1,'test1','ignoreme');
insert into TESTGROUPBY (id,name,pref) values (1,'test2','pickme');
insert into TESTGROUPBY (id,name,pref) values (1,'test3','ignoreme');
insert into TESTGROUPBY (id,name,pref) values (2,'test4','pickme');
insert into TESTGROUPBY (id,name,pref) values (2,'test5','pickme');
insert into TESTGROUPBY (id,name,pref) values (2,'test6','ignoreme');

select t.id ,strpref(STRING_PREFERED_PARS_TYPE(t.name, t.pref, 'pickme'))
from testgroupby t group by t.id;

drop table TESTGROUPBY;
*/