Using a CASE statement and checking for NULL values?

Question

Post reply

Using a CASE statement and checking for NULL values?

david.dartnell

Ten Centuries

Points: 1276
More actions
August 14, 2014 at 12:02 am

#292224

Hi Everyone,
I am using the CASE statement below (the commented section does not work, and it is the part that I need to remedy).
I am using the CASE statement below (the commented section does not work, and it is the part that I need to remedy).
Whilst the above works fine when looking for the value '0.00', and the responding output is correct, I also need to look for NULLs and respond accordingly. If anybody has any suggestions on how I can go about this it will be greatly appreciated.
Kind Regards,
David

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

The commented line should be preceded with a new CASE.

If you would have posted your code as regular text instead of a screenshot, I could have modified it 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

david.dartnell Ten Centuries Points: 1276 More actions · Answer 2

Hi Koen,

I am not really following - can you please edit the code block below?

, CASE WHEN

CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS decimal(10,2)) AS varchar) = '0.00' THEN 'N/A'

-- CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS decimal(10,2)) AS varchar) IS NULL THEN 'N/A'

ELSE CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS decimal(10,2)) AS varchar) END

Kind Regards,

David

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

At second sight another CASE is not needed necessarily, just another WHEN will suffice.

,CASEWHEN CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS DECIMAL(10,2)) AS VARCHAR(30)) = '0.00'

THEN 'N/A'

WHEN CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS DECIMAL(10,2)) AS VARCHAR(30)) IS NULL

THEN 'N/A'

ELSE CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS DECIMAL(10,2)) AS VARCHAR(30))

END

Or you can shorten the code like this:

,CASEWHEN ISNULL(CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS DECIMAL(10,2)) AS VARCHAR(30)),'0.00') = '0.00'

THEN 'N/A'

ELSE CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS DECIMAL(10,2)) AS VARCHAR(30))

END

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

david.dartnell Ten Centuries Points: 1276 More actions · Answer 4

Thanks so much for your advice. I will give this a crack tomorrow after a good nights sleep!

david.dartnell Ten Centuries Points: 1276 More actions · Answer 5

Thanks again, I am using the top code excerpt that you kindly shared, and everything is working great! 🙂