persistence@glassfish.java.net

Unexpected token: ORDER

From: MariusW <java_at_bluebricks.no>
Date: Wed, 9 Apr 2008 01:32:40 -0700 (PDT)

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.