replace "0" with "null" (blank) in SQL query

[Homerboy]

part of my SELECT statement looks like the following:

COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -1) THEN FILENO END) AS '-1',

What I'd like to be able to do is if that COUNT returns "0" that it is replaced with "NULL" so that it shows up in my report as a blank cell/value.

Any suggestions or thoughts would be appreciated.

Thanks.

 

[Cogman]

Could you throw some logic into your report to say "if 0, don't print"? Either way, this is probably what you are looking for

http://www.tizag.com/sqlTutorial/sqlcase.php
http://sqlservercodebook.blogspot.com/2008/03/return-null-if-value-is-certain-value.html

 

[KIAman]

SELECT CASE WHEN COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -1) THEN FILENO END) = 0 
THEN 
null 
ELSE 
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -1) THEN FILENO END) AS '-1',

 

[Homerboy]

I get what is going on there, but it's throwing an error:


Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AS'.

 

[KLin]

Missing an End keyword.

You could also use

SELECT NullIf(COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -1) THEN FILENO END), 0) As '-1'

And only need one line of code.

 

[Homerboy]

Missing an End keyword.

You could also use

SELECT NullIf(COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -1) THEN FILENO END), 0) As '-1'

And only need one line of code.

Yeah I figured out the issue in the initial code, but this new, single line is WAY cleaner and easy to implement (as I have dozens of these COUNT statements in the full query.

This should work awesomely.

Thanks!