case expressions and set ansinull

Case expressions may produce different results for null values if you enable set ansinull and include a when NULL clause in queries with structures similar to:

select CVT = 
   case case_expression
      when NULL  then 'YES'
      else 'NO'
   end
from A

If you set ansinull to off (the default) these case expressions match the NULL values and the predicate evaluates to true for NULL values (similar to how the query processor evaluates a where clause that includes a NULL value). If you set ansinull to on the case expression cannot compare the NULL values, and evaluates the predicate to false. For example:

select CVT = 
   case advance 
          when  NULL  then 'YES'
          else  'NO'
   end, 
advance from titles

--- ------------------------ 
NO                 5,000.00 
NO                15,000.00 
YES                    NULL 
NO                 7,000.00 
NO                 8,000.00 
YES                    NULL
NO                  7,000.00

However, if you enable set ansinull and run the same query, case expressions returns a no value when it encounters a NULL value:

set ansinull on

select CVT =  
   case advance 
          when  NULL  then 'YES'
          else  'NO'
   end, 
advance from titles

 CVT advance                  
--- ------------------------ 
NO                  5,000.00 
NO                 15,000.00 
NO                      NULL 
NO                  7,000.00 
NO                  8,000.00 
NO                      NULL 
NO                   7,000.00

See the Reference Manual: Commands.