CODEWITHSUNDEEP
sqlsql-server
Jack
Jack

Reputation: 387

How can I replace the NULL or 0 value to dash(-) or a blank string

I want to return a string (-) dash, or empty value when the returned value is NULL or Zero, below is the finding, the first column ([Total 1]) is the original SUM() query, the second column ([Total 2]) is the result I want, it is working but the code is repeated and looks bulky, is there any cleaner way?

    SELECT 
    SUM(CASE 
        WHEN ( DATEDIFF(day,OrderDate,GETDATE()) = 0 ) THEN 1 
        WHEN ( DATEDIFF(day,BookingDate,GETDATE()) = 0 ) THEN 1              
    END) AS [Total 1],
 
    (CASE 
        WHEN (SUM(CASE 
                        WHEN ( DATEDIFF(day,OrderDate,GETDATE()) = 0 ) THEN 1 
                        WHEN ( DATEDIFF(day,BookingDate,GETDATE()) = 0 ) THEN 1              
                    END) IS NULL)
        THEN '-' 
        ELSE
            CAST(SUM(CASE 
                WHEN ( DATEDIFF(day,OrderDate,GETDATE()) = 0 ) THEN 1 
                WHEN ( DATEDIFF(day,BookingDate,GETDATE()) = 0 ) THEN 1              
            END) AS VARCHAR(20))
            end 
    ) AS [Total 2]
FROM TABLE

Thanks.

Upvotes: 0

Views: 1533

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You have to convert the value to a string. If you use a CASE expression, you need to repeat the SUM(). Another method is to nest NULLIF() and COALESCE():

COALESCE(NULLIF(CONVERT(VARCHAR(255),
                        SUM(CASE WHEN ( DATEDIFF(day,OrderDate,GETDATE()) = 0 ) THEN 1 
                                 WHEN ( DATEDIFF(day,BookingDate,GETDATE()) = 0 ) THEN 1              
                            END), 0
                       )
               ), '-'
        ) AS [Total 1],

This type of operation is rather cumbersome in SQL. Often, it is better done in at the application level.

Upvotes: 1

Related Questions