-
-
Save henninb/105c26dead2dbbfd92318ce00af02883 to your computer and use it in GitHub Desktop.
| 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) |
@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.
Happens to me too, occasionally when writing integration tests, btw 😅