Adding with NULL in SQL

Question

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 ?

Do something like set ColX = coalesce(nullable-select, ColX). — jarlh
– jarlh, Commented Aug 10, 2016 at 11:51
ok, can you help , how to define TAT column as null or not null. i should display the value irrespective of NULL. — Suraj Nerati
– Suraj Nerati, Commented Aug 10, 2016 at 11:53
If you set Tat column to NOT NULL you'll get an error instead... (A (sub)select with SUM returns NULL if no rows found.) — jarlh
– jarlh, Commented Aug 10, 2016 at 11:55

Veljko89 · Accepted Answer · 2016-10-13 11:28:33Z

4

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

edited Oct 13, 2016 at 11:28

answered Aug 10, 2016 at 11:52

Veljko89

1,9533 gold badges30 silver badges49 bronze badges

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

2 Comments

jarlh Over a year ago

Actyally, ISNULL is not (ANSI) SQL, it's a product specific function.

jarlh Over a year ago

Absolutely, and COALESCE() is the ANSI SQL function.

Koshinae · Accepted Answer · 2016-08-10 11:52:43Z

1

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.

answered Aug 10, 2016 at 11:52

Koshinae

2,3302 gold badges31 silver badges42 bronze badges

Comments

user6309797 · Accepted Answer · 2016-08-10 11:52:49Z

0

set NOTNULL while creating the table

answered Aug 10, 2016 at 11:52

user6309797

1 Comment

jarlh Over a year ago

Wont help. A (sub)select with SUM returns NULL if no rows found.

praveen · Accepted Answer · 2016-08-10 11:53:22Z

0

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

answered Aug 10, 2016 at 11:53

praveen

92 bronze badges

Comments

Clar Cleetus · Accepted Answer · 2016-08-10 11:55:31Z

0

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

answered Aug 10, 2016 at 11:55

Clar Cleetus

2751 silver badge12 bronze badges

Comments

Suraj Nerati · Accepted Answer · 2016-08-10 12:02:58Z

0

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.

answered Aug 10, 2016 at 12:02

Suraj Nerati

991 silver badge9 bronze badges

Collectives™ on Stack Overflow

Adding with NULL in SQL

6 Answers 6

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related