db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Concatenating text from multiple rows
Date Mon, 30 Jun 2008 13:18:22 GMT
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


Mime
View raw message