Hi,
I agree with Dies. The Java Persistence query language does not support
ORDER BY in a subquery.
I understand you call Query.setMaxResults in order to limit the result
of the subquery, correct? I think that does not work, because
setMaxResult applies to the entire query which is an UPDATE query in
your case. I propose to skip the ORDER BY clause in the subquery.
Regards Michael
> I need it because I use setMaxResults to get only a subset of the records.
> And this subset must always be ordered by priority so that the messages with
> the highest priority is selected first. In this way, if there are 1000
> messages currently queued, and setMaxResults is 100, I must be sure that the
> most important messages are picked up first, no matter how long low-priority
> messages have been in the queue.
>
> If you have ideas on other ways to solve this, they are most welcome!
>
> //Marius
>
>
> Dies wrote:
>
>> JPQL does not allow the use of ORDER BY in a subquery:
>>
>> subquery_from_clause ::=
>> FROM subselect_identification_variable_declaration
>> {, subselect_identification_variable_declaration}*
>>
>> Why do you need it?
>>
>>
>>
>> MariusW wrote:
>>
>>> 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
>>>
>>>
>>
>
>
--
*Michael Bouschen*
*Prokurist*
akquinet tech_at_spree GmbH
Tempelhofer Ufer 23-24, D-10963 Berlin
Bülowstr. 66, D-10783 Berlin
Fon: +49 30 235 520-33
Fax: +49 30 217 520-12
Email: michael.bouschen_at_akquinet.de
Url: www.akquinet.de <http://www.akquinet.de>
akquinet tech_at_spree GmbH, Berlin
Geschäftsführung: Prof. Dr. Christian Roth, Hendrik Saly, Martin Weber
Amtsgericht Berlin-Charlottenburg HRB 86780
USt.-Id. Nr.: DE 225 964 680