3 Replies Latest reply on Sep 9, 2002 3:51 AM by Volodymyr Shram

    EJB-QL problem. max() statement.

    Volodymyr Shram Newbie

      Hi.

      I have a problem with ejb-ql statement.

      I have entity-bean with finder findByShiftIdAndDate that consists:
      select OBJECT(oshcd) from OsdShiftHourCorrection oshcd where oshcd.shiftIdFK = ?1 and (oshcd.year = (select max(oshcdy.year) from OsdShiftHourCorrection oshcdy where oshcdy.year<=?2))

      When I deploy jar, I got next application error message:

      2002-09-08 12:51:26,221 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.OsdShiftHourCorrection.findByShiftIdAndDate] EJB-QL: select OBJECT(oshcd) from OsdShiftHourCorrection oshcd where oshcd.shiftIdFK = ?1 and (oshcd.year = (select max(oshcdy.year) from OsdShiftHourCorrection oshcdy where oshcdy.year<=?2))
      2002-09-08 12:51:26,251 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.parentTraceEnabled=true
      2002-09-08 12:51:26,251 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.nestedTraceEnabled=true
      2002-09-08 12:51:26,251 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.detectDuplicateNesting=true
      2002-09-08 12:51:26,261 ERROR [org.jboss.ejb.EjbModule] Starting failed
      org.jboss.deployment.DeploymentException: Error compiling ejbql; - nested throwable: (org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "= ( select" at line 1, column 99.
      Was expecting one of:
      ")" ...
      "+" ...
      "-" ...
      "*" ...
      "/" ...
      "=" "+" ...
      "=" "-" ...
      "=" <NUMERIC_VALUED_PATH> ...
      "=" <INTEGER_LITERAL> ...
      "=" <FLOATING_POINT_LITERAL> ...
      "=" "(" "+" ...
      "=" "(" "-" ...
      "=" "(" <NUMERIC_VALUED_PATH> ...
      "=" "(" <INTEGER_LITERAL> ...
      "=" "(" <FLOATING_POINT_LITERAL> ...
      "=" "(" "(" ...
      "=" "(" <NUMERIC_VALUED_PARAMETER> ...
      "=" "(" "LENGTH" ...
      "=" "(" "LOCATE" ...
      "=" "(" "ABS" ...
      "=" "(" "SQRT" ...
      "=" <NUMERIC_VALUED_PARAMETER> ...
      "=" "LENGTH" ...
      "=" "LOCATE" ...
      "=" "ABS" ...
      "=" "SQRT" ...
      "NOT" ...
      "BETWEEN" ...
      )
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.(JDBCEJBQLQuery.java:46)
      ...

      Does that means I can't use MAX() oracle function in my ejb-ql statements?

      I've been tried to set

      <function-mapping>
      <function-name>max</function-name>
      <function-sql>max(?1)</function-sql>
      </function-mapping>


      in my standardjbosscmp-jdbc.xml file

      but a result is the same: error message still exists.

      Would you so kind to clear up me what i have to do to solve my problem?

      Thanks a lot for advice.

      Regards, Scar

        • 1. Re: EJB-QL problem. max() statement.
          Péter Miklós Newbie

          hi,

          In the specification of EJB 2.0 you can read the followings:

          EJB QL includes the following built-in functions

          String Functions:
          CONCAT(String, String) returns a String
          SUBSTRING(String, start, length) returns a String
          LOCATE(String, String [, start]) returns an int
          LENGTH(String) returns an int

          Note that start and length designate the positions in a string defined by an int.

          Arithmetic Functions:
          ABS(number) returns a number (int, float, or double)
          SQRT(double) returns a double

          So, I'm afraid EJB-QL doest not support MAX, but if it does, I'm interested in the solution as well.

          regards
          -peter

          • 2. Re: EJB-QL problem. max() statement.
            Péter Miklós Newbie

            well, the real problem might be that EJB 2.0 says:

            select_clause ::= SELECT [DISTINCT ] { single_valued_path_expression |OBJECT (identification_variable)}
            ---

            And I found the following in the EJB 2.1 Proposed Final Draft:

            select_clause ::=SELECT [DISTINCT ] {select_expression |OBJECT (identification_variable)}

            select_expression ::= single_valued_path_expression | aggregate_select_expression

            aggregate_select_expression ::= {AVG | MAX | MIN | SUM | COUNT }( [DISTINCT ] cmp_path_expression) | COUNT ( [DISTINCT ] identification_variable | single_valued_cmr_path_expression)
            ---

            So, aggregate expressions, such as MAX, are definied in the select clause in EJB 2.1. As I know, JBoss supports EJB 2.0 :-(

            • 3. Re: EJB-QL problem. max() statement.
              Volodymyr Shram Newbie

              Thanx for your answer mipe -- one is very helpful.

              I am disappointed in EJB2.0 really -- max() function is not very difficult to support I think :( Of course, there are another ways to solve my problem and to get the same result --just search in the query-without-max() result and fish data from result collection.

              Thanks again for your tips mipe.

              Regards, Scar.