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? View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3847634#3847634 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3847634 ------------------------------------------------------- This SF.Net email is sponsored by BEA Weblogic Workshop FREE Java Enterprise J2EE developer tools! Get your free copy of BEA WebLogic Workshop 8.1 today. http://ads.osdn.com/?ad_id=5047&alloc_id=10808&op=click _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
