Hey Lukas, Sorry about the latency of my response.
This is very interesting from a factoring perspective, since I've always found aliasing quite brittle, and my original post alludes to the fact that this brittleness has come back to bite me. I think I'll redesign my SQL generation libraries to better leverage QueryPart ASTs. Cheers, Ben On Wed, Mar 19, 2014 at 12:16 PM, Lukas Eder <lukas.e...@gmail.com> wrote: > Hi Ben, > > Thanks for the feedback. Getting aliasing and dynamic SQL right is not > always trivial. My recommendation to you is to defer aliasing as long as > possible. If you're using subselects, the most expressive and reliable way > to alias derived tables and their columns is to use derived column lists, > which are supported by jOOQ: > http://www.jooq.org/javadoc/latest/org/jooq/Table.html#as(java.lang.String, > java.lang.String...) > > Derived column lists are emulated using UNION ALL in those databases that do > not support them: > http://blog.jooq.org/2013/01/07/simulating-the-sql-standard > > So, these two expressions are equivalent: > > -- Some databases (e.g. Postgres, SQL Server) > SELECT t.a, t.b > FROM ( > SELECT 1, 2 > ) t(a, b) -- derived column list: t(a, b) > > -- All databases (e.g. Oracle) > SELECT t.a, t.b > FROM ( > SELECT null a, null b FROM DUAL WHERE 1 = 0 > UNION ALL > SELECT 1, 2 FROM DUAL > ) t > > The good thing with dynamic SQL building with jOOQ, however, is the fact > that you often do not need SQL aliases because you can reuse and reference > QueryPart ASTs from your Java code directly. > > Hope this helps > Lukas > > > 2014-03-18 10:43 GMT+01:00 Ben Hood <0x6e6...@gmail.com>: >> >> On Tue, Mar 18, 2014 at 9:36 AM, Ben Hood <0x6e6...@gmail.com> wrote: >> > On Tue, Mar 18, 2014 at 9:29 AM, Lukas Eder <lukas.e...@gmail.com> >> > wrote: >> >> What is the purpose of aliasing that particular expression inside your >> >> toCharge() method? >> > >> > In this context, absolutely none. That was some library code that is >> > used in whole bunch of different queries, but most of the existing >> > calls to that particular method assume that they are being fishing >> > something out of a subselect. So if I modify this particular library >> > to not alias the field before trimming it, that might be the cause of >> > the problem. >> >> So it turns that the issue was PEBCAK - my libraries were too eagerly >> aliasing SQL building blocks. This was working well the code was being >> used for a bunch of subselects, but when I tried to re-use the stuff >> I'd aliased, it blew up. So getting rid of the internal aliasing >> solved the issue. >> >> Thanks very much for the help. >> >> -- >> 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 jooq-user+unsubscr...@googlegroups.com. >> For more options, visit https://groups.google.com/d/optout. > > > -- > 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 jooq-user+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. -- 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 jooq-user+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
