[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Aggregate Functions in ORDER BY

Hi Derek,

The syntax that includes the "AS" in the select clause is only legal in JPA 2.0. To get this to work, please try our latest 2.0 milestone.

-Tom

Derek Knapp wrote:
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)



------------------------------------------------------------------------

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users