1

Im getting following error while running the query.

org.hibernate.hql.ast.QuerySyntaxException: expecting CLOSE, found 'LIMIT' near line 1, column 194 [from com.claystone.db.Gpsdata where id.mobileunitid = '2090818044' and gpsdate in (select id.gpsdate from com.claystone.db.Gpsdata where id.mobileunitid = '2090818044' ORDER BY id.gpsdate DESC LIMIT 1 )  and gpsstatus='true']

This is my Query.Please give the suggession what is the mistake in this query?

data=session.createQuery[from com.claystone.db.Gpsdata where id.mobileunitid = '2090818044' and gpsdate in (select id.gpsdate from com.claystone.db.Gpsdata where id.mobileunitid = '2090818044' ORDER BY id.gpsdate DESC LIMIT 1 )  and gpsstatus='true']
Andreas Dolk
  • 113,398
  • 19
  • 180
  • 268
lakshmi
  • 4,539
  • 17
  • 47
  • 55
  • I believe HQL does not support the LIMIT keyword. If you cannot rewrite the query you could use a native query... Not the most portable option. :(. I'm curious about other answers though. – Bogdan Jun 08 '10 at 07:41
  • @Bogdan - which leads to a related question: http://stackoverflow.com/questions/1239723/how-do-you-do-a-limit-query-in-hql – Andreas Dolk Jun 08 '10 at 07:44

2 Answers2

4

why are you using the subquery? just do it like this :

data=session.createQuery[from com.claystone.db.Gpsdata where id.mobileunitid = '2090818044' and gpsstatus='true' ORDER BY id.gpsdate DESC LIMIT 1]

you might need to take the LIMIT 1 off the end and use .setMaxResults(1) on the query.

chris
  • 9,745
  • 1
  • 27
  • 27
  • I believe he wants to limit the subquery result to 1 not the whole result set :). I'd be interested too to know how is done in JPA – Bogdan Jun 08 '10 at 07:37
  • What about when you want to use a subquery select inside of your main select statement. It needs to return only 1 item. – lakshmi Jun 08 '10 at 07:47
  • but the subquery seems to join on the same table/object - so surely you can do it with just a single query/orderby/limit as i've suggested? – chris Jun 08 '10 at 08:38
0

On the other hand , If you use createQuery() instead of CreateSQLQuery() same error can be seen. "expecting CLOSE, found 'LIMIT'" or "expecting CLOSE, found 'NULL'

maskapsiz
  • 244
  • 5
  • 23