Adding three Integer variable shows result as Null, why?

Question

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

They are on another column expressions, not on your table. You would have to repeat the whole CASE again for that matter. — EzLo
– EzLo, Commented Jan 28, 2019 at 13:15
The tot column in the result just adds them directly, without regarding to nulls. There is at least one in either of them. — Alejandro
– Alejandro, Commented Jan 28, 2019 at 13:16

Dheerendra · Accepted Answer · 2019-01-28 13:13:34Z

2

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)

answered Jan 28, 2019 at 13:13

Dheerendra

3081 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

KPavezC · Accepted Answer · 2019-01-28 13:14:10Z

1

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

answered Jan 28, 2019 at 13:14

KPavezC

3052 gold badges6 silver badges20 bronze badges

Comments

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-01-28 13:18:48Z

0

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)

edited Jan 28, 2019 at 13:18

answered Jan 28, 2019 at 13:14

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

2 Comments

Dhanil Dinesan Over a year ago

There is no error message. It shows the same result what i was trying

Derviş Kayımbaşıoğlu Over a year ago

are you sure that your where clause returns any result?

Collectives™ on Stack Overflow

Adding three Integer variable shows result as Null, why?

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related