IF...THEN Statement...help! - 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 2000 Forums

SQL Server Development (2000)

IF...THEN Statement...help!

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-09 : 14:24:27

If I have a NULL value in a table and want to replace it with something else, how do I do that?

Here is the coding I have so far, but it doesn't work:

Select * From tblCapRec
If (MailAddress = NULL)
Then MailAddress = PropAddress
End If

Any ideas?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 14:25:12

Select isnull(mailAddress,PropAddress)

Corey

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-09 : 14:40:28

Tara

Thanks for ALWAYS answering ALL my questions. I would love to have just half of your knowledge. Thanks!

Brenda

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-09 : 15:05:54

This is what I have now, but it is not replacing things:

SELECT CaseNumber,MailAddress,MailCity,MailState,MailZipCode,PropAddress,PropCity,PropState,PropZipCode,
(CASE WHEN MailAddress IS NULL THEN PropAddress ELSE MailAddress END)
FROM tblCapRec

What am I doing wrong?

Brenda

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 15:11:44

SELECT CaseNumber,MailAddress,MailCity,MailState,MailZipCode,PropAddress,PropCity,PropState,PropZipCode,
isNull(MailAddress,PropAddress) as YourColumnName
FROM tblCapRec

Corey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 16:01:50

If you are going to use CASE, then you need to put CASE instead of the column:

SELECT CaseNumber,CASE WHEN MailAddress IS NULL THEN PropAddress ELSE MailAddress END,MailCity,MailState,MailZipCode,PropAddress,PropCity,PropState,PropZipCode
FROM tblCapRec

Use ISNULL though like in Corey's query.

Tara

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-08-09 : 16:39:49

SELECT CaseNumber,COALESCE(MailAddress,PropAddress), MailCity,MailState,MailZipCode,PropAddress,PropCity,PropState,PropZipCode
FROM tblCapRec

Pohket
Starting Member

1 Post

Posted - 2004-08-09 : 16:56:43

You said that you wanted to replace the null value with something else - if you wanted to replace it at the table level, as opposed to simply returning it through select statements, you'd have to do an update to it...

update tblCapRec
set MailAddress = PropAddress
where MailAddress IS NULL

// Pohket

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-09 : 18:10:58

Ok, just implementing taras initial suggestion:

SELECT CaseNumber, IsNull(MailAddress, 'something@sqlteam.com'), MailCity, MailState, MailZipCode, PropAddress, PropCity, PropState, PropZipCode
FROM tblCapRec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 18:12:45

It wasn't me! Corey gets the credit.

Tara

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-09 : 18:26:00

Hehe, sorry! "Something" tells me you could have come up with the same solution (

) but Corey definetly is the rightful reciever of credit...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources