Post reply

Using a CASE statement and checking for NULL values?

  • 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

  • August 14, 2014 at 12:43 am

    #1737914

    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

  • August 14, 2014 at 12:53 am

    #1737917

    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

  • August 14, 2014 at 12:58 am

    #1737918

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

    , CASE WHEN 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:

    , CASE WHEN 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

  • August 14, 2014 at 2:06 am

    #1737936

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

  • August 14, 2014 at 6:21 pm

    #1738245

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

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

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