Hi Todd, 2016-05-26 4:52 GMT+02:00 Todd Costella <[email protected]>:
> 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. > The easiest way to see what jOOQ is really doing is by turning on debug logging: http://www.jooq.org/doc/latest/manual/sql-execution/logging Behind the scenes, this activates the LoggerListener, so this will have the same effect as your approach 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. > Yes. You're expecting this: 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) But you wrote this: 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(); So, you're expecting "WHERE" but you wrote "JOIN". Specifically, from what you posted so far, I have no explanation for the 1 = 0 predicate, which is why I asked whether you're sure that what you posted corresponds to what you executed... It looks like a debugging mistake to me. In any case, your API usage is correct, it's just not the query that you wanted to write... -- 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.
