Another Case problem - 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 2008 Forums

Transact-SQL (2008)

Another Case problem

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-04-17 : 08:34:39

I would need to use the value from the field as is if it is something else than 0 or NULL. If it is 0 or NULL, replace it with 1. I thought something like this:

CASE MyField
WHEN 0 THEN 1
WHEN IS NULL THEN 1
ELSE MyField
END

But it doesn't seem to work, gives me an error pointing to that is (i.e. I'm pretty sure I use the IS NULL in some wrong way).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-17 : 09:03:55

A slight change to the syntax:

CASE
	WHEN MyField = 0 THEN 1
	WHEN MyField IS NULL THEN 1
	ELSE MyField
END

You can play with that and do all other kinds of variations. For example, both of these should result in the same behavior:

CASE
	WHEN COALESCE(Myfield,0) = 0 THEN 1
	ELSE MyField
END



COALESCE(NULLIF(Myfield,0),1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources