Sql-server – Computed field when NULL

computed-columnnullsql servert-sql

I have two fields in my table : A int, B int. I want to add two computed fields (CF1 & CF2) that uses these two fields A & B.

CF1 = (case when [A]>[B] then (CF1=3) else case when [A]<[B] then (CF1=0) else (CF1=1) end end)

CF2 = (case when [A]<[B] then (CF2=3) else case when [A]>[B] then (CF2=0) else (CF2=1) end end)

This works fine, but when the two fields (A & B) are NULL, CF1 and CF2 are = 1. How do I avoid this? I mean when either one of the fields A or B is NULL, both CF1 and CF2 should also be NULL.

In other words: there should only be a value of 0, 1, or 3 in CF1 and CF2 if there is a value NOT NULL in fields A & B.

+------+------+------+------+
|  A   |  B   | CF1  | CF2  |
+------+------+------+------+
| 2    | 3    | 0    | 3    |
| 5    | 2    | 3    | 0    |
| 2    | 2    | 1    | 1    |
| NULL | NULL | NULL | NULL |
| NULL | 1    | NULL | NULL |
| 1    | NULL | NULL | NULL |
+------+------+------+------+

Best Answer

First, let me explain why your code doesn't work when A or B is NULL by formatting your code to show that you have 2 CASE expressions, one inside the other:

CF1 = (case 
           when [A]>[B] then (CF1=3) 
           else case 
                    when [A]<[B] then (CF1=0) 
                    else (CF1=1) 
                end 
       end)

If A > B is true, the result is (CF1=3). If that isn't true, and if A < B is true, the result is (CF1=0). Otherwise, if neither A>B nor A<B is true, the result is (CF1=1). This last case (the ELSE - ELSE part) covers any remaining case which means when A = B or A is null or B is null.

So to solve the issue, you need to explicitly compare A = B or compare A and B with NULL, to differentiate. As a result the CASE expression should have 4 parts, not 3. And (irrelevent to the issue) we don't need two nested expressions, we can have the result we want with one expression.

And I assume that you want the result to be 3, 1, etc and the (CF1=3) is just pseudo-code.

CF1 = case 
          when A > B then  3 
          when A < B then  0 
          when A = B then  1 
          else             null 
      end

Note that the ELSE NULL is not needed as it is the default for CASE expressions. If you want, you can have even more strict and clear with your intentions code:

CF1 = case 
          when (A > B) then  3
          when (A < B) then  0 
          when (A = B) then  1 
          when (A is null or b is null) then 
                             null
          else               null     --  'ERROR: This should never happen'
      end
Related Question