0 Replies Latest reply on Mar 12, 2003 8:49 AM by Laurent Broudoux

    Operations priority during EJB-QL -> SQL transalation

    Laurent Broudoux Newbie

      Hello,

      I'm using JBoss-3.0.4 and I have a EJB-QL finder that must retrieve update objects having specific status or having no status (NULL).

      My EJB-QL declaration is the following :
      SELECT DISTINCT OBJECT(u) FROM Update AS u WHERE u.status IS NULL OR (u.status.id IN ('3', '4', '5'))

      I was expecting the following SQL translation :
      SELECT DISTINCT u.s_id FROM updates u, status s WHERE (u.s_status IS NULL) OR (s.s_id IN ('3', '4', '5') AND u.s_status=s.s_id)
      that will match my needs.

      The problem is that JBoss produces the following statement :
      SELECT DISTINCT u.s_id FROM updates u, status s WHERE (u.s_status IS NULL OR (s.s_id IN ('3', '4', '5'))) AND (u.s_status=s.s_id)
      which doesn't allow to retrieve updates with null status
      (cause when status is null where's no foreign key relation).

      Is there's a mean to reach the expected statement by forcing operations (OR) priority ? Is it a JBoss bug or must I dissociate these finder into 2 differents (1 for null, 1 for IN) ?

      Thanks a lot for your help or your ideas.

      --
      Laurent