Hi Lukas, On Tue, Aug 6, 2013 at 9:18 AM, Lukas Eder <[email protected]> wrote: > 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
Cool - I like the way people find ways around the idiosyncrasies of all of the different DBs out there :-) > 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. That sounds like a good start. > 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(). This sounds a bit more powerful than the external file approach. Not that I claim to know anything about design, but this approach gives me the impression that I can selectively decorate the render phase according to my custom dialect. >> 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 ...) OK, so what I've ended up having to do is implement my own custom aggregation function in the CLR that implements the WITHIN GROUP (ORDER BY ...) semantics that Oracle provides out of the box. Basically ordered set functions are not supported in SQL Server - see here: https://connect.microsoft.com/SQLServer/feedback/details/728969. So where I've ended up from a JOOQ perspective is that I can easily create a DSL field object that will render the correct function name, so all is good with JOOQ for now :-) In addition, I just wanted to say that the SQL generation that JOOQ has allowed me to build has been invaluable in maintaining ports of non-trivial queries across Oracle, MySQL, Postgres and SQL Server - so thanks for such a great library :-) 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.
