Problem using nested avg(..) aggregate function in hibernate hql

Question

I am using HQL to get data through DAO class but it throws as error stated below :

ERROR org.hibernate.hql.PARSER  - <AST>:0:0: unexpected AST node: query

Below is my Hql Query :

select new com.shaikh.dto.UserResult ( user.userSurName, avg((select avg(v1.age)  from com.shaikh.dto.UserResult v1  where v1.joinDate between to_date(:dayFirst, 'dd-Mon-yy') and to_date(:dayLast, 'dd-Mon-yy') )),  avg(user.age) ) from com.shaikh.dto.User user group by user.userSurName";

String [] paramNames = { "dayFirst", "dayLast" };
Object [] values = { firstDay,lastDay};
return getHibernateTemplate().findByNamedParam(queryString, paramNames, values);

I am using Oracle 11g as Database.

If I replace nested avg() function with simple avg(user.age) for testing it works fine so it seems that class mappings are working fine. Though I am getting error as above which informs that hql query is not proper. I am not sure how can I fix it. Thanks in Advance :)

Thanks & Regards,
Shariq

Answer 1

You are combining HQL with SQL

to_date(:dayFirst, 'dd-Mon-yy') and to_date(:dayLast, 'dd-Mon-yy')

pass those parameters as date arguments.

Answer 2

You don't need to use avg twice ... here is correct HQL:

select new com.shaikh.dto.UserResult ( user.userSurName, (select avg(v1.age) from com.shaikh.dto.UserResult v1 where v1.joinDate between :dayFirst and :dayLast), avg(user.age) ) from com.shaikh.dto.User user group by user.userSurName";

And pass dayFirst and dayLast as java.util.Date