Thanks so much for the response. I'm obviously still getting used to all of this. I tried your suggestion, but there still seems to be a problem with the syntax. The query I'm using is as you suggested with comparing entity instances:
@NamedQuery(name = "findObjsByElem", query = "SELECT w FROM MyObj w LEFT JOIN w.elem3 x WHERE (w.elem1 = :elem) OR (w.elem2 = :elem) OR ((x IS NOT NULL) AND (x = :elem))")
But the error I get is:
Caused by: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build b09d-\
fcs (12/06/2007))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have a\
n error in your SQL syntax; check the manual that corresponds to your MySQL server \
version for the right syntax to use near '* null) AND (23 = t1.ITEMID)))' at line 1
Error Code: 1064
Call: SELECT t0.ITEMID, t0.NAME, t0.ELEM1_ITEMID, t0.ELEM2_ITEMID, t0.ELEM3_ITEMID FROM MyObj t0 LEFT OUTER JOIN ElemObj t1 ON (t1.ITEMID = t0.ELEM3_ITEMID) WHERE (((t0.ELEM1_ITEMID = ?) OR (t0.ELEM2_ITEMID = ?)) OR (NOT (t0.ELEM3_ITEMID =* ?) AND (? = t1.ITEMID)))
bind => [23, 23, null, 23]
I just took a guess at this and changed the query to:
@NamedQuery(name = "findObjsByElem", query = "SELECT w FROM MyObj w LEFT JOIN w.elem3 x WHERE (w.elem1 = :elem) OR (w.elem2 = :elem) OR (x = :elem)")
From a short test, this seems to be working, though I'm out of time right now and will have to test it more later.
Thanks so much to both of you for your help!
Renee
[Message sent by forum member 'drrevis' (drrevis)]
http://forums.java.net/jive/thread.jspa?messageID=286256