nested select, sum and coalesce

[EREZ KATZ]

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

[Simon Niederberger]

I usually wrap the field directly with coalesce. Does

DSL.coalesce(DSL.field("AMOUNT"))

work?

[Lukas Eder]

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

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.