Hello guys
I am running into a problem, basically I need to translate the follow SQL (to HQL)
Code:
SELECT count(*), status, date(eventDate)
from call_detail
where clientid = 32 and date(eventDate)
GROUP BY status, date(eventDate)
to HQL (which i am doing as follows)
Code:
SELECT count(*), status, date(eventDate)
from CallDetail
where clientId = 32 and date(eventDate)
GROUP BY status, date(eventDate)
This gives me the following exception
Code:
Exception in thread "main" java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'date' {originalText=date}
\-[EXPR_LIST] SqlNode: 'exprList'
\-[DOT] DotNode: 'calldetail0_.CALLDATE' {propertyName=callDate,dereferenceType=4,propertyPath=callDate,path={synthetic-alias}.callDate,tableAlias=calldetail0_,className=net.voicelog.callRecording.entities.CallDetail,classAlias=null}
+-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}
\-[IDENT] IdentNode: 'callDate' {originalText=callDate}
at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:145)
at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:705)
at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:529)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:645)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
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 net.voicelog.callRecording.ClientDriver.hqlGroupByAudioStatusDate(ClientDriver.java:34)
at net.voicelog.callRecording.ClientDriver.main(ClientDriver.java:22)
but if I run the same HQL without the sql date() function in the select clause the HQL gives no exception but thats not the result I want.
Now the reason I am using the date() function is that my date column is database table is of type "timestamp" which means the format is like "dd/mm/yyyy hh:mm:ss" and what I have to do in my query is to group by the date part i.e. (dd/mm/yyyy) and the status. OR to get counts of different status by date (only no time)
Any idea how can I achieve this using HQL ?
What I am using is
-Hibernate 3.2.2ga
-EnterpriseDB
-JDK 1.5