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.
