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.
