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]>: > 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]. > 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.
