ISNULL Not working when I am calculating the sum of two columns in the expression part of the function

2326 views sql-server
-4

I am trying to use ISNULL () while the expression part instead of having a column name as the argument have the sum of two columns and it should return a 0 whenever one of the value in the column is NULL else the sum. This is how I have written my query:

Select ISNULL([FY18 P1]+[FY18 P2]+[FY18 P3]+[FY18 P4],0) as [Previous YTD]
from TableA 

This calculated column inside the ISNULL function is not working. Can anybody help me rewrite this expression so that it will work.

answered question

Hi and welcome to SO. What do you mean by "is not working"? The code you posted should work for the requirements you stated.

If any of those columns IS NULL then it will returrn 0 no matter what is the value of other columns, mybe you are looking to use ISNULL() function for each column.

1 Answer

11

If any column in your concatenation IS NULL then the result will be NULL.

You need to wrap each column in IS NULL to make this column value 0 so that your addition doesn't return NULL.

SELECT ISNULL([FY18 P1],0) +ISNULL([FY18 P2],0) + ISNULL([FY18 P3],0) + ISNULL([FY18 P4],0) 

This is because anything + NULL returns NULL

select 1 + 2 + 3 + NULL --returns `NULL`

posted this

Have an answer?

JD

Please login first before posting an answer.