# Computed field when NULL

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 |
+------+------+------+------+
``````

## 4 Answers

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
``````

You could also do this without any `CASE` expression at all.

The `SIGN` function

Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

and returns `NULL` on `NULL` input

If you can live with `0,1,2` instead of `0,1,3` then you could just use `1 + SIGN(A - B)`.

If you must have `0,1,3` then you could use the following pattern

``````+------+----------+--------------------+------------------------+
| sign | sign + 1 | Power(2, sign + 1) | Power(2, sign + 1) - 1 |
+------+----------+--------------------+------------------------+
|   -1 |        0 |                  1 |                      0 |
|    0 |        1 |                  2 |                      1 |
|    1 |        2 |                  4 |                      3 |
+------+----------+--------------------+------------------------+
``````

As below

``````DECLARE @T TABLE (
A INT,
B INT,
cf1 AS POWER(2, SIGN(A - B) + 1) - 1,
cf2 AS POWER(2, SIGN(B - A) + 1) - 1 );

-- populate with some data
INSERT INTO @T
(A,
B)
VALUES      (2,3),
(5,2),
(2,2),
(NULL,NULL),
(NULL,1),
(1,NULL);

SELECT *
FROM   @T
``````

which returns

``````+------+------+------+------+
|  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 |
+------+------+------+------+
``````
1. You should add an additional condition for equality.
The result of an unmatched CASE statement is a NULL,
so all other use-cases will result in a NULL.

2. No need for nested CASE statements

``````CF1 = case when [A]>[B] then 3 when [A]<[B] then 0 when [A]=[B] then 1 end
CF2 = case when [A]<[B] then 3 when [A]>[B] then 0 when [A]=[B] then 1 end
``````

Demo

``````create table t ([A] int,[B] int)
insert into  t ([A],[B]) values (2,3),(5,2),(2,2),(null,null),(null,1),(1,NULL)
``````

``````select  [A],[B]
,CF1 = case when [A]>[B] then 3 when [A]<[B] then 0 when [A]=[B] then 1 end
,CF2 = case when [A]<[B] then 3 when [A]>[B] then 0 when [A]=[B] then 1 end

from    t
``````

``````+------+------+------+------+
| 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 |
+------+------+------+------+
``````

A complete t-sql table creation script would be like the following

``````use tempdb
go
create table #t (a int, b int
, cf1 as case when [A]>[B] then 3 when [A]<[B] then 0 when [A]=[B] then 1 end
, cf2 as case when [A]<[B] then 3 when [A]>[B] then 0 when [A]=[B] then 1 end )
-- populate with some data
insert into #t (a, b) values (2, 3), (5, 2), (2, 2), (null, null), (null, 1), (1, null);
-- select the table
select * from #t
``````

The result will be