2

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

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
|improve this answer|||||
6

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 |
+------+------+------+------+
|improve this answer|||||
4
  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 |
+------+------+------+------+
|improve this answer|||||
0

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

enter image description here

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.