Adding three Integer variable shows result as Null, why?

Here is the code I tried

SELECT 
 case when InsertedRows is null then 0 else InsertedRows end InsertedRows
,case when FailedRows is null then 0 else FailedRows end FailedRows
,case when UpdatedRows is null then 0 else UpdatedRows end UpdatedRows
,InsertedRows + UpdatedRows + FailedRows as tot
FROM PATS.ImportLog
WHERE CreatedBy='suvaneeth' 
      AND ISNULL(CompletedYN,0) = 0 
      AND CAST(CreatedDate AS date) >= CAST(GETDATE() AS date)

and I get the result for tot is NULL

99  0   0   NULL

I'm expecting the result is 99

select 
case when InsertedRows is null then 0 else InsertedRows end InsertedRows
,case when FailedRows is null then 0 else FailedRows end FailedRows
,case when UpdatedRows is null then 0 else UpdatedRows end UpdatedRows
,COALESCE(InsertedRows,0) + COALESCE(UpdatedRows,0) + COALESCE(FailedRows,0) as tot
from PATS.ImportLog
WHERE CreatedBy='suvaneeth' AND ISNULL(CompletedYN,0)=0 AND CAST(CreatedDate AS date)>=CAST(GETDATE() AS date)

Adding three Integer variable shows result as Null, why? Ask Question Asked 1 year, 4 months ago. Active 1 year, 4 months ago. Addition with NULL values.

You can try using ISNULL(insertedrows, 0) or COALESCE(insertedrows, 0) instead of CASE

The Null indicates that expression or variable doesn’t contain valid data. Null is not the empty value because VBA thinks variable value has not yet been started and doesn’t treat as Null. Examples of ISNULL Function in VBA. Below are the examples of the VBA ISNULL Function. Example #1. Start off with a simple VBA ISNULL example. Check

Try in this way.

select 
  Coalesce(InsertedRows, 0) InsertedRows
  ,Coalesce(FailedRows, 0) FailedRows
  ,Coalesce(UpdatedRows, 0) UpdatedRows
  ,Coalesce(InsertedRows, 0) + Coalesce(FailedRows, 0)+ Coalesce(UpdatedRows, 0) as tot
from PATS.ImportLog
WHERE CreatedBy='suvaneeth' AND ISNULL(CompletedYN,0)=0 AND CAST(CreatedDate AS date)>=CAST(GETDATE() AS date)

CREATE TABLE customer ( id INT NOT NULL, date_of_birth DATE NULL ) User-defined variables are NULL until a value is explicitly assigned. Stored routines parameters and local variables can always be set to NULL. If no DEFAULT value is specified for a local variable, its initial value will be NULL.

R language supports several null-able values and it is relatively important to understand how these values behave, when making data pre-processing and data munging. In general, R supports: NULL NA NaN Inf / -Inf NULL is an object and is returned when an expression or function results in an undefined value. In R language, NULL … Continue reading R null values: NULL, NA, NaN, Inf

Yes good point, but checked the database and it's really a NULL value. If I check "blank on null" value is blanked. I made a really simple report just to reproduce problem. See below. Variable "variable1" just check if MT_LIB field is null. It prints 'false' is field is not null but it print "null" instead of "true" if field is null.

Fortunately (I mean fortunately), you cannot assign NULL to int variable. NULL is mostly used with pointers. Also for a char value you can directly assign null as char a = ‘\0’; Strings have a delimiter which is also a null char.

Comments
  • NULL + 0 = NULL
  • Possible duplicate of Addition with NULL values
  • there is no NULL values. All of them are converted into 0
  • They are on another column expressions, not on your table. You would have to repeat the whole CASE again for that matter.
  • The tot column in the result just adds them directly, without regarding to nulls. There is at least one in either of them.
  • There is no error message. It shows the same result what i was trying
  • are you sure that your where clause returns any result?