0 Replies Latest reply on Nov 5, 2003 8:19 AM by Alberto Otero Garcia

    Date in subquery, please, help!

    Alberto Otero Garcia Newbie

      Hello everybody,

      We'd like to get an element from a table that has got the MAX date with the next, but it does not deply correctly, can you help us?:

      Jboss: 3.2.2

      RDBMS: PostgreSQL

      Query:

      SELECT OBJECT(p1)
      FROM VehicleEntity AS v1, IN (v1.OBU.positions) AS p1
      WHERE v1 = ?1 AND
      p1.date = (SELECT MAX(p2.date) FROM VehicleEntity AS v2, IN (v2.OBU.positions) AS p2 WHERE v2 = ?1)

      Deployment error:

      2003-11-05 11:30:31,375 ERROR [org.jboss.ejb.EntityContainer] Starting failed
      org.jboss.deployment.DeploymentException: Error compiling EJB-QL statement 'SELECT OBJECT(p1) FROM VehicleEntity AS v1, IN (v1.OBU.positions) AS p1 WHERE v1 = ?1 AND p1.date = (SELECT MAX(p2.date) FROM VehicleEntity AS v2, IN (v2.OBU.positions) AS p2 WHERE v2 = ?1)'; - nested throwable: (org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "(" at line 1, column 101.
      Was expecting one of:
      <DATETIME_VALUED_PARAMETER> ...
      <DATETIME_VALUED_PATH> ...
      )

      Thanks for your answer.