Greetings Forum Folks,
I'm attempting to do some EJB QL on Jboss 4.0.4 compiled for ejb3 and I've run into a problem. The query I am attempting is the following:
Query query = entityManager.createQuery("FROM ProjectEstimate AS pe WHERE
((pe.subStartDate IS NOT NULL and pe.subEndDate IS NULL and :date >= pe.subStartDate)
or (pe.subEndDate IS NOT NULL and pe.subStartDate IS NULL and :date <= pe.subEndDate)
or (pe.subStartDate IS NOT NULL and pe.subEndDate IS NOT NULL and :date >= pe.subStartDate and :date <= pe.subEndDate))
ORDER BY pe.name ");
query.setParameter("date", new java.util.Date());
However, when this runs, the where clause of the sql is:
WHERE (pe.subStartDate IS NOT NULL) and (pe.subEndDate IS NULL) and (:date >= pe.subStartDate)
or (pe.subEndDate IS NOT NULL) and (pe.subStartDate IS NULL) and (:date <= pe.subEndDate)
or (pe.subStartDate IS NOT NULL) and (pe.subEndDate IS NOT NULL) and (:date >= pe.subStartDate and :date <= pe.subEndDate)
We all know that those two where clauses don't return the same data. Reading the J2EE tutorial on sun, it states the following:
A WHERE clause consists of a conditional expression, which is evaluated from left to right within a precedence level.
You can change the order of evaluation by using parentheses.
So, theoretically, it should process my query, with the parentheses intact. But it is not. What am I doing wrong? or is there another way of doing this?
Thanks,
Andy
