I am using HQL createQuery method to get all the columns as to be returned in List<Object[]> array.. as I have few new fields to be generated from sum of the records.
My query looks like,

"select a.param1, a.param2, a.param3, a.param4, a.param5, sum(a.param6) as count from table_name a where a.param1 in (:param1) and a.param2=:param2 and a.date >=:date and (a.param3 in (:param3) or a.param4in (:param4)) GROUP BY a.abc ORDER BY count DESC"

query.setParameterList("param1", param1);
.
.
query.setParameter("date", date);

List<Object[]> result = query.list();



But this is not working in HQL, giving an exception :

org.hibernate.hql.ast.QuerySyntaxException: expecting OPEN, found 'DESC'
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
at sun.reflect.GeneratedMethodAccessor71.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.orm.hibernate3.HibernateTemplate$CloseSuppressingInvocationHandler.invoke(HibernateTemplate.java:1282)
at com.sun.proxy.$Proxy149.createQuery(Unknown Source)

Any help is much appreciated. Looking forward to hear from you guys. How do I get it resolved?


-Abhi