Some annotations on your query:
DSL.select(ID, DSL.coalesce( ( // These parentheses, while required
in SQL, don't really have any meaning in Java
DSL.select(DSL.field("AMOUNT").sum()).from(RECOMMENDATION_TABLE)
.where(RECOMMENDATION_TABLE.OPPORTUNITY_ID.eq(OPPORTUNITY_TABLE.ID)).asField("FRED")
// why did you alias
the subquery here? ^^^^ ), 0).as("TOTAL"))
.from(INVESTMENT_OPPORTUNITY_TABLE);
The meaning of the FieldLike.asField(String) method is to create an aliased
column expression from whatever FieldLike is, in your case, a correlated
subquery. That certainly makes sense when you put it directly in the SELECT
clause, but not when you nest it somewhere. Do note that aliased
expressions (both of type Field and Table) behave differently depending on
where they're located.
field.as("alias"):
- Will generate the alias declaration "<field> as alias" when put directly
in the SELECT clause
- Will reference the alias everywhere else
table.as("alias"):
- Will generate the alias declaration "<table> as alias" when put directly
in the FROM clause (or in some part of a JOIN graph)
- Will reference the alias everywhere else
This makes sense if you want to reference your correlated subquery named
"FRED" from the ORDER BY clause, or if your correlated subquery was
actually placed in a derived table, and you want to reference it from an
outer query.
The solution is really simple. Just drop the unneeded alias. If you want to
maintain column level type safety, prefer DSL.field(Select<? extends
Record1<T>>) over Select.asField().
Hope this helps,
Lukas
2018-05-15 20:56 GMT+02:00 EREZ KATZ <[email protected]>:
> Hi all,
>
>
> I have this select statement:
>
>
> select
>
> ID,
>
> (
>
> select sum(cast(AMOUNT as numeric))
>
> from "recommendations"
>
> where "recommendations"."opportunity_id" = "opportunities"."id"
>
> ) "TOTAL"
>
> from "opportunities"
>
>
>
> that was generated from this code:
>
>
> DSL.select(ID,
>
> (
>
>
> DSL.select(DSL.field("AMOUNT").sum()).from(RECOMMENDATION_TABLE)
>
>
> .where(RECOMMENDATION_TABLE.OPPORTUNITY_ID.eq(OPPORTUNITY_TABLE.ID)).asField("FRED")
>
> ).as("TOTAL")
>
>
> ).from(OPPORTUNITY_TABLE)
>
>
>
>
> It is ok, but if there are not records in recommendation_table, then total
> would be Null.
>
> I need it to be zero (it throws the sorting off).
>
>
> This works:
>
>
> select
>
> ID,
>
> coalesce((
>
> select sum(cast(AMOUNT as numeric))
>
> from "recommendations"
>
> where "recommendations"."opportunity_id" = "opportunities"."id"
>
> ),0) "TOTAL"
>
> from "opportunities"
>
>
>
>
>
> But that was straight SQL. I tried to use JOOQ to generate it like so:
>
>
>
>
> DSL.select(ID,
>
> DSL.coalesce(
>
> (
>
>
> DSL.select(DSL.field("AMOUNT").sum()).from(RECOMMENDATION_TABLE)
>
>
> .where(RECOMMENDATION_TABLE.OPPORTUNITY_ID.eq(OPPORTUNITY_TABLE.ID)).asField("FRED")
>
> ),0).as("TOTAL")
>
>
> ).from(INVESTMENT_OPPORTUNITY_TABLE);
>
>
> But that "ate" the inner select and generated this:
>
>
>
> select
>
> ID,
>
> coalesce(
>
> "FRED",
>
> 0
>
> ) "TOTAL"
>
> from "opportunities"
>
>
>
> What am I doing wrong?
>
> I am using jooq 3.8.4 (comes with
> com.bendb.dropwizard:dropwizard-jooq:1.0.0-0) and postgres 10.
>
>
> Cordially,
>
>
> Erez
>
>
> --
> 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.
