|
Description:
|
I'm trying to make a select for update that returns only 1 row and in the query there is an order by:
{code:borderStyle=solid}
<query name="msg_m_to_b.select.head.by.userId.appId">
from MessageMtoB msg
where msg.userId = ? and msg.applicationId = ? and msg.blockId is null
order by msg.id asc
</query>
Query query = session.getNamedQuery("msg_m_to_b.select.head.by.userId.appId");
query.setMaxResults(1);
query.setLockMode("msg", LockMode.UPGRADE);
query.setLong(0, userId);
query.setLong(1, appId);
message = (IMessage) query.uniqueResult();
{code}
The query that generates is incorrect:
{code:borderStyle=solid}
select * from (
select messagemto0_.MOAPVKMSGMT_ID as MOAPVKMS1_, messagemto0_.MOAPVUMSGMT_MESSAGE_ID as MOAPVUMS2_6_, messagemto0_.MOAPNFMSGMT_USER_ID as MOAPNFMS3_6_, messagemto0_.MOAPNKMSGMT_LICENSE_ID as MOAPNKMS4_6_, messagemto0_.MOAPVFMSGMT_IN_RESPONSE_OF as MOAPVFMS5_6_, messagemto0_.MOAPNFMSGMT_APPLICATION_ID as MOAPNFMS6_6_, messagemto0_.MOAPDGMSGMT_CREATION_DATE as MOAPDGMS7_6_, messagemto0_.MOAPCVMSGMT_MESSAGE_BODY as MOAPCVMS8_6_, messagemto0_.MOAPVGMSGMT_BLOCK_ID as MOAPVGMS9_6_, messagemto0_.MOAPDGMSGMT_ENQUEUE_DATE as MOAPDGM10_6_
from MOAPTBMSG_M_TO_T messagemto0_
where messagemto0_.MOAPNFMSGMT_USER_ID=? and messagemto0_.MOAPNFMSGMT_APPLICATION_ID=? and (messagemto0_.MOAPVGMSGMT_BLOCK_ID is null)
order by messagemto0_.MOAPVKMSGMT_ID asc )
where rownum <= ? for update of messagemto0_.MOAPVKMSGMT_ID
---> ORA-00904: messagemto0_.MOAPVKMSGMT_ID not a valid identifier
{code}
The field in the "for update" should be MOAPVKMS1_:
{code:borderStyle=solid}
select * from (
select messagemto0_.MOAPVKMSGMT_ID as MOAPVKMS1_, messagemto0_.MOAPVUMSGMT_MESSAGE_ID as MOAPVUMS2_6_, messagemto0_.MOAPNFMSGMT_USER_ID as MOAPNFMS3_6_, messagemto0_.MOAPNKMSGMT_LICENSE_ID as MOAPNKMS4_6_, messagemto0_.MOAPVFMSGMT_IN_RESPONSE_OF as MOAPVFMS5_6_, messagemto0_.MOAPNFMSGMT_APPLICATION_ID as MOAPNFMS6_6_, messagemto0_.MOAPDGMSGMT_CREATION_DATE as MOAPDGMS7_6_, messagemto0_.MOAPCVMSGMT_MESSAGE_BODY as MOAPCVMS8_6_, messagemto0_.MOAPVGMSGMT_BLOCK_ID as MOAPVGMS9_6_, messagemto0_.MOAPDGMSGMT_ENQUEUE_DATE as MOAPDGM10_6_
from MOAPTBMSG_M_TO_T messagemto0_
where messagemto0_.MOAPNFMSGMT_USER_ID=? and messagemto0_.MOAPNFMSGMT_APPLICATION_ID=? and (messagemto0_.MOAPVGMSGMT_BLOCK_ID is null)
order by messagemto0_.MOAPVKMSGMT_ID asc )
where rownum <= ? for update of MOAPVKMS1_
{code}
Now, this query gives the following error:
ORA-02014:, cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
I think that this is because you can't put an order by in the inner query and a for update in the external query.
I re-write the query, adding the rownum condition to the inner query and eliminating the extenal query:
{code:borderStyle=solid}
select messagemto0_.MOAPVKMSGMT_ID as MOAPVKMS1_, messagemto0_.MOAPVUMSGMT_MESSAGE_ID as MOAPVUMS2_6_, messagemto0_.MOAPNFMSGMT_USER_ID as MOAPNFMS3_6_, messagemto0_.MOAPNKMSGMT_LICENSE_ID as MOAPNKMS4_6_, messagemto0_.MOAPVFMSGMT_IN_RESPONSE_OF as MOAPVFMS5_6_, messagemto0_.MOAPNFMSGMT_APPLICATION_ID as MOAPNFMS6_6_, messagemto0_.MOAPDGMSGMT_CREATION_DATE as MOAPDGMS7_6_, messagemto0_.MOAPCVMSGMT_MESSAGE_BODY as MOAPCVMS8_6_, messagemto0_.MOAPVGMSGMT_BLOCK_ID as MOAPVGMS9_6_, messagemto0_.MOAPDGMSGMT_ENQUEUE_DATE as MOAPDGM10_6_
from MOAPTBMSG_M_TO_T messagemto0_
where messagemto0_.MOAPNFMSGMT_USER_ID=? and messagemto0_.MOAPNFMSGMT_APPLICATION_ID=? and (messagemto0_.MOAPVGMSGMT_BLOCK_ID is null) and rownum <= ?
order by messagemto0_.MOAPVKMSGMT_ID asc
for update of messagemto0_.MOAPVKMSGMT_ID
{code}
this query works
|
