Hi!
I'm having trouble using the following hql query:

SELECT
viz.cliId, COUNT(*), SUM(TIME_TO_SEC(viz.vizDataOut))
FROM
com.sm.sfa.mapping.Sfa_vizite as viz
WHERE
viz.vizMotiv='0' and
viz.principal=:agPrincipal and
viz.vizDataIn>=:strDataIn and
viz.vizDataOut<=:strDataOut
GROUP BY
viz.cliId

I've pasted bellow the exception I get when I try to run it. I've deleted the SUM(...) part and I didn't get that exception. Do you know what's the problem?

Sorry if any similar questions already exist on this forum... I've searched but I didn't find anything about this.

Thank you!



Hibernate version:

Full stack trace of any exception that occurs:
2006-10-24 14:17:54 StandardContext[/servlets-examples]SessionListener: contextDestroyed()
2006-10-24 14:17:54 StandardContext[/servlets-examples]ContextListener: contextDestroyed()
2006-10-24 14:17:54 StandardContext[/jsp-examples]SessionListener: contextDestroyed()
2006-10-24 14:17:54 StandardContext[/jsp-examples]ContextListener: contextDestroyed()
2006-10-24 14:17:57 StandardContext[/Festino]Closing root WebApplicationContext
2006-10-24 14:18:03 StandardContext[/balancer]org.apache.webapp.balancer.BalancerFilter: init(): ruleChain: [org.apache.webapp.balancer.RuleChain: [org.apache.webapp.balancer.rules.URLStringMatchRule: Target string: News / Redirect URL: http://www.cnn.com], [org.apache.webapp.balancer.rules.RequestParameterRule: Target param name: paramName / Target param value: paramValue / Redirect URL: http://www.yahoo.com], [org.apache.webapp.balancer.rules.AcceptEverythingRule: Redirect URL: http://jakarta.apache.org]]
2006-10-24 14:18:05 StandardContext[/Festino]Loading root WebApplicationContext
2006-10-24 14:18:17 StandardContext[/jsp-examples]ContextListener: contextInitialized()
2006-10-24 14:18:17 StandardContext[/jsp-examples]SessionListener: contextInitialized()
2006-10-24 14:18:17 StandardContext[/servlets-examples]ContextListener: contextInitialized()
2006-10-24 14:18:17 StandardContext[/servlets-examples]SessionListener: contextInitialized()
2006-10-24 14:19:56 StandardWrapperValve[action]: Servlet.service() for servlet action threw exception
org.springframework.orm.hibernate.HibernateQueryException: ( expected before ) in select [

select
viz.cliId, count(*), SUM(TIME_TO_SEC(viz.vizDataOut))
from
com.sm.sfa.mapping.Sfa_vizite as viz
where
viz.vizMotiv='0' and
viz.principal=:agPrincipal and
viz.vizDataIn>=:strDataIn and
viz.vizDataOut<=:strDataOut
GROUP by
viz.cliId

]; nested exception is net.sf.hibernate.QueryException: ( expected before ) in select [

select
viz.cliId, count(*), SUM(TIME_TO_SEC(viz.vizDataOut))
from
com.sm.sfa.mapping.Sfa_vizite as viz
where
viz.vizMotiv='0' and
viz.principal=:agPrincipal and
viz.vizDataIn>=:strDataIn and
viz.vizDataOut<=:strDataOut
GROUP by
viz.cliId

]
net.sf.hibernate.QueryException: ( expected before ) in select [

select
viz.cliId, count(*), SUM(TIME_TO_SEC(viz.vizDataOut))
from
com.sm.sfa.mapping.Sfa_vizite as viz
where
viz.vizMotiv='0' and
viz.principal=:agPrincipal and
viz.vizDataIn>=:strDataIn and
viz.vizDataOut<=:strDataOut
GROUP by
viz.cliId

]
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:110)
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:294)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1562)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1533)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at org.springframework.orm.hibernate.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:494)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:150)
at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:170)
at org.springframework.orm.hibernate.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:487)
at com.sm.sfa.dao.ViziteDaoImpl.getInfoViziteCuActivitate(ViziteDaoImpl.java:149)
at com.sm.sfa.service.CentralizatorComenziServiceImpl.getDateRapActiv(CentralizatorComenziServiceImpl.java:5802)
at com.sm.sfa.presentation.controller.ppc.RaportDeActivitateAction.execute(RaportDeActivitateAction.java:153)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.atlassian.util.profiling.filters.ProfilingFilter.doFilter(ProfilingFilter.java:148)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:793)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:702)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:571)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:644)
at java.lang.Thread.run(Thread.java:534)


Name and version of the database you are using:
MySQL 4.1

The generated SQL (show_sql=true):
no SQL generated

The available SQL functions are generally defined in the appropriate dialect class. Are you using the correct dialect for the database? You can register new sql functions by subclassing the dialect OR using addSqlFunction() against the configuration prior to building the SessionFactory.

Curtis ...

The problem was the TIME_TO_SEC function... Considering the error message I got first time, I assumed the problem was that you can't open a new paranthesis inside a pair of paranthesis (the imbricated function). I modified the query and I got the exception below. So you were right...

javax.servlet.ServletException: undefined alias: TIME_TO_SEC [

select
viz.cliId, TIME_TO_SEC(viz.vizDataIn)
from
com.sm.sfa.mapping.Sfa_vizite as viz
where
viz.vizMotiv='0' and
viz.principal=:agPrincipal and
viz.vizDataIn>=:strDataIn and
viz.vizDataOut<=:strDataOut


]; nested exception is net.sf.hibernate.QueryException: undefined alias: TIME_TO_SEC [

select
viz.cliId, TIME_TO_SEC(viz.vizDataIn)
from
com.sm.sfa.mapping.Sfa_vizite as viz
where
viz.vizMotiv='0' and
viz.principal=:agPrincipal and
viz.vizDataIn>=:strDataIn and
viz.vizDataOut<=:strDataOut


]
...........

I'm using hibernate2 and my dialect property is set to net.sf.hibernate.dialect.MySQLDialect. I've tried extending a Dialect class and adding sql function via Dialect.registerFunction() but my query still doesn't work. Could you give me a snippet or a link to some documentation about extending dialects in hibernate 2?
Thanx anyway!

Did you look at the source code for Hibernate 2? You should be able to model your subclass on what is already in the H2 source -- the dialects are all done that way: i.e. DB2400Dialect extends DB2Dialect extends Dialect. After you have created your subclass, you need to change the hibernate.properties file to point to your "new" dialect class.

Curtis ...

thanks dude ;)