Robert Niestroj Robert Niestroj - 2 months ago 6
SQL Question

WHERE with CASE and null checking

i have a problem with a CASE statement in a WHERE clause. In my query i want to get rows which are before a date. The date is check on 3 columns.

date1
is NOT NULL so i dont need there a NULL check, but dates
date2
and
date3
can be NULL so i want to check for nulls and accordingly check the date condition. I tried two approaches but cant figure out how to do it right. Can you help?

Query 1:

SELECT *
FROM
docs
WHERE
date1 < '20120601'
AND CASE
WHEN date2 IS NOT NULL AND date3 IS NOT NULL THEN date2 < '20120601'
WHEN date2 IS NOT NULL AND date3 IS NULL THEN date2 < '20120601'
WHEN date2 IS NULL AND date3 IS NOT NULL THEN date3 < '20120601'
END


This gives and error in the first when:
Incorrect syntax near '<'.


I modified my query to this:

SELECT *
FROM
docs
WHERE
date1 < '20120601'
AND CASE
WHEN date2 IS NOT NULL AND date3 IS NOT NULL AND date2 < '20120601' THEN TRUE
WHEN date2 IS NOT NULL AND date3 IS NULL AND date2 < '20120601' THEN TRUE
WHEN date2 IS NULL AND date3 IS NOT NULL AND date3 < '20120601' THEN TRUE
END


and got another error:
An expression of non-boolean type specified in a context where a condition is expected, near 'END'.

Answer

This should work:

SELECT *
FROM
    docs
WHERE (date1 < '20120601')
OR    (date2 IS NOT NULL AND date2 < '201205601')
OR    (date2 IS NULL AND date3 IS NOT NULL AND date3 < '20120601')
Comments