| Re: [eclipselink-users] Aggregate Functions in ORDER BY |
|
thanks for the reply Tom. I tried this return em.createQuery("SELECT MAX(a.date) AS maxdate, c " + "FROM Activity a INNER JOIN a.cookieid cookie " + "INNER JOIN cookie.contactidCollection c " + "INNER JOIN c.listidCollection l " + "WHERE l.userid = :user " + "AND a.companyid = :company " + "AND a.date BETWEEN :start AND :end " + "GROUP BY c " + "ORDER BY maxdate DESC") .setParameter("user", user) .setParameter("company", user.getCompanyid()) .setParameter("start", start) .setParameter("end", end) .getResultList(); but get the following exception Caused by: Exception [EclipseLink-8024] (Eclipse Persistence Services - 1.0.2 (Build 20081024)): org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing the query [SELECT MAX(a.date) AS maxdate, c FROM Activity a INNER JOIN a.cookieid cookie INNER JOIN cookie.contactidCollection c INNER JOIN c.listidCollection l WHERE l.userid = :user AND a.companyid = :company AND a.date BETWEEN :start AND :end GROUP BY c ORDER BY maxdate DESC], line 1, column 19: syntax error at [AS]. Internal Exception: MismatchedTokenException(8!=27) at org.eclipse.persistence.exceptions.JPQLException.syntaxErrorAt(JPQLException.java:355) at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.handleRecognitionException(JPQLParser.java:304) at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.addError(JPQLParser.java:245) at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.reportError(JPQLParser.java:362) at org.eclipse.persistence.internal.libraries.antlr.runtime.BaseRecognizer.recoverFromMismatchedElement(Unknown Source) at org.eclipse.persistence.internal.libraries.antlr.runtime.BaseRecognizer.recoverFromMismatchedToken(Unknown Source) at org.eclipse.persistence.internal.libraries.antlr.runtime.BaseRecognizer.mismatch(Unknown Source) at org.eclipse.persistence.internal.libraries.antlr.runtime.BaseRecognizer.match(Unknown Source) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.fromClause(JPQLParser.java:1880) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.selectStatement(JPQLParser.java:341) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.document(JPQLParser.java:261) at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.parse(JPQLParser.java:133) at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.buildParseTree(JPQLParser.java:94) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:203) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:170) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:134) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:95) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:80) at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:740) ... 68 more Caused by: MismatchedTokenException(8!=27) ... 81 more StandardWrapperValve[Blocking Servlet]: PWC1406: Servlet.service() for servlet Blocking Servlet threw exception MismatchedTokenException(8!=27) at org.eclipse.persistence.internal.libraries.antlr.runtime.BaseRecognizer.mismatch(Unknown Source) at org.eclipse.persistence.internal.libraries.antlr.runtime.BaseRecognizer.match(Unknown Source) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.fromClause(JPQLParser.java:1880) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.selectStatement(JPQLParser.java:341) at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.document(JPQLParser.java:261) at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.parse(JPQLParser.java:133) at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.buildParseTree(JPQLParser.java:94) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:203) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:170) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:134) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:95) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:80) at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:740) at com.sun.enterprise.util.EntityManagerWrapper.createQuery(EntityManagerWrapper.java:349) at com.iTrac.Beans.ActivityFacade.getWebHotlist(ActivityFacade.java:182) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1067) at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:176) at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2895) at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3986) at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:197) at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:83) at $Proxy827.getWebHotlist(Unknown Source) at itrac.report.webhotlist.load(webhotlist.java:78) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.sun.el.parser.AstValue.invoke(AstValue.java:187) at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:297) at com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68) at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:77) at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:91) at javax.faces.component.UICommand.broadcast(UICommand.java:383) at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:447) at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:752) at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:97) at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:251) at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:117) at com.icesoft.faces.webapp.http.core.JsfLifecycleExecutor.apply(JsfLifecycleExecutor.java:18) at com.icesoft.faces.webapp.http.core.ReceiveSendUpdates.renderCycle(ReceiveSendUpdates.java:132) at com.icesoft.faces.webapp.http.core.ReceiveSendUpdates.service(ReceiveSendUpdates.java:74) at com.icesoft.faces.webapp.http.core.RequestVerifier.service(RequestVerifier.java:31) at com.icesoft.faces.webapp.http.common.standard.PathDispatcherServer.service(PathDispatcherServer.java:24) at com.icesoft.faces.webapp.http.servlet.BasicAdaptingServlet.service(BasicAdaptingServlet.java:16) at com.icesoft.faces.webapp.http.servlet.PathDispatcher.service(PathDispatcher.java:23) at com.icesoft.faces.webapp.http.servlet.SessionDispatcher.service(SessionDispatcher.java:53) at com.icesoft.faces.webapp.http.servlet.SessionVerifier.service(SessionVerifier.java:26) at com.icesoft.faces.webapp.http.servlet.PathDispatcher.service(PathDispatcher.java:23) at com.icesoft.faces.webapp.http.servlet.MainServlet.service(MainServlet.java:131) at javax.servlet.http.HttpServlet.service(HttpServlet.java:831) at com.icesoft.faces.webapp.xmlhttp.BlockingServlet.service(BlockingServlet.java:56) at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:411) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:290) at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:271) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:202) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577) at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:94) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:206) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:571) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1080) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:150) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:571) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1080) at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:272) at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.invokeAdapter(DefaultProcessorTask.java:637) at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.doProcess(DefaultProcessorTask.java:568) at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.process(DefaultProcessorTask.java:813) at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.executeProcessorTask(DefaultReadTask.java:341) at com.sun.enterprise.web.connector.grizzly.ssl.SSLReadTask.process(SSLReadTask.java:440) at com.sun.enterprise.web.connector.grizzly.ssl.SSLReadTask.doTask(SSLReadTask.java:228) at com.sun.enterprise.web.connector.grizzly.TaskBase.run(TaskBase.java:265) at com.sun.enterprise.web.connector.grizzly.ssl.SSLWorkerThread.run(SSLWorkerThread.java:106) |