SQL case 0 then NULL

Question

I want to get NULL when the number in my table is 0. How can I achieve this using SQL? I tried

SELECT ID, 
       Firstname, 
       Lastname, 
       CASE Number 
         WHEN 0 THEN NULL 
       END
  FROM tPerson

But this results in an error:

At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.

Answer 1

As others have mentioned you forgot to tell your CASE statement to return the number in case the number is not null.

However in SQL Server you can use NULLIF, which I consider more readable:

select 
  id, 
  firstname, 
  lastname, 
  nullif(number, 0) as number
from tperson;

If you want to stick to standard SQL then stay with CASE:

case when number = 0 then null else number end as number

Answer 2

May be you need ELSE:

SELECT 
  ID, Firstname, Lastname CASE Number WHEN 0 THEN NULL ELSE Number END
FROM tPerson