Skip to content
Advertisement

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

Advertisement

Answer

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)
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement