how to make nulls return as 0 - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

how to make nulls return as 0

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-09-04 : 17:45:11

I am trying to make nulls return as zeros, and if a value is not null then I want it to return regularly.

For example:

select empID,
empName,
salary

from employee
left join compensation
on employee.empID = compensation.empID

How would I make salary return as 0 instead of null if there was no salary in the compensation table for an employee, but return as the correct salary if there is a salary in the compensation table for that employee?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-04 : 17:47:26

You can use COALESCE or ISNULL functions. Check SQL Server Books Online for details such as syntax.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-04 : 17:48:38

try the replace function
http://msdn.microsoft.com/en-us/library/ms186862.aspx

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-04 : 17:52:49

my mistake

quote:
Originally posted by tkizer

You can use COALESCE or ISNULL functions. Check SQL Server Books Online for details such as syntax.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

better option

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 03:52:23

quote:
Originally posted by afrika

try the replace function
http://msdn.microsoft.com/en-us/library/ms186862.aspx

How would replace help in this case?
You would still get NULL

Madhivanan

Failing to plan is Planning to fail

shanmp
Starting Member

13 Posts

Posted - 2008-09-05 : 04:13:28

Please see the below query , I have used case statement hope it will work fine.
select select empID,
empName,
case when salary = 0 then null else salary end as salary
from employee
left join compensation
on employee.empID = compensation.empID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 04:17:44

That should be

select select empID,
empName,
case when salary is null then 0 else salary end as salary
from employee
left join compensation
on employee.empID = compensation.empID

or

select select empID,
empName,
coalesce(salary ,0) as salary
from employee
left join compensation
on employee.empID = compensation.empID

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources