CODEWITHSUNDEEP
postgresqlpostgresql-9.3jooq
Urco
Urco

Reputation: 365

Call aliased column result of aggregate function JOOQ

I'm currently trying to retrieve a single double value from this query in JOOQ Query Builder and PostgreSQL as the database, providing that DRINKS.PRICE is of type double and ORDER_DRINK.QTY is of type integer.

Record rec = create.select(DSL.sum(DRINKS.PRICE.multiply(ORDER_DRINK.QTY)).as("am_due")).from(ORDERS
                        .join(ORDER_DRINK
                            .join(DRINKS)
                            .on(DRINKS.DRINK_KEY.equal(ORDER_DRINK.DRINK_KEY)))
                        .on(ORDERS.ORDKEY.equal(ORDER_DRINK.ORDER_KEY)))
                        .where(ORDERS.TOKEN.eq(userToken))
                        .fetchOne();

As I've understood from the (brief) tutorial, once I retrieve the value from that aliased record, in the form:

double v = rec.getValue("am_due");

I should have the sum of all the prices multiplied by their quantities. Still, I get a NullPointerException instead.

Any help would be very welcome. Thank you.

Upvotes: 1

Views: 723

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

Your jOOQ usage is correct, but I suspect that your sum is simply null because:

  • Your join doesn't return any records
  • All of your multiplication operands are null

and since you're unboxing Double to double, you're getting that NullPointerException. You can solve this

... using Java:

Double v1 = rec.getValue("am_due");               // will return null
double v2 = rec.getValue("am_due", double.class); // will return 0.0

... using jOOQ / SQL

Record rec = create.select(DSL.nvl(
  DSL.sum(
    DRINKS.PRICE.multiply(ORDER_DRINK.QTY)
  ), 0.0
).as("am_due"))

Upvotes: 1

Related Questions