conversion from null to 0 (zero)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

conversion from null to 0 (zero)

Author

Topic

msc_timor
Starting Member

11 Posts

Posted - 2007-04-21 : 01:44:45

hi,
i have two tables with similar data i.e one table has sales data and other table has sales return data
what i want to do is join these two tables in select statement and want the net value in one column(sales-salesreturn). fine, but the problem lies here, some data are NULL so subtaction is not possible. so how can i change NULL to 0 (zero)
plz find me a solution

thanks

spejbl
Starting Member

28 Posts

Posted - 2007-04-21 : 02:05:53

[code]ISNULL(YourColumn,0)[/code]

--
Tom
Microsoft KB articles monitoring | Apple KB articles monitoring

Kristen
Test

22859 Posts

Posted - 2007-04-21 : 02:13:50

We use COALESCE(YourColumn,0) here, instead of ISNULL, because that is Ansi standard.

FWIW Coalesce lets you use multiple expressions, so:

COALESCE(PrimaryTable.MyColumn, SecondaryTable.AnotherColumn, 0)

there are also some subtle issues with IsNull() when implicit Casting is invoked between different types.

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-21 : 02:16:39

another point in favor of coalesce is that it's harder to spell, so it makes you a better speller.

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-04-21 : 02:29:29

Ha! Not to mention the fact that IsNull() implies a return value of True/False rather than "Return that first value that is NOT null ...

msc_timor
Starting Member

11 Posts

Posted - 2007-04-21 : 05:12:06

hi, thanx,
isnull worked fine for me
thanx 'gain

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-21 : 11:34:48

blatantly refusing our advice! the impudence!

www.elsasoft.org

herothecat
Starting Member

19 Posts

Posted - 2007-04-21 : 12:25:51

Hi All,

Although COALESCE is ANSI standard and has it's benefits ISNULL is just as usefull, and in some cases prefered.

See: http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html for more details.

Just my two cents!

Why push the envelope when you can just open it?

Kristen
Test

22859 Posts

Posted - 2007-04-21 : 13:11:49

Most of the differences I regard as Negatives against ISNULL().

But for my money I would not want to have a mixture of the two through our code, and because we sometimes want multiple parameters we have standardised on COALESCE()

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-04-21 : 21:19:22

not to mention datatype precedence...


declare @i1 int
declare @v1 bigint
select coalesce(@i1, @v1, 1111111111111111111)
select isnull(@i1, 1111111111111111111)
select isnull(@v1, 1111111111111111111)

Edit:
ok... it's in the article... haven't read it before posting...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-04-22 : 06:23:46

"Edit:
ok... it's in the article... haven't read it before posting..."

And the thread

"there are also some subtle issues with IsNull() when implicit Casting is invoked between different types."

... you're getting old old-boy!

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-04-22 : 11:57:28

looks like it yes.... or i can blame it on posting at 2:30 am
I'm going to be silent about my state at the time

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources