[SOLVED] Can anyone tell me why my case statement return null(good)

Can anyone tell me why my case statement return null(good)

I have a query return null(good) with case statements,but when use where statement for each case, have records in output .

select a,b,sysdate ,      
 CASE 
     WHEN a IS NULL  AND SYSDATE > b THEN 'O'
     WHEN a IS NULL AND SYSDATE <= b THEN 'W'
     WHEN a > b THEN 'Fail'
     WHEN a <= b THEN 'Pass'
   else 'good'
 END  as result
 from mytable

a | b | sysdate | result

null | null| 10-JUL-19 |good

null | null| 10-JUL-19 |good

null | null| 10-JUL-19 |good

null | null| 10-JUL-19 |good

I have ouput when I execute this query(or other two)

select a,b,sysdate  from mytable 
where a > b

output(where query):

a | b | sysdate

10-APR-15| 06-APR-15| 10-JUL-19|

06-APR-15| 06-APR-15| 10-JUL-19|

02-APR-15| 01-APR-15| 10-JUL-19|

select a,b,sysdate  from mytable 
  where a IS NULL  AND SYSDATE > b 

select a,b,sysdate from mytable
   where a <= b

Solution

When where statement gets null values, then it returns unknown, which ecludes record from resultset.

Read more about three valued logic in SQL Server (just google it :) ).

This is why you don't get any records. For example, comapring to a column, where a is always null, gives you no records in rsult.

You need to use coalesce or similair "null-preventing" functions.