3 Replies Latest reply on Sep 16, 2004 3:41 AM by Gerd Streubel

    Using subqueries for EJB QL

    Gerd Streubel Newbie

      I observed that the conversion of EJB QL to SQL between database vendors does not function correctly. The following finder results in my environment in 3 objects using MySql and 2 objects using Oracle9i whereas 2 is the correct one:

      SELECT OBJECT(p) FROM Person AS p, Businesspartner AS b, IN(b.contacts) AS c
      WHERE b = ?1 AND p.contacts IS NOT EMPTY AND c NOT MEMBER OF p.contacts


      The generated SQL statements are:

      MySql
      SELECT DISTINCT t0_p.PK
      FROM PERSONS t0_p, BUSINESSPARTNERS t1_b, CONTACTS t3_c
      LEFT JOIN CONTACTS t2_p_contacts ON t0_p.PK=t2_p_contacts.FK_PERSON
      WHERE (
       (t1_b.PK=?) AND
       t2_p_contacts.PK IS NOT NULL
      ) AND (
       t1_b.PK=t3_c.FK_BUSINESSPARTNER
      )

      Oracle
      SELECT DISTINCT t0_p.PK
      FROM PERSONS t0_p, BUSINESSPARTNERS t1_b, CONTACTS t3_c
      WHERE (
       (t1_b.PK=133) AND
       EXISTS (
       SELECT t2_p_contacts.PK
       FROM CONTACTS t2_p_contacts
       WHERE t0_p.PK=t2_p_contacts.FK_PERSON
       ) AND
       NOT EXISTS (
       SELECT t2_p_contacts.PK
       FROM CONTACTS t2_p_contacts
       WHERE t0_p.PK=t2_p_contacts.FK_PERSON
       AND t2_p_contacts.PK = t3_c.PK
       )
      ) AND (
       t1_b.PK=t3_c.FK_BUSINESSPARTNER
      )
      


      Because MySql 4.1 is now able to support subqueries like the second SQL statement I want to instruct JBoss to generate this type of statement. Can someone point me to the right direction how to do that?