Hi Ben, 2013/8/5 Ben Hood <[email protected]>
> 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. > You're probably right with this. I haven't evaluated the depths of the STUFF solution yet, as this is really a low-prio improvement. But it appeared to be quite a bit of a hack to me, at the time. Similar to jOOQ's RPAD/LPAD emulation for SQLite: http://blog.jooq.org/2012/07/19/funky-string-function-simulation-in-sqlite > 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. > I see. Yes, this kind of thing is on the roadmap: https://github.com/jOOQ/jOOQ/issues/2337 It's certainly worth thinking about these things, in order to support arbitrary SQL dialects more easily. My first evaluations of this functionality showed, however, that it will be quite hard to implement the full jOOQ SQL feature support in an external file. It might still be good enough for tweaking, though. Another option is to think about this new idea I've had, recently, in the context of a customer requirement: https://github.com/jOOQ/jOOQ/issues/2665 This issue will allow for providing a custom SQL rendering and variable binding SPI implementation. The original requirement was to be able to dynamically append predicates to WHERE clauses in the event of access to any given table X. Of course, this functionality could be used to re-write jOOQ's SQL generation for 1-2 types of API elements, such as LISTAGG(). > 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. > I'm always open to suggestions. > 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). > I have a strong feeling that the LISTAGG syntax might make it into the SQL standard. Not necessarily the function name, but the ordered aggregate clause: WITHIN GROUP (ORDER BY ...) The joys of "why isn't feature X supported in Y database" :-) > Yes. When you check out the jOOQ code with respect to SQL standardisation, it's crazy how many different ways of doing the same exist... Cheers Lukas -- 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.
