0 Replies Latest reply on Apr 4, 2006 1:32 PM by Ing

    Jboss 4.0 , EJB-QL and CMP-Finder-Methods, need help

    Ing Newbie

      Hello!

      Running Jboss 4.0 with Netbeans.
      Using CMP.

      I am able to fill in the 'cmp fields' and the schema is created.
      I am able to insert values in my schema ( id Long, title String, price Integer ).
      I am ablt to remove rows from my schema.

      The trouble begins when I want to extract the highest and the lowest price.

      using methods such as
      'select * from movieentbean where price =(select min(price) from movieentbean)'
      or the 'SELECT OBJECT(o) FROM MovieEnt o WHERE o.price =( SELECT MIN(ok.price) FROM MovieEnt ok)'

      the error Jboss gives me is the following

      19:26:58,162 INFO [STDOUT] org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "SELECT" at line 1, column 51.
      Was expecting one of:
      "ABS" ...
      "LENGTH" ...
      "LOCATE" ...
      "SQRT" ...
      "MOD" ...
      "(" ...
      "+" ...
      "-" ...
      <INTEGER_LITERAL> ...
      <FLOATING_POINT_LITERAL> ...
      <NUMERIC_VALUED_PARAMETER> ...
      <NUMERIC_VALUED_PATH> ...


      How am I able to get the row where the price is highest or lowest using the functions
      within the database, not wanting to fetch all my rows and doing the check myself.
      Is it not possible to have nested queries in EJB-QL ?

      I am able to ask a simple question like this 'SELECT OBJECT(emp) FROM MovieEnt emp', when having only one item in my table, and beeing able to do a 'getPrice()'
      on the object created.
      I thought that was a breakhrough and therefore did this little view
      'Create view movieminpricev as
      select * from movieentbean where price =(select min(price) from movieentbean)'


      But new to this world and not knowing how to define a new schema pointing out my VIEW in my ejb-jar.xml.

      Hopefully you can help me out, excited about J2EE and Jboss, wanting to
      move with the speed of light.

      Best regards, i