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.
