Hi Lukas, The output from these two JOOQ statements was captured using an ExecuteListener inspired by the Custom Loggin ExecuteListener example found at http://www.jooq.org/doc/3.8/manual/sql-execution/execute-listeners/.
I haven't written a ton of JOOQ code (yet), but what I have written (and logged) seems to be correctly rendered by my implementation of an ExecuteListener. It's possible that this generated statement isn't actually what JOOQ is generating, but I don't know of an other way of capturing that. I'm a bit confused by your comment about JOIN and no WHERE clause. Are you referring to the subquery not having a where clause? The outer part of the query has both the join and the where. Thanks for the help, again I'm sure this is something silly on my end. Todd On Wednesday, May 25, 2016 at 6:59:27 AM UTC-6, Lukas Eder wrote: > > Hi Todd, > > Are you sure the generated SQL is really produced from those jOOQ > statements? In both of your jOOQ statements, I see you're using a JOIN and > no WHERE clause... > > Best > Lukas > > 2016-05-24 20:02 GMT+02:00 Todd Costella <[email protected] > <javascript:>>: > >> Hi all, >> >> I have what I think is a simple question but for the life of me I can't >> seem to get to work. >> >> Given the following (oracle) sql query: >> >> SELECT ACL.AGMT_CNTRCT_LINE_ID, >> ACL.AGMT_CNTRCT_ID, >> ACV.CNTRCT_REF >> FROM agmt_cntrct_line acl, >> agmt_cntrct_v acv >> WHERE ACL.AGMT_CNTRCT_ID = ACV.AGMT_CNTRCT_ID >> AND acv.effective_date = >> (SELECT MAX(acv.effective_date) FROM agmt_cntrct_v a WHERE >> a.AGMT_CNTRCT_ID = acv.agmt_cntrct_id) >> >> I would like to jooqify this but am struggling with the subquery part. >> >> I've tried: >> >> >> AgmtCntrctV version = AGMT_CNTRCT_V.as("version"); >> Result<Record4<BigDecimal, BigDecimal, String,Timestamp>> >> agmtContractLineRecords = >> >> sql.select(AGMT_CNTRCT_LINE.AGMT_CNTRCT_LINE_ID,AGMT_CNTRCT_LINE.AGMT_CNTRCT_LINE_ID,AGMT_CNTRCT_V.CNTRCT_REF,AGMT_CNTRCT_LINE.UPDATE_DATE) >> .from(AGMT_CNTRCT_LINE) >> .join(AGMT_CNTRCT_V) >> >> .on(AGMT_CNTRCT_LINE.AGMT_CNTRCT_ID.equal(AGMT_CNTRCT_V.AGMT_CNTRCT_ID)) >> >> .where(AGMT_CNTRCT_V.EFFECTIVE_DATE.equals(sql.select(max(AGMT_CNTRCT_V.EFFECTIVE_DATE)).from(version).join(AGMT_CNTRCT_V).on(version.AGMT_CNTRCT_ID.eq(AGMT_CNTRCT_V.AGMT_CNTRCT_ID)))) >> .fetch(); >> >> >> and inspired by: >> http://www.jooq.org/doc/3.6/manual/sql-building/table-expressions/nested-selects/ >> >> >> >> AgmtCntrctV version = AGMT_CNTRCT_V.as("version"); >> Field<Object> subselect = >> sql.select(max(version.EFFECTIVE_DATE)).from(version).join(AGMT_CNTRCT_V).on(version.AGMT_CNTRCT_ID.eq(AGMT_CNTRCT_V.AGMT_CNTRCT_ID)).asField(); >> Result<Record4<BigDecimal, BigDecimal, String,Timestamp>> >> agmtContractLineRecords = >> >> sql.select(AGMT_CNTRCT_LINE.AGMT_CNTRCT_LINE_ID,AGMT_CNTRCT_LINE.AGMT_CNTRCT_LINE_ID,AGMT_CNTRCT_V.CNTRCT_REF,AGMT_CNTRCT_LINE.UPDATE_DATE) >> .from(AGMT_CNTRCT_LINE) >> .join(AGMT_CNTRCT_V) >> >> .on(AGMT_CNTRCT_LINE.AGMT_CNTRCT_ID.equal(AGMT_CNTRCT_V.AGMT_CNTRCT_ID)) >> .where(AGMT_CNTRCT_V.EFFECTIVE_DATE.equals(subselect)) >> .fetch(); >> >> >> Looking at the generated sql I get: >> select >> "AGMT_CNTRCT_LINE"."AGMT_CNTRCT_LINE_ID", >> "AGMT_CNTRCT_LINE"."AGMT_CNTRCT_LINE_ID", >> "AGMT_CNTRCT_V"."CNTRCT_REF", >> "AGMT_CNTRCT_LINE"."UPDATE_DATE" >> from "AGMT_CNTRCT_LINE" >> join "AGMT_CNTRCT_V" >> on "AGMT_CNTRCT_LINE"."AGMT_CNTRCT_ID" = >> "AGMT_CNTRCT_V"."AGMT_CNTRCT_ID" >> where (0 = 1) >> >> Obviously the where clause on the subquery isn't resolving as I would >> expect. >> >> I'm likely doing something very silly, but I can't see it. >> >> Any suggestions? >> >> Thanks a bunch in advance. >> >> Todd >> >> >> >> -- >> 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 [email protected] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
