Hi John,
You may be able to press user-defined aggregates into service here. They
are a new feature available in the development trunk and slated for
release 10.10 next year. To demonstrate how to do this, I'm enclosing an
ij script plus two Java classes which support the script.
Hope this helps,
-Rick
---------------
First the script:
connect 'jdbc:derby:memory:db;create=true';
create table products( userName varchar( 10 ), product varchar( 10 ),
quantity int );
insert into products values ( 'me', 'abc', 3 ), ( 'you', 'abc', 5 ), (
'you', 'def', 7 );
select * from products;
create type HashMap external name 'java.util.HashMap' language java;
create type NamedInt external name 'NamedInt' language java;
create function nameInt( n varchar( 10 ), v int ) returns NamedInt
language java parameter style java no sql
external name 'NamedInt.nameInt';
create function getInt( n varchar( 10 ), h HashMap ) returns int
language java parameter style java no sql
external name 'NamedInt.getInt';
create derby aggregate nia for NamedInt returns HashMap external name
'NamedIntAggregator';
-- pivot the table
select userName,
getInt( 'abc', nia ) "abc",
getInt( 'def', nia ) "def"
from
(
select userName, nia( nameInt( product, quantity ) ) nia
from products
group by userName
) s
;
Now the two classes:
public class NamedInt implements java.io.Serializable
{
public final String name;
public final int value;
private NamedInt( String n, int v ) { name = n; value = v; }
public static NamedInt nameInt( String n, int v ) { return new
NamedInt( n, v ); }
public static Integer getInt( String n,
java.util.HashMap<String,Integer> h ) { return h.get( n ); }
}
... and
import java.util.HashMap;
public class NamedIntAggregator implements
org.apache.derby.agg.Aggregator<NamedInt,HashMap,NamedIntAggregator>
{
private HashMap<String,Integer> _accumulator;
public NamedIntAggregator() {}
public void init() { _accumulator = new
HashMap<String,Integer>(); }
public void accumulate( NamedInt ni ) { _accumulator.put(
ni.name, ni.value ); }
public void merge( NamedIntAggregator otherAggregator ) {
_accumulator.putAll( otherAggregator._accumulator ); }
public HashMap terminate() { return _accumulator; }
}
