CODEWITHSUNDEEP
sqlt-sqldatenullwhere-clause
Robert Niestroj
Robert Niestroj

Reputation: 16141

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'.

Upvotes: 2

Views: 12410

Answers (4)

Shebas
Shebas

Reputation: 1

The correct ANSWER is:

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

Upvotes: 0

podiluska
podiluska

Reputation: 51504

where
date1 < '20120601'
    and
coalesce(date2, date3, '20000101') < '20120601'

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460208

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')

Upvotes: 8

S&#233;bastien Nussbaumer
S&#233;bastien Nussbaumer

Reputation: 6472

What about this ?

SELECT *
FROM
    docs
WHERE date1 < '20120601'
  AND ISNULL(date2, '20120601') < '20120601'
  AND ISNULL(date3, '20120601') < '20120601'

Upvotes: 0

Related Questions