0 Replies Latest reply: Aug 9, 2010 10:47 AM by 843833 RSS

    JPA Problem using alias for columns in a query

    843833
      Hello, I am having some problems with a query that I am trying to use in my JEE project. This query doesnt return an entity but a group of values. I created a class representing the result and a query with the jpa constructor expression but it is not working.
      The problem is that in the query I added some alias to the results, and when I try to run the project it says that it cannot parse the query.

      My Query:
      Query query = em.createQuery("SELECT NEW vo.VOOperacionesAgrupadas (o.nemotecnico as nemotecnico, o.esCompra as esCompra, i.equivUltimo as equivUltimo, sum(o.saldo) as saldo, sum(o.utilidad) as utilidad, sum(o.tasaCompraVenta)/count(o.nemotecnico) as promedioTasaCompra, (i.equivUltimo-sum(o.tasaCompraVenta)/count(o.nemotecnico))*100 as puntosBasicos) FROM Operaciones o, Instrumentos i WHERE o.idUsuario = :idUsuario AND o.nemotecnico = i.nemotecnico AND o.estaCerrada = 'false' Group by o.nemotecnico, o.esCompra, i.equivUltimo"); 
      When I use that the server returns :

      Exception Description: Syntax error parsing the query [SELECT NEW vo.VOOperacionesAgrupadas (o.nemotecnico as nemotecnico, o.esCompra as esCompra, i.equivUltimo as equivUltimo, sum(o.saldo) as saldo, sum(o.utilidad) as utilidad, sum(o.tasaCompraVenta)/count(o.nemotecnico) as promedioTasaCompra, (i.equivUltimo-sum(o.tasaCompraVenta)/count(o.nemotecnico))*100 as puntosBasicos) FROM Operaciones o, Instrumentos i WHERE o.idUsuario = :idUsuario AND o.nemotecnico = i.nemotecnico AND o.estaCerrada = 'false' Group by o.nemotecnico, o.esCompra, i.equivUltimo], line 1, column 53: syntax error at [as].
      Internal Exception: MismatchedTokenException(8!=82)
      GRAVE: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
      Exception Description: Syntax error parsing the query [SELECT NEW vo.VOOperacionesAgrupadas (o.nemotecnico as nemotecnico, o.esCompra as esCompra, i.equivUltimo as equivUltimo, sum(o.saldo) as saldo, sum(o.utilidad) as utilidad, sum(o.tasaCompraVenta)/count(o.nemotecnico) as promedioTasaCompra, (i.equivUltimo-sum(o.tasaCompraVenta)/count(o.nemotecnico))*100 as puntosBasicos) FROM Operaciones o, Instrumentos i WHERE o.idUsuario = :idUsuario AND o.nemotecnico = i.nemotecnico AND o.estaCerrada = 'false' Group by o.nemotecnico, o.esCompra, i.equivUltimo], line 1, column 53: syntax error at [as].
      Internal Exception: MismatchedTokenException(8!=82)
      ...
      ...
      at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:57)
      at com.sun.grizzly.ContextTask.run(ContextTask.java:69)
      at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:330)
      at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:309)
      at java.lang.Thread.run(Thread.java:619)
      Caused by: Exception [EclipseLink-8024] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.JPQLException
      Exception Description: Syntax error parsing the query [SELECT NEW vo.VOOperacionesAgrupadas (o.nemotecnico as nemotecnico, o.esCompra as esCompra, i.equivUltimo as equivUltimo, sum(o.saldo) as saldo, sum(o.utilidad) as utilidad, sum(o.tasaCompraVenta)/count(o.nemotecnico) as promedioTasaCompra, (i.equivUltimo-sum(o.tasaCompraVenta)/count(o.nemotecnico))*100 as puntosBasicos) FROM Operaciones o, Instrumentos i WHERE o.idUsuario = :idUsuario AND o.nemotecnico = i.nemotecnico AND o.estaCerrada = 'false' Group by o.nemotecnico, o.esCompra, i.equivUltimo], line 1, column 53: syntax error at [as].
      Internal Exception: MismatchedTokenException(8!=82)
      at org.eclipse.persistence.exceptions.JPQLException.syntaxErrorAt(JPQLException.java:362)
      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.constructorExpression(JPQLParser.java:2635)
      at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.selectExpression(JPQLParser.java:2045)
      at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.selectItem(JPQLParser.java:1351)
      at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.selectClause(JPQLParser.java:1266)
      at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.selectStatement(JPQLParser.java:352)
      at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.document(JPQLParser.java:276)
      at org.eclipse.persist
      GRAVE: ence.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:198)
      at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:173)
      at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:125)
      at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:109)
      at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1326)
      ... 59 more
      Caused by: MismatchedTokenException(8!=82)
      ... 74 more


      What can I do?? I have been stuck in this problem for 2 weeks :s I have tried almost everything..

      Thanks in advance for your help!