Change Null to Zero - 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)

Change Null to Zero

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-09-24 : 09:53:33

I have this query in SQL Server that brings me back the Max number of weeks within a recordset. I have some that have no records in, how can I say in the code below that if its Null then show Zero?

[CODE]
SELECT Convert(varchar, Max([Weeks Waiting])) + ' Weeks' As [Max]
FROM jez.WL_DATA
[/CODE]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-24 : 10:01:31

SELECT ISNULL(Convert(varchar, Max([Weeks Waiting])),'0' + ' Weeks' As [Max]
FROM jez.WL_DATA

jim

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-24 : 10:10:48

SELECT ISNULL(Convert(varchar, Max([Weeks Waiting])),'0') + ' Weeks' As [Max]
FROM jez.WL_DATA

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-24 : 10:16:24

Always specify the length during convertion
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 16:56:25

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

union all

select 0
) AS d

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources