I am using mysql, my query includes some of date functions. Query working fine from commnadline.. but when i tried to execute from HQL it is throwing QUERYEXCEPTION.

my query is :
SELECT to_days(now()) - to_days(DATE_ADD(u.last_passwd_change, INTERVAL (ga.password_expiry_period - ga.password_pre_expiry_notify) DAY)) as expiryDate from user as u, global_attributes as ga;

Exception details:

[STDERR] javax.ejb.FinderException: net.sf.hibernate.QueryException: aggregate function expected before (
in SELECT [SELECT (to_days(now()) - to_days(DATE_ADD(u.lastPasswordChange, INTERVAL (ga.passwordExpiryPeriod - ga.passwordPreExpiryNotify) DAY))) from com.mlifestyle.etud.model.common.User as u, com.mlifestyle.etud.model.common.GlobalAttributes as ga WHERE u.userId = ?]
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:91)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)
at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30) .......etc...

advance thanks...

earlier advice should be appreciatable....


Prasad Kavuri.

Sorry HQL does not currently support expressions or multiple argument functions in the select clause. V2.2 is planned to introduce a new HQL parser which makes this much easier to do.

NOTE: As extra information - HQL functions must be know to the dialect you are using. Currently, very few dialects have extra functions defined in them. Check Oracle Dialect as an example.

You could use direct JDBC or the SQL query interface to do this.