The problem with that query is that it will return rows where c.customer IS NULL but the parameter ?1 is not null. You only want rows where c.customer is null and ?1 is also null. The equals doesn't work, and IS NULL only tests one side of the equation. You can't apply it to the parameter to be sure both the parameter and c.customer are null at the same time.
I've worked around this by creating multiple queries to test for the various scenarios, and then aggregating the results. That's extremely inefficient, but the runtime inefficiency is not the biggest problem. The problem is that the number of queries you need is the number of optional parameters you have to the power of 2. For instance, if you have two optional parameters, then you need 4 queries to cover all possible combinantions of null and not null values. However, I'm asking this question today because I now had a case where the business key has 4 optional columns. This would require 16 queries, and I'm not going there. To get around this for now, I created a "dummy null" value, and have a lot at points to convert it back and forth between a real null. This was at least better than creating 16 EJB-QL queries and creating a lot of Java code to tie them together. View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836279#3836279 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836279 ------------------------------------------------------- This SF.Net email is sponsored by: Oracle 10g Get certified on the hottest thing ever to hit the market... Oracle 10g. Take an Oracle 10g class now, and we'll give you the exam FREE. http://ads.osdn.com/?ad_id=3149&alloc_id=8166&op=click _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
