Why does this not work - any help appreciated 1

[karlomutschler]

Hi
the Query looks like this

SELECT *
FROM tablename
WHERE activ_date BETWEEN '2001/03/01' AND '2001/03/31'
AND deactive_date > '2001/05/01' ;
Result is correct !

If the WHERE-Clause is extended to read

WHERE activ_date BETWEEN '2001/03/01' AND '2001/03/31'
AND deactive_date > '2001/05/01'
OR deactive_date IS NULL ;

the result active_date returned is outside the given criteria.

TIA
Karlo Mutschler

 

[tdgulbra]

I believe what is happening is that the "AND" is being evaluated before the "OR". Which means it will return all rows with "deactive_date" being NULL. If I understand the answer set you are looking for correctly, you need to put the two "deactive_date" criteria in parentheses so they can be evaluated together:

WHERE activ_date BETWEEN '2001/03/01' AND '2001/03/31'
AND (deactive_date > '2001/05/01'
OR deactive_date IS NULL) ;

Hope this helps.