Hi!
We just encountered the following problem using LIMIT in oracle (11....)
with the following query. (JOOQ 3.2.2)
.delete(RC_BOX)
.where(
RC_BOX.ID.in(DSL
.select(RC_BOX.ID)
.from(RC_BOX)
.where(RC_BOX.LC_ASSORTMENT_ID.equal(lcAssortmentId))
.limit(oldNumberOfBoxesValue - newNumberOfBoxes
)));
The problem is that the Oracle dialect rewrite the query with a help
expression (rownum) that is
not filtered out in the final subselect and the above statement cannot be
executed.
SQL [delete from STAN1.RC_BOX
where STAN1.RC_BOX.ID in (
select * from (
select LIMIT_13865716.*, rownum as ROWNUM_13865716
from (
select STAN1.RC_BOX.ID
from STAN1.RC_BOX
where STAN1.RC_BOX.LC_ASSORTMENT_ID = ?
) LIMIT_13865716
where rownum <= (? + ?)
)
where ROWNUM_13865716 > ?
)]; ORA-00913: too many values
As workaround, we tried the following and it seems to work, but it's a bit
ugly.
.delete(RC_BOX)
.where(
RC_BOX.ID.in(DSL
.select(field("ID", Long.class))
.from(DSL
.select(RC_BOX.ID)
.from(RC_BOX)
.where(RC_BOX.LC_ASSORTMENT_ID.equal(lcAssortmentId))
.limit(oldNumberOfBoxesValue - newNumberOfBoxes))
));
Has someone else encountered the same problem?
Cheers.
stan.
--
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.
