I am trying to execute the following query:

-- find all resources that do not have assignments
SELECT *
FROM RESOURCE
WHERE ID NOT IN (SELECT RESOURCEID_FK FROM RES_ISSUE_ASSIGNMENT
UNION
SELECT RESOURCEID_FK FROM RES_RECEIPT_ASSIGNMENT);

My jooQ is like this:

DSLContext dsl = getDSLContext();

SelectOrderByStep<Record1<Integer>> allAssignments = 
dsl.select(RES_ISSUE_ASSIGNMENT.RESOURCEID_FK)
        .from(RES_ISSUE_ASSIGNMENT)
        
.union(dsl.select(RES_RECEIPT_ASSIGNMENT.RESOURCEID_FK).from(RES_RECEIPT_ASSIGNMENT));

Result<ResourceRecord> resourceRecords = 
dsl.selectFrom(RESOURCE).where(RESOURCE.ID.notIn(allAssignments)).fetch();


The created SQL is throwing a derby exception:


Exception in thread "main" org.jooq.exception.DataAccessException: SQL [select 
"APP"."RESOURCE"."ID", "APP"."RESOURCE"."NAME", "APP"."RESOURCE"."TERMINAL_FK", 
"APP"."RESOURCE"."CAPACITY", "APP"."RESOURCE"."THROUGHPUT_GPM", 
"APP"."RESOURCE"."COMMENT" from "APP"."RESOURCE" where "APP"."RESOURCE"."ID" 
not in (select * from (select "APP"."RES_ISSUE_ASSIGNMENT"."RESOURCEID_FK" from 
"APP"."RES_ISSUE_ASSIGNMENT") x union select * from (select 
"APP"."RES_RECEIPT_ASSIGNMENT"."RESOURCEID_FK" from 
"APP"."RES_RECEIPT_ASSIGNMENT") x)]; 'SELECT *' only allowed in EXISTS and NOT 
EXISTS subqueries.
        at org.jooq_3.10.4.DERBY.debug(Unknown Source)
        at org.jooq.impl.Tools.translate(Tools.java:2239)
        at 
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:704)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:361)
        at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:317)
        at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2597)


As one can see, the generated SQL contains "extra" select *.  I don't 
understand why this occurs.  I am assuming that my jOOQ code is not 
appropriate in some way, but after looking at the manual, I cannot tell why.

Thanks!

-- 
You received this message because you are subscribed to the Google Groups "jOOQ 
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to