Hi Geoff,
This sounds like a good use for a user-defined aggregate (DERBY-672).
Most of the machinery for this feature actually exists inside Derby. We
would have to agree on some syntax for declaring user-defined aggregates
since ANSI doesn't have language for this. I'd be happy to coach someone
through the work.
Another comment inline...
Six Fried Rice wrote:
I have a table "CUSTOM_FUNCTION" with an ID, and another table
"CUSTOM_FUNCTION_PARAMETER" with a foreign key "ID_CUSTOM_FUNCTION"
such that each CUSTOM_FUNCTION record has 0 or more associated
CUSTOM_FUNCTION_PARAMETER records.
In one situation, it would be exceptionally handy to concatenate all
associated values from a VARCHAR column in the
CUSTOM_FUNCTION_PARAMETER table into a single value in a result set
with one row per CUSTOM_FUNCTION. For instance, suppose I have:
CUSTOM_FUNCTION.ID = 1
CUSTOM_FUNCTION.NAME = "Volume"
CUSTOM_FUNCTION_PARAMETER.ID_CUSTOM_FUNCTION = 1
CUSTOM_FUNCTION_PARAMETER.NAME = "length"
CUSTOM_FUNCTION_PARAMETER.ID_CUSTOM_FUNCTION = 1
CUSTOM_FUNCTION_PARAMETER.NAME = "width"
CUSTOM_FUNCTION_PARAMETER.ID_CUSTOM_FUNCTION = 1
CUSTOM_FUNCTION_PARAMETER.NAME = "height"
I would like a single SQL query that returns a single row like this:
NAME: Volume
PARAMS: length; width; height
Where the "params" result column is a VARCHAR with all three parameter
names concatenated, with semicolon's in between.
In MySQL, I would accomplish this with the odd-but-handy GROUP_CONCAT
function, along these lines:
select
F.NAME as NAME,
GROUP_CONCAT(P.NAME, "; ") as PARAMS
from
CUSTOM_FUNCTION F
left join CUSTOM_FUNCTION_PARAMETER P on P.ID_CUSTOM_FUNCTION = F.ID
group by F.ID
I know that isn't standard, but I'm wondering if there is any clever
approach in Derby to accomplish the same thing. I've been trying to
dream something up, but with no success so far. Of course I know I can
get the same effect by processing the result set on the Java side, but
for various reasons, it would be much more convenient in this case to
let Derby do it for me.
I don't understand the distinction you are drawing between Derby and the
Java side. Derby functions let you run your Java code inside the
database. You can create a user-defined function which loops through
custom_function_parameter inside your query. Then you can write a master
query like this:
select name, concatenateFunctionParameters( id )
from custom_function
In the Derby demo code, you can find an example of how to code and use
one of these aggregating functions. Just grep the demo code for
"getMedianTestScore".
Hope this helps,
-Rick
Any ideas would be appreciated.
Thanks,
Geoff
