3 Replies Latest reply on Mar 17, 2004 4:55 AM by Alexey Loubyansky

    EJB-QL to SQL translation problem

    Panagiotis Korros Newbie

      I have the following EJB-QL statement :
      SELECT OBJECT( o )
      FROM User o ,IN(o.positions) p
      WHERE ( o.organicPosition.id = ?1 OR p.id = ?2 )

      to get the users that have a specific organicPosition X (?1) or contain position Y (?2)

      this is translated to the following SQL statement:
      SELECT t0_o.id FROM tuser t0_o, tposition t2_p, tuser_position t5_o_positions_RELATION_TABLE, tposition t1_o_organicPosition WHERE (((t1_o_organicPosition.id = ? OR t2_p.id = ?) AND t0_o.position=t1_o_organicPosition.id)) AND t0_o.id=t5_o_positions_RELATION_TABLE.user_id AND t2_p.id=t5_o_positions_RELATION_TABLE.position_id ORDER BY t0_o.rnumber ASC

      This EJB-QL statement doesn't return users that don't have an organicPosition even though they contain position Y.

      I think that the following SQL statement should be generated:

      SELECT t0_o.id
      FROM tposition t2_p INNER JOIN
      tuser_position t5_o_positions_RELATION_TABLE ON t2_p.id = t5_o_positions_RELATION_TABLE.position_id RIGHT OUTER JOIN
      tuser t0_o ON t5_o_positions_RELATION_TABLE.user_id = t0_o.id LEFT OUTER JOIN
      tposition t1_o_organicPosition ON t0_o.[position] = t1_o_organicPosition.id
      WHERE (t1_o_organicPosition.id = 10) OR (t2_p.id = 10)

      Is this correct or there is something wrong with my EJB-QL statement?

      I am using JBoss 3.2.3