Replace Null with 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

SQL Server 2005 Forums

Transact-SQL (2005)

Replace Null with 0

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-09-15 : 05:44:34

I have this query which returns the max number in the table, i'm looking to find out how I can change the return value if Null then how can I replace it with 0 (Zero)?

[CODE]
SELECT MAX([Weeks Waiting]) AS 'Max'
FROM jez.CYPPhyPaediatric
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 05:50:30

use ISNULL(MAX(WeeksWaiting),0) or
COALESCE(MAX(WeeksWaiting),0)

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-15 : 06:20:57

Visa, why do you include MAX, what happens when MAX is not used with isnull or coalesce commands?

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 06:32:16

quote:
Originally posted by abacusdotcom

Visa, why do you include MAX, what happens when MAX is not used with isnull or coalesce commands?

I sign for fame not for shame but all the same, I sign my name.

i didnt include it. it was in the code op posted. i just copy pasted from it

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-09-15 : 07:13:49

Excellent, Thanks for help...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 17:00:41

SELECT CAST(MAX([Weeks waiting]) AS VARCHAR(11)) + ' weeks'
from (
select max([Weeks waiting]) AS [Weeks waiting]
from jez.CYPPhyPaediatric

union all

select 0
) AS d

E 12°55'05.63"
N 56°04'39.26"

miked1978
Starting Member

25 Posts

Posted - 2008-09-25 : 09:09:13

Sorry to hijack this thread but your title fits my problem.

I'm getting some null value records eliminated because there are some null values in my Ahead and Behind columns below. How would I replace the nulls with 0 in the following code?

Select Mondate, Ship, Hull, Div, sum (Ahead) as Ahead, sum (Behind) as Behind, sum (SVc) As SVc, sum (CVc) as CVc
into tbAhdBhdstep3
from tbAhdBhdstep2
Group by Mondate, Ship, Hull, Div
Order by Hull, Div

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 09:11:57

quote:
Originally posted by miked1978

Sorry to hijack this thread but your title fits my problem.

I'm getting some null value records eliminated because there are some null values in my Ahead and Behind columns below. How would I replace the nulls with 0 in the following code?

Select Mondate, Ship, Hull, Div, sum (Ahead) as Ahead, sum (Behind) as Behind, sum (SVc) As SVc, sum (CVc) as CVc
into tbAhdBhdstep3
from tbAhdBhdstep2
Group by Mondate, Ship, Hull, Div
Order by Hull, Div

not sure what you're asking for
SUM() will ignore NULL values and return sum of non null values alone. Even if you're trying to convert NULL values to 0 you will get same result. didnt understand the purpose behind changing them to 0.

miked1978
Starting Member

25 Posts

Posted - 2008-09-25 : 09:19:46

quote:
Originally posted by visakh16

quote:
Originally posted by miked1978

Sorry to hijack this thread but your title fits my problem.

I'm getting some null value records eliminated because there are some null values in my Ahead and Behind columns below. How would I replace the nulls with 0 in the following code?

Select Mondate, Ship, Hull, Div, sum (Ahead) as Ahead, sum (Behind) as Behind, sum (SVc) As SVc, sum (CVc) as CVc
into tbAhdBhdstep3
from tbAhdBhdstep2
Group by Mondate, Ship, Hull, Div
Order by Hull, Div

not sure what you're asking for
SUM() will ignore NULL values and return sum of non null values alone. Even if you're trying to convert NULL values to 0 you will get same result. didnt understand the purpose behind changing them to 0.

What would I use other then sum?

There are several records being eliminated becuase the Ahead column contains nulls. I need to show those nulls as zero if possible.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 09:22:43

whats your requirement? to show sum or to show individual values of Ahead & Behind?

miked1978
Starting Member

25 Posts

Posted - 2008-09-25 : 09:30:28

quote:
Originally posted by visakh16

whats your requirement? to show sum or to show individual values of Ahead & Behind?

Well its to show sum. If I don't I get too many records. Kinda tricky huh?

I think I may just do an update before this chunk of code and update all null values to zero.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 09:31:56

quote:
Originally posted by miked1978

quote:
Originally posted by visakh16

whats your requirement? to show sum or to show individual values of Ahead & Behind?

Well its to show sum. If I don't I get too many records. Kinda tricky huh?

I think I may just do an update before this chunk of code and update all null values to zero.

sorry your explanation doesnt make much sense. can you illustrate what you're telling with some sample data?

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-25 : 09:51:36

Just an observation...wouldn't null here in this case mean no rows, or no values at all for n rows?

What value would that have?

Or are you trying to check fopr those 2 things?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

miked1978
Starting Member

25 Posts

Posted - 2008-09-25 : 10:33:57

quote:
Originally posted by visakh16

quote:
Originally posted by miked1978

quote:
Originally posted by visakh16

whats your requirement? to show sum or to show individual values of Ahead & Behind?

Well its to show sum. If I don't I get too many records. Kinda tricky huh?

I think I may just do an update before this chunk of code and update all null values to zero.

sorry your explanation doesnt make much sense. can you illustrate what you're telling with some sample data?

Maybe this will help. Here is the code where I'm actually setting the Ahead and Behind values. Maybe if I can create an If statement setting nulls to zero?

update tbAhdBhdstep2
set Ahead = SVc
where Svc > 0

update tbAhdBhdstep2
set Behind = SVc
where Svc < 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 10:47:01

i told you to post some sample data to illustrate what you're telling.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources