Translate complex nested SQL subqueries to JOOQ

Question

I'm having some issues translating SQL queries to JOOQ. So the alias sugActive within join clause is unknown. There might be a better way to write this. So, I still can't figure out what's wrong. I appreciate any tips, thanks in advance.

1. The SQL query:

        select
        count(campId) as 'campCount',
        count(case when targetTest != 0 then campId else null end) as 'targetTestSet',
        count(case when targetTest = 0 then campId else null end) as 'targetTestNotSet',
        coalesce(sum(sugActive), 0) as 'sugActive',
        coalesce(sum(sugApproved), 0) as 'sugApproved',
    from (
        select 
            c.venId,
            c.name,
            s.*
        from campTable c
        join (select s.campId,
                    count(case when s.status = 'ACTIVE'
                        then id end) as 'sugActive',
                    count(case when s.status = 'APPROVED'
                        then id end) as 'sugApproved',
                from sugTable s
                group by s.campId
            ) s on c.campId = s.campId
        where c.state = 'enabled'
    ) a;

return

2. The JOOQ query:

    Field<Integer> sugActive = igDb
    .select(
        count(when(
            sugTable.status.eq("ACTIVE"), sugTable.campId)
            .as("sugActive")))
    .from(sugTable).asField("sugActive");
Field<Integer> sugApproved = igDb
    .select(
            count(when(
                sugTable.status.eq("APPROVED"), sugTable.campId)
                .as("sugApproved")))
    .from(sugTable).asField("sugApproved");
IgOrgSumRecord result = igDb
    .select(
        count(campTable.venId).as("venIdCount"),
        count(sugTable.campId).as("campCount"),
        count(when(
            campTable.targetTest.notEqual(BigDecimal.ZERO), sugTable.campId
            ).otherwise("")).as("targetTestSet"),
        count(when(
            campTable.targetTest.eq(BigDecimal.ZERO), sugTable.campId
            ).otherwise("")).as("targetTestNotSet"),
        coalesce(sum(sugActive)).as("sugActive"),
        coalesce(sum(sugApproved)).as("sugApproved"))
    .from(select(
        campTable.venId,
        campTable.name,
        sugTable.asterisk()
    )
        .from(campTable.as("c"))
        .join(
            select(
                sugTable.campId,
                count(when(
                    sugTable.status.eq("ACTIVE"), sugTable.campId)
                    .as("sugActive")),
                count(when(
                    sugTable.status.eq("APPROVED"), sugTable.campId)
                    .as("sugApproved"))
            )
                .from(sugTable)
                .groupBy(sugTable.campId)
        )
        .on(campTable.campId.eq(sugTable.campId))
        .where(campTable.state.eq("enabled"))
    )
.fetchOneInto(IgOrgSumRecord.class);

return

The error I get: Unknown column 'sugActive' in 'field list'.

Answer 1

The problem is this:

Field<Integer> sugActive = igDb
    .select(
        count(when(
            sugTable.status.eq("ACTIVE"), sugTable.campId)
            .as("sugActive")))
    .from(sugTable).asField("sugActive");

This is not just a column expression, it's an alias declaration (or reference). You're then using the alias reference, re-aliasing it, when you actually wanted to use the expression:

coalesce(sum(sugActive)).as("sugActive"),

Instead, declare your sugActive variable like this:

Field<Integer> sugActive = field(igDb
    .select(
        count(when(
            sugTable.status.eq("ACTIVE"), sugTable.campId)
            .as("sugActive")))
    .from(sugTable));

See also the explanation here: https://github.com/jOOQ/jOOQ/issues/10119