Error in Query - missing FROM-clause entry for table - SQL

FROM tempstockpos, cte WHERE .... AND tempstockpos.batchid = cte.batchid

FROM tempstockpos INNER JOIN cte ON  tempstockpos.batchid = cte.batchid WHERE ....

SELECT "region", sum("items"), sum("actcost")
FROM "apr","Regions"
WHERE "bnfname" LIKE 'Flucloxacillin %' AND "apr"."sha"="Regions"."sha"
GROUP BY "region"

if i == 0
  q = "universities.state_id = state.id and states.code = ?"
else
  q += " OR universities.state_id = state.id and states.code = ?"
end

if i == 0
  q = "universities.state_id = states.id and states.code = ?"
else
  q += " OR universities.state_id = states.id and states.code = ?"
end

select 
  "alias_88420990"."ledger_id", -- These are correctly referenced, because you used
  "alias_88420990"."amount"     -- select(), so jOOQ did the dereferencing for your
from (
  select 
    "public"."test_safes_funds_allocation"."ledger_id", 
    sum("public"."test_safes_funds_allocation"."amount") as "amount"
  from "public"."test_safes_funds_allocation" 
  group by "public"."test_safes_funds_allocation"."ledger_id"
) as "alias_88420990" -- This alias is generated by jOOQ

-- In these predicates, you're referencing the original column name with full qualification
-- when you should be referncing the column from alias_88420990 instead
where ("public"."test_safes_funds_allocation"."amount" >= ? and 1 = 1) 
order by "public"."test_safes_funds_allocation"."amount" asc 
limit ?

// Create a local variable to contain your subquery. Ideally, provide an explicit alias
Table<?> t = table(
    select(
        TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID,       
        sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT)
           .as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))
   .from(TEST_SAFES_FUNDS_ALLOCATION)
   .groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)).as("t");

// Now, use t everywhere, instead of TEST_SAFES_FUNDS_ALLOCATION
context.select()
       .from(t)
       .where(t.field(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).greaterOrEqual(amount))
       .and(t.field(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID).notIn(excludedLedgers))
       .orderBy(t.field(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).asc())
       .limit(1)
       .fetchOne();

// This t reference now has all the column references like the original table
TestSafesFundsAllocation t = TEST_SAFES_FUNDS_ALLOCATION.as("t");

// The subquery is also named "t", but has a different definition
Table<?> subquery = table(
    select(
        TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID,       
        sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT)
           .as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))
   .from(TEST_SAFES_FUNDS_ALLOCATION)
   .groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)).as(t);

// Now, select again from the subquery, but dereference columns from the aliased table t
context.select()
       .from(subquery)
       .where(t.AMOUNT.greaterOrEqual(amount))
       .and(t.LEDGER_ID.notIn(excludedLedgers))
       .orderBy(t.AMOUNT.asc())
       .limit(1)
       .fetchOne();

context.select(
          TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID, 
          sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))
       .from(TEST_SAFES_FUNDS_ALLOCATION)
       .where(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID.notIn(excludedLedgers))
       .groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)
       .having(sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).greaterOrEqual(amount))
       .orderBy(sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).asc())
       .limit(1)
       .fetchOne()