Hey Lukas,
On Aug 5, 2013, at 7:37, Lukas Eder <[email protected]> wrote:
> Yes, this LISTAGG() emulation idea using SQL Server's STUFF is a bit funky.
>
The specific issue I have with it is that it doesn't behave like other
aggregation functions - you can't aggregate a single column, since it
concatenates each entire row of the projection. This makes it difficult to
compose with subqueries. I would say that this technique is more of a result
wrapping function that can be made to look like an aggregation for relatively
simple use cases.
> I prefer not to rely on such a third-party extension that might be installed
> in SQL Server (or not). Think about the various other "useful" functions that
> are present in 1-2 databases but missing in the remaining 12, supported by
> jOOQ.
Sorry, I expressed myself badly - I wasn't suggesting that JOOQ should
implement this 3rd party extension out of the box (for the very reason you make
above).
I was wondering whether there was a way for application level code to extend
the groupConcat() function based on the dialect, rather than having to wire in
a special case DSL field. This would increase the reuse of my query building
blocks.
> Of course, you can create your own function, using plain SQL:
>
> public static Field<String> groupConcat(Field<String> column) {
> return DSL.field("dbo.GROUP_CONCAT({0})",
> String.class, column);
> }
>
> public static Field<String> groupConcat(Field<String> column, String
> delimiter) {
> return DSL.field("dbo.GROUP_CONCAT({0}, {1})",
> String.class, column, val(delimiter));
> }
Yes, this is exactly what I have got right now. Basically I've wired in the
dialect to all query building blocks so that I can do the dialect specific
stuff. For example, I've also created an MD5 function in the dbo namespace. It
works well enough for now, but I was wondering there is a neater way to do this
kind of thing.
>
> Note, jOOQ should probably support code generation for custom aggregate
> functions. This is currently only supported in Oracle. I have added a feature
> request for this:
> https://github.com/jOOQ/jOOQ/issues/2677
I guess what I'm trying to do is to monkey patch the groupConcat() and redirect
it to the custom aggregate function for SQL Server. If I was trying to get
groupConcat() to be transparent across dialects, I probably wouldn't want to
overload groupConcat(), instead I would use a separate function name (although
the 3rd party function is actually called GROUP_CONCAT).
The joys of "why isn't feature X supported in Y database" :-)
Cheers,
Ben
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
