user2696497 user2696497 - 1 month ago 10
SQL Question

Check if column value is zero MS SQL Server

I have a view which has a complicated CASE statement to determine the value of one of the columns.

SELECT a.itemcode, count(*) total, b.foo
CASE
WHEN foo IN ('aab', 'aac')
THEN 1
WHEN foo IN ('qqq', 'fff')
THEN 2
WHEN foo IN ('foo', 'bar')
THEN 10 % count(*)
ELSE 9 % count(*)
END AS other_total

FROM a INNER JOIN b ON a.itemcode = b.itemcode
GROUP BY itemcode, foo


I want to add a check for the value of the column other_total. If it is
0
, I want to set the value to
1
.

Obviously I could surround the whole thing in a
CASE
statement...

CASE ( CASE
WHEN foo IN ('aab', 'aac')
THEN 1
WHEN foo IN ('qqq', 'fff')
THEN 2
WHEN foo IN ('foo', 'bar')
THEN 10 % count(*)
ELSE 9 % count(*)
END )
WHEN 0 THEN 1
ELSE CASE
WHEN foo IN ('aab', 'aac')
THEN 1
WHEN foo IN ('qqq', 'fff')
THEN 2
WHEN foo IN ('foo', 'bar')
THEN 10 % count(*)
ELSE 9 % count(*)
END
END AS other_total


But this is just a bit messy and seems like there should be an easier way.

Is there another function, similar to
ISNULL()
, that would allow me to change the value of the column if it equals zero?

ANSWER

Thanks to gofr1's answer I was able to work this one out. I used the NULLIF function to return NULL if the case statement was equal to 0, then surrounded with an ISNULL function to set the value to 1 if the NULLIF function returned NULL.

SELECT a.itemcode, count(*) total, b.foo,
ISNULL (
NULLIF (
CASE
WHEN foo IN ('aab', 'aac')
THEN 1
WHEN foo IN ('qqq', 'fff')
THEN 2
WHEN foo IN ('foo', 'bar')
THEN 10 % count(*)
ELSE 9 % count(*)
END
), 0)
), 1) other_total

FROM a INNER JOIN b ON a.itemcode = b.itemcode
GROUP BY itemcode, foo

Answer

You can use NULLIF

Returns a null value if the two specified expressions are equal.

CASE WHEN 
NULLIF(
    CASE WHEN foo IN ('aab', 'aac') THEN 1
        WHEN foo IN ('qqq', 'fff') THEN 2
        WHEN foo IN ('foo', 'bar') THEN 10 % count(*)
        ELSE 9 % count(*)
        END
    ,0) IS NULL THEN 0 ELSE 1 END
 AS other_total

If value = 0 then it becomes NULL else the value is in output. Then we use CASE WHEN value IS NULL then 0 else 1.