Jooq.groovy

groovy

        Field TOTALS_DEBITS = dslContext.select(DSL.coalesce(DSL.sum(T_TRANSACTION.AMOUNT), 0.0).as("debits"))
                .from(T_TRANSACTION)
                .where(T_TRANSACTION.ACTIVE_STATUS.eq(true) & T_TRANSACTION.ACCOUNT_TYPE.eq("debit")).asField()

        Field TOTALS_CREDITS = dslContext.select(DSL.coalesce(DSL.sum(T_TRANSACTION.AMOUNT), 0.0).as("credits"))
                .from(T_TRANSACTION)
                .where(T_TRANSACTION.ACTIVE_STATUS.eq(true) & T_TRANSACTION.ACCOUNT_TYPE.eq("credit")).asField()


        return dslContext.select((TOTALS_DEBITS - TOTALS_CREDITS).as("totals"))
                .fetchOneInto(Summary)

You forgot the FROM clause in at least one of your queries ;-)

Happens to me too, occasionally when writing integration tests, btw 😅

@lukaseder that was a really good catch on the FROM clause missing. I totally missed that. Thank you.

@lukaseder I updated my code above and I am still seeing the same error.

org.jooq.exception.DataAccessException: SQL [select (select ("debits" - "credits") as "totals") as "totals"]; ERROR: column "debits" does not exist

what is strange is I can dslContext.select((TOTALS_DEBITS, TOTALS_CREDITS) without issues. Please let me know if you have any other thoughts and thanks again.

Ah, I see. It's because you're aliasing your subqueries using .asField("debits"). That's short for .asField().as("debits"). There's no purpose to this aliasing, so just remove it

@lukaseder thanks again for your time and assistance.

When I remove the .asField("debits") and .asField("credits"), I am not allowed to subtract the 2 fields because they are of type SelectConditionStep. I assume I want these variables to be of type Field so I can subtract them?

@lukaseder I got it working with the code above. Thank you so much for walking through my struggles with me.