I have a nested query that throws this exception:
Caused by: Exception [TOPLINK-8025] (Oracle TopLink Essentials - 2.0 (Build
b58-rc1 (08/04/2007))): oracle.toplink.essentials.exceptions.EJBQLException
Exception Description: Syntax error parsing the query
[updateProcessingCutoff: UPDATE MobileMessage x SET x.statusmsg =
'PROCESSING',x.transporttime = :transporttime WHERE x.id IN (SELECT y.id
FROM MobileMessage y WHERE y.gateway = :gateway AND y.statusmsg IN (
'PENDING','RETRY','SCHEDULED') AND ((y.timetosend IS NULL) OR (y.timetosend
< :cutoff )) ORDER BY y.priority)], line 1, column 273: unexpected token
[ORDER].
Internal Exception: line 1:273: unexpected token: ORDER
In a more readable format, the EJB QL looks like this:
UPDATE MobileMessage x
SET x.statusmsg = 'PROCESSING',x.transporttime = :transporttime
WHERE x.id IN (
SELECT y.id FROM MobileMessage y
WHERE y.gateway = :gateway
AND y.statusmsg IN ( 'PENDING','RETRY','SCHEDULED')
AND ((y.timetosend IS NULL) OR (y.timetosend < :cutoff ))
ORDER BY y.priority
)
If I translate it to standard SQL and execute it, it works fine:
UPDATE bb_core.bb_message
SET statusmsg = 'PROCESSING'
WHERE id IN (
SELECT id FROM bb_core.bb_message
WHERE gateway = 1
AND statusmsg IN ( 'PENDING','RETRY','SCHEDULED')
AND ((timetosend IS NULL) OR (timetosend < now() ))
ORDER BY priority
)
Any tips would be greatly appreciated!
// Marius
--
View this message in context: http://www.nabble.com/Unexpected-token%3A-ORDER-tp16582059p16582059.html
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.