default value in select 2

[icemel]

Hi,

What is the best way to write this?

In a SELECT statement, if the value selected is null, then use a default value instead. Is a case statment the way to go?

Thanks

 

[gmmastros]

Select IsNull(FieldName, 'HardCodedDate')

Or you could use Coalesce:

Select Coalesce(FieldName, 'HardCodedDate')


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

use COALESCE or ISNULL

select COALESCE(ColName,'') for chars or
select COALESCE(ColName,0) for numeric

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

Hi, thanks. Not sure what I'm doing wrong here...

ALTER VIEW [dbo].[MailList]
AS
SELECT DISTINCT C.Email, C.Name, IsNull(c.webAddr, '

http://www.defaultwebsite.com)

FROM dbo.Contacts AS C LEFT OUTER JOIN

Error:

Create View or Function failed because no column name was specified for column 3.

THANKS

 

[SQLDenis]

ALTER VIEW [dbo].[MailList]
AS
SELECT DISTINCT C.Email, C.Name, IsNull(c.webAddr, '

http://www.defaultwebsite.com')

AS webAddr
FROM dbo.Contacts AS C LEFT OUTER JOIN

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

You're missing an apostrophe and you must give the column an alias

ALTER   VIEW [dbo].[MailList]
AS
SELECT DISTINCT C.Email, C.Name, IsNull(c.webAddr, 'www.defaultwebsite.com[!]'[/!]) [!]As SomeColumnName[/!]
FROM dbo.Contacts AS C LEFT OUTER JOIN

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[icemel]

thanks!