Oracle unfortunately doesn't support it natively... Some more details here: - http://blog.jooq.org/2013/01/07/simulating-the-sql-standard/ - http://blog.jooq.org/2013/05/03/sql-query-transformation-fun-predicates-with-row-value-expressions/
The closest what Oracle can offer to derived column lists are common table expressions (now supported with jOOQ 3.4), but unlike derived column lists, CTE influence execution plans, so they may not be a good choice in some cases... 2014-07-14 13:23 GMT+02:00 Stanislas Nanchen <[email protected]>: > i did not know that syntax. will dig into it. thanks a lot! :) > cheers stan. > > > On Friday, July 11, 2014 3:27:57 PM UTC+2, Lukas Eder wrote: > >> IMO, it is a design error in the SQL standard :) >> >> >> That's probably true. The ideal way to rename columns from a syntax >> perspective is through derived column lists, e.g. >> >> (SELECT a, b FROM x) AS table(column1, column2) >> >> >> jOOQ supports this syntax, but few people know it. Besides, you will lose >> some of the typesafety. >> >> But simple column aliasing is useful for the quick-and-dirty renaming >> job, so I guess we cannot do without it. Maybe, we can factor out a more >> general use-case for your issue? For that, I'd need to know a bit more >> about that "abstract" field, though. >> >> Best, >> Lukas >> >> 2014-07-11 15:14 GMT+02:00 Stanislas Nanchen <[email protected]>: >> >> Hi Lukas, >>> >>> Thanks for the answer :). Yeah, that's what we do. But conceptually, I >>> have put together an expression that has corresponds to an "abstract" field >>> and it would be nice to be able to manipulating it without having to think >>> in which context it is used. IMO, it is a design error in the SQL standard >>> :) >>> >>> Cheers, stan. >>> >>> On Wednesday, July 9, 2014 11:44:53 AM UTC+2, Lukas Eder wrote: >>> >>>> Hi Stan, >>>> >>>> I believe we've had similar feedback on the user group before but I >>>> cannot seem to find it. In essence, these two expressions are quite >>>> different: >>>> >>>> DSL.coalesce(B, A) >>>> DSL.coalesce(B, A).as("BA") >>>> >>>> >>>> The first is the actual expression whereas the second is a named >>>> reference (alias) to that function. In jOOQ, such named references have two >>>> ways of being rendered: >>>> >>>> - as a declaration (only in the SELECT clause) >>>> - as a reference (in all other clauses) >>>> >>>> Imagine that field is a column from a derived table, then the GROUP BY >>>> clause would behave correctly as it is today. In fact, there are even >>>> databases that allow for referencing column declarations from the SELECT >>>> clause in GROUP BY, e.g. MySQL, SQLite, PostgreSQL: >>>> >>>> SELECT COALESCE(a, b) AS c, count(*) >>>> FROM ( >>>> VALUES(null, 1), >>>> (null, 2), >>>> (1 , 2) >>>> ) AS t (a , b) >>>> GROUP BY c -- Non-standard SQL reference to column alias >>>> ORDER BY c -- Standard SQL reference to column alias >>>> >>>> >>>> Result: >>>> >>>> | C | COUNT | >>>> |---|-------| >>>> | 1 | 2 | >>>> | 2 | 1 | >>>> >>>> >>>> See it in action here: >>>> http://sqlfiddle.com/#!15/d41d8/2513 >>>> >>>> I guess, the workaround is this: >>>> >>>> Field<Long> field = DSL.coalesce(B, A); >>>> dsl.select(field.as("BA")) >>>> .from(Tables.TABLE_X) >>>> .groupBy(field) >>>> >>>> Hope this helps, >>>> Lukas >>>> >>>> 2014-07-07 17:08 GMT+02:00 Stanislas Nanchen <[email protected]>: >>>> >>>>> Hi everyone, >>>>> >>>>> We have the following problem when rendering queries in the Oracle >>>>> Dialect (we are using Jooq 3.2.5) >>>>> Assume we have the following table >>>>> >>>>> | TABLE_X | >>>>> |----|-----------------| >>>>> | ID | NUMBER NOT NULL | >>>>> | A | NUMBER NOT NULL | >>>>> | B | NUMBER | >>>>> |----|-----------------| >>>>> >>>>> We would like to build the following Query >>>>> >>>>> Field<Long> field = DSL.coalesce(B, A).as("BA"); >>>>> dsl.select(field) >>>>> .from(Tables.TABLE_X) >>>>> .groupBy(field) >>>>> >>>>> In the Oracle dialect, this is rendered as follows. >>>>> >>>>> select coalesce(B, A) BA >>>>> from TABLE_X >>>>> group by BA >>>>> >>>>> But aliases are not available in group by expressions and therefore, >>>>> we would like to have >>>>> the following rendering: >>>>> >>>>> select coalesce(B, A) BA >>>>> from TABLE_X >>>>> group by coalesce(B, A) >>>>> >>>>> Have you had the same problem? and if yes how did you solve it? >>>>> Thanks! >>>>> >>>>> Cheers. stan. >>>>> >>>>> -- >>>>> 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/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 [email protected]. >>> 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 [email protected]. > 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
