I am trying to run through this SQL. I guess my formatting of the date is not correct. The query is not getting parsed. Could anyone please help?

TIA,
Vivek.


Hibernate version: 2.1

Mapping documents:

Code between sessionFactory.openSession() and session.close():
String hibernateQueryString = "select reportsDto.teamRoom, " +
"sum(case when reportsDto.submitDate between to_date('01/01/2005','mm/dd/yyyy') and to_date('12/31/2005','mm/dd/yyyy') then 1 else 0 end) AS reportsDto.numSubmissions " +
"from ReportsDTO as reportsDto " +
"group by reportsDto.teamRoom";
Query q = session.createQuery(hibernateQueryString);
List reportList = q.list();

Full stack trace of any exception that occurs:

[2/14/06 15:14:41:736 EST] DEBUG (ReportsDAOImpl.java:46) - SQL String: select reportsDto.teamRoom, sum(case when reportsDto.submitDate between '01-JAN-05' and '31-DEC-05' then 1 else 0 end) AS reportsDto.numSubmissions from ReportsDTO as reportsDto group by reportsDto.teamRoom
[2/14/06 15:14:41:866 EST] ERROR (ActionBase.java:64) - org.springframework.orm.hibernate.HibernateQueryException: , expected in SELECT [select reportsDto.teamRoom, sum(case when reportsDto.submitDate between '01-JAN-05' and '31-DEC-05' then 1 else 0 end) AS reportsDto.numSubmissions from net.coke.bestpractices.dto.ReportsDTO as reportsDto group by reportsDto.teamRoom]; nested exception is net.sf.hibernate.QueryException: , expected in SELECT [select reportsDto.teamRoom, sum(case when reportsDto.submitDate between '01-JAN-05' and '31-DEC-05' then 1 else 0 end) AS reportsDto.numSubmissions from net.coke.bestpractices.dto.ReportsDTO as reportsDto group by reportsDto.teamRoom]
org.springframework.orm.hibernate.HibernateQueryException: , expected in SELECT [select reportsDto.teamRoom, sum(case when reportsDto.submitDate between '01-JAN-05' and '31-DEC-05' then 1 else 0 end) AS reportsDto.numSubmissions from net.coke.bestpractices.dto.ReportsDTO as reportsDto group by reportsDto.teamRoom]; nested exception is net.sf.hibernate.QueryException: , expected in SELECT [select reportsDto.teamRoom, sum(case when reportsDto.submitDate between '01-JAN-05' and '31-DEC-05' then 1 else 0 end) AS reportsDto.numSubmissions from net.coke.bestpractices.dto.ReportsDTO as reportsDto group by reportsDto.teamRoom]
net.sf.hibernate.QueryException: , expected in SELECT [select reportsDto.teamRoom, sum(case when reportsDto.submitDate between '01-JAN-05' and '31-DEC-05' then 1 else 0 end) AS reportsDto.numSubmissions from net.coke.bestpractices.dto.ReportsDTO as reportsDto group by reportsDto.teamRoom]
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:169)
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)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:295)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1572)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
at net.coke.bestpractices.dao.ReportsDAOImpl$1.doInHibernate(ReportsDAOImpl.java:48)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:363)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:332)
at net.coke.bestpractices.dao.ReportsDAOImpl.getStatusChangeReport(ReportsDAOImpl.java:35)
at net.coke.bestpractices.bo.ReportsBOImpl.getStatusChangeReport(ReportsBOImpl.java:33)
at java.lang.reflect.Method.invoke(Native Method)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:335)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
at org.springframework.orm.hibernate.HibernateInterceptor.invoke(HibernateInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
at $Proxy199.getStatusChangeReport(Unknown Source)
at net.coke.bestpractices.admin.actions.StatusChangeReportAction.executeSpecific(StatusChangeReportAction.java:38)
at net.coke.bestpractices.shared.actions.ActionBase.execute(ActionBase.java:49)
at net.coke.bestpractices.shared.actions.DelegatingPortalStrutsActionProxy.execute(DelegatingPortalStrutsActionProxy.java:122)
at com.ibm.wps.portlets.struts.WpsRequestProcessor.processActionPerform(WpsRequestProcessor.java:248)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at com.ibm.wps.portlets.struts.WpsStrutsPortlet.processActionPerformed(WpsStrutsPortlet.java:1550)
at com.ibm.wps.portlets.struts.WpsStrutsPortlet.actionPerformed(WpsStrutsPortlet.java:1268)
at com.ibm.wps.pe.pc.legacy.SPIPortletInterceptorImpl.handleEvents(SPIPortletInterceptorImpl.java:438)
at org.apache.jetspeed.portlet.Portlet._dispatch(Portlet.java:794)
at org.apache.jetspeed.portlet.Portlet.access$100(Portlet.java:88)
at org.apache.jetspeed.portlet.Portlet$Context.callPortlet(Portlet.java:899)
at com.ibm.wps.pe.pc.legacy.cmpf.impl.PortletFilterChainImpl.doFilter(PortletFilterChainImpl.java:139)
at com.ibm.wps.portlet.filters.PortletControlledTranscodingFilter.doActionEvent(PortletControlledTranscodingFilter.java:192)
at com.ibm.wps.pe.pc.legacy.cmpf.PortletFilterAdapter.doFilter(PortletFilterAdapter.java:126)
at com.ibm.wps.pe.pc.legacy.cmpf.impl.PortletFilterChainImpl.doFilter(PortletFilterChainImpl.java:118)
at com.ibm.wps.pe.pc.legacy.cmpf.impl.PortletFilterManager.doFilter(PortletFilterManager.java:242)
at org.apache.jetspeed.portlet.Portlet.dispatch(Portlet.java:636)
at org.apache.jetspeed.portlet.Portlet.doPost(Portlet.java:516)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jetspeed.portlet.Portlet.service(Portlet.java:491)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:1020)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java(Compiled Code))
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.include(WebAppRequestDispatcher.java:251)
at com.ibm.wps.pe.pc.legacy.invoker.impl.PortletInvokerImpl.callMethod(PortletInvokerImpl.java:466)
at com.ibm.wps.pe.pc.legacy.invoker.impl.PortletInvokerImpl.action(PortletInvokerImpl.java:109)
at com.ibm.wps.pe.pc.legacy.PortletContainerImpl.callPortletMethod(PortletContainerImpl.java:1233)
at com.ibm.wps.pe.pc.legacy.EventEnvironmentImpl.includePortlet(EventEnvironmentImpl.java:191)
at com.ibm.wps.pe.pc.legacy.event.ActionEventImpl.prepare(ActionEventImpl.java:121)
at com.ibm.wps.pe.pc.legacy.event.EventQueueManager.processEventLoop(EventQueueManager.java:92)
at com.ibm.wps.pe.pc.legacy.PortletContainerImpl.performEventPhase(PortletContainerImpl.java:1566)
at com.ibm.wps.pe.pc.legacy.PortletContainerImpl.initPage(PortletContainerImpl.java:222)
at com.ibm.wps.pe.pc.PortletContainerImpl.initPage(PortletContainerImpl.java:223)
at com.ibm.wps.pe.pc.PortletContainer.initPage(PortletContainer.java:148)
at com.ibm.wps.engine.templates.themes.Home.doPreRender(Home.java:205)
at com.ibm.wps.engine.templates.themes.Default.render(Default.java:67)
at com.ibm.wps.engine.templates.ThemeTemplate.render(ThemeTemplate.java:71)
at com.ibm.wps.engine.Servlet.callPortal(Servlet.java:785)
at com.ibm.wps.engine.Servlet.doGet(Servlet.java:467)
at com.ibm.wps.engine.Servlet.doPost(Servlet.java:816)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:76)
at com.ibm.wps.mappingurl.impl.URLAnalyzer.doFilter(URLAnalyzer.java:185)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:132)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:71)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:1011)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java(Compiled Code))
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:198)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:80)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:212)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:116)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:186)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:443)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)

Name and version of the database you are using:
Oracle 9i

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Why don't you use Bind Variables for that query. I doubt that to_date exists for HQL.

Try the following: