none
Using a CASE statement and checking for NULL values?

    Question

  • 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).

    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

    Thursday, August 14, 2014 6:02 AM

Answers

  • Hi David,

    To take your code and check, better post as text. So that we can just copy and try out before suggesting a solution.

    For checking is null you could use it like below

    isnull(column,'N/A')


    Working as a Senior Database Analyst & Architect at Ministry of Higher Education in KSA

    Thursday, August 14, 2014 6:14 AM
  • 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).

    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

    Use 

    isnull(...,0)=0

    instead of having "... is null" above.


    Many Thanks & Best Regards, Hua Min


    Thursday, August 14, 2014 10:17 AM

All replies

  • The image is not visible to me..Can you post the code within code block?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, August 14, 2014 6:09 AM
  • Hi David,

    To take your code and check, better post as text. So that we can just copy and try out before suggesting a solution.

    For checking is null you could use it like below

    isnull(column,'N/A')


    Working as a Senior Database Analyst & Architect at Ministry of Higher Education in KSA

    Thursday, August 14, 2014 6:14 AM
  • Hi David,

    First, I would use a derived table or a CTE and use an alias for the expression you are evaluating. This is just a matter of readability. Instead of writing the same long expression several times, you can then write it once inside the derived table or CTE, give it a column alias, and then use the friendly alias several times instead of the long expression.

    Now, let's say your alias is "ProfitLoss", then your CASE expression, should look like that:

    CASE
    	WHEN ProfitLoss = '0.00'	THEN 'N/A'
    	WHEN ProfitLoss IS NULL		THEN 'N/A'
    	ELSE				ProfitLoss
    END
    

    I hope it helps...

    --------------------------------------------
    Guy Glantser
    SQL Server Consultant & Instructor
    Madeira - SQL Server Services
    http://www.madeirasql.com


    Thursday, August 14, 2014 6:25 AM
  • 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).

    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

    Use 

    isnull(...,0)=0

    instead of having "... is null" above.


    Many Thanks & Best Regards, Hua Min


    Thursday, August 14, 2014 10:17 AM