CODEWITHSUNDEEP
sqlsql-servert-sql
ryansin
ryansin

Reputation: 1817

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

Upvotes: 6

Views: 34067

Answers (3)

gofr1
gofr1

Reputation: 15977

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.

Upvotes: 6

gvee
gvee

Reputation: 17161

SELECT NullIf(<your_code>, 0) AS zero_to_null
     , Coalesce(NullIf(<your_code>, 0), 1) AS zero_to_null_to_one

or

SELECT CASE WHEN other_total = 0 THEN 1 ELSE other_total END AS new_total
FROM   (
        <your_code>
       ) AS a_subquery

Upvotes: 3

Squirrel
Squirrel

Reputation: 24763

use DERIVED QUERY or CTE

-- Derived query

SELECT itemcode, total, foo, case when other_total = 0 then 1 else other_total end as other_total
FROM
(
  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
) d

OR

you can just use CASE on the last 2 expression of the CASE WHEN... since that is the only 2 places that will gives you 0.

  WHEN foo IN ('foo', 'bar')
  THEN CASE WHEN 10 % count(*) = 0 THEN 1 ELSE 10 % count(*) END
  ELSE CASE WHEN  9 % count(*) = 0 THEN 1 ELSE  9 % count(*) END
  END AS other_total

Upvotes: 0

Related Questions