CODEWITHSUNDEEP
sqlasp.net
Suraj Nerati
Suraj Nerati

Reputation: 99

Adding with NULL in SQL

i'm planning to add sql values. 

 Dim cmdStringc23 As String = " Update [Associate_wise_chart] 
        set [Hours]= (select SUM(tat) from [Dashboard].[dbo].[Dashboard] 
        where [assignee] like '%Santosh%') + (select SUM(tat) 
        from [Dashboard].[dbo].[requests] 
        where [assignee] like '%Santosh%') "

So consider, value from dashboard table is 10 value from requests table is NULL.

So I'm getting answer as 10+NULL = NULL.
I have set tat as NULL. My requirement , i have to display answer as 10 and not as NULL

Could any one have a look ?

Upvotes: 0

Views: 97

Answers (6)

Veljko89
Veljko89

Reputation: 1953

Use ISNULL function from SQL ... 

Update [Associate_wise_chart] 
  set [Hours]= ISNULL((select SUM(tat) from [Dashboard].[dbo].[Dashboard] 
 where [assignee] like '%Santosh%'), 0) + ISNULL((select SUM(tat) 
from [Dashboard].[dbo].[requests]
 where [assignee] like '%Santosh%'), 0) "

That will get you ISNULL(10, 0) + ISNULL(NULL, 0) = 10 + 0 = 10

Upvotes: 4

Suraj Nerati
Suraj Nerati

Reputation: 99

Update [Dashboard].[dbo].[Associate_wise_chart] set [Hours]= ISNULL ( (select SUM(tat) from [Dashboard].[dbo].[Dashboard] where [assignee] like '%Santosh%'),0) + ISNULL((select SUM(tat) from [Dashboard].[dbo].Unbuilds where [assignee] like '%Santosh%'),0) where [Name]='Santhosh'

ISNULL is working. Thank you Everyone.

Upvotes: 0

Clar Cleetus
Clar Cleetus

Reputation: 275

Dim cmdStringc23 As String = " Update [Associate_wise_chart] 
    set [Hours]= (select isnull(sum(tat), 0) from [Dashboard].[dbo].[Dashboard] 
    where [assignee] like '%Santosh%') + (select isnull(sum(tat), 0) 
    from [Dashboard].[dbo].[requests] 
    where [assignee] like '%Santosh%') "

Use isnull

Upvotes: 0

praveen
praveen

Reputation: 9

fast and easy just convert tat column into Not Null and have 0 in place of NULL.

Upvotes: 0

user6309797
user6309797

Reputation:

set NOTNULL while creating the table

Upvotes: 0

Koshinae
Koshinae

Reputation: 2320

You can't.

NULL is a synonym for "unknown". Ten plus unknown is unknown. The unknown can be 0, -1, 42, or even 6E23+π/2. But it's still unknown.

This is why you get NULL.

Instead, check for NULLs, and only add the other value, if it is not NULL.

Upvotes: 1

Related Questions