I am trying to create a JBOSS-QL query that need to use collection condition.
the query is :
SELECT DISTINCT OBJECT(t)
FROM ejbTask AS t, IN(t.mandatories) m, IN(t.optionals) o
WHERE ((t.owner.name LIKE ?1) OR (m.name LIKE ?1) OR (o.name LIKE ?1)) AND
((t.startDate BETWEEN ?2 AND ?3 OR t.endDate BETWEEN ?2 AND ?3) OR
(t.startDate <= ?2 AND t.endDate >= ?3))
ORDER BY t.startDateit retrieves all tasks associated with a user, no matter if the user is the owner of the task nor if he appears in the mandatory-persons list nor in the optionals-persons list.
The relation-tables are correctly set when I create a task. The problem has been tightened to the query.
I found in a book that when using "IN(bean.collection) var" in the FROM clause, then it returns values ONLY if there are one or more results in the collection (Actually the book says "Note that declaring variables will restrict your queries even if you don't use the variables")
For my stuff, the mandatory and optional collection can be empty both or one-at-a-time so it seems to be why the query returns an empty set.
My question is : how to archieve the same query but without being stop by this restiction ?
I tried :
SELECT DISTINCT OBJECT(t)
FROM ejbTask AS t, ejbPerson AS m, ejbPerson AS o
WHERE (t.owner.name LIKE ?1 OR m MEMBER OF t.mandatories OR o MEMBER OF t.optionals AND m.name LIKE ?1 AND o.name LIKE ?1) AND
((t.startDate BETWEEN ?2 AND ?3 OR t.endDate BETWEEN ?2 AND ?3) OR
(t.endDate <= ?2 AND t.endDate >= ?3))
ORDER BY t.startDate
but it fails at deploy time.
Many thanks for any tips ! Ionel
------------------------------------------------------- This SF.net email is sponsored by: Etnus, makers of TotalView, The best thread debugger on the planet. Designed with thread debugging features you've never dreamed of, try TotalView 6 free at www.etnus.com. _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
