I'm trying to execute the following:
Code:
getSession().createQuery("select distinct b from Bid b inner join (select x.auction, x.amount, count(x) cnt from Bid x where x.status in ('CONFIRMED','WINNER') group by x.auction, x.amount order by cnt limit 1) z on z.amount = b.amount and z.auction = b.auction where b.auction = :auction and b.status in ('CONFIRMED','WINNER') order by b.received").setParameter("auction", auction).setMaxResults(1).list();
(In a nutshell, this should return the "lowest unique bid" in a sort of "reverse-auction" thing I'm working on.).
Hibernate is throwing the following exception:
Code:
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 64 [select distinct b from com.lbs.entities.Bid b inner join (select x.auction, x.amount, count(x) cnt from com.lbs.entities.Bid x where x.status in ('CONFIRMED','WINNER') group by x.auction, x.amount order by cnt limit 1) z on z.amount = b.amount and z.auction = b.auction where b.auction = :auction and b.status in ('CONFIRMED','WINNER') order by b.received]
line 1, column 64 appears to be the opening parenthesis of the first subquery. Can anyone tell me what I'm doing wrong here, or else help me refactor this into a criteria-based query?
[edit] Oh yeah, also, instead of "x.status in ('CONFIRMED','WINNER')", it should be something like "x.status in (BidStatus.CONFIRMED, BidStatus.WINNER)", which I can do with Criteria (using a @Type annotation and a custom EnumUserType class), but can't figure out how to do in HQL. Any help with that would definitely be an added bonus :-)
Login
FAQ
Search
