Corelated subquery with max

[Todd Costella]

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

[Lukas Eder]

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

--
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+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Todd Costella]

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

[Lukas Eder]

Hi Todd,

2016-05-26 4:52 GMT+02:00 Todd Costella <toddco...@gmail.com>:

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

[Lukas Eder]

Egh...

Forget what I wrote. The exact moment I had sent the previous E-Mail, I realised what the mistake was here:

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();

You accidentally used Object.equals() rather than Field.equal(), which is also why the 1 = 0 predicate is generated. You're simply passing the Java boolean "false" to where(). This is accepted, because there is a controversial where(Boolean) overload that swallows any warnings about potentially bad API usage.

Others have run into this issue as well, which is why we deprecated these overloads. In the latest jOOQ versions, you should see a deprecation warning on the where() clause.

That was tough to spot! :) Hope it helps

Lukas