Select Case IsNull - 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

General SQL Server Forums

New to SQL Server Programming

Select Case IsNull

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-28 : 10:19:03

Hi, I have the following query and I do not see why the else statement does not work the way I wish.
In one situation I have the transit.name Null and transit2.name not null, it does not display the value of transit2.name ????

SELECT
CASE
WHEN vbig.name is null
THEN ss.newuser
Else Isnull(Isnull(Isnull( transit.name , transit2.name ), transit3.name ), transit4.name )
END AS Username
from table

Thanks in advance!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 10:21:29

Try using COALESCE

SELECT
CASE 
WHEN vbig.name is null 
THEN ss.newuser
Else COALESCE( transit.name , transit2.name , transit3.name , transit4.name,'Other' ) 
END AS Username
from table

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-28 : 10:27:58

Hi,

The thing I wish to do is like:
if transit.name is null
then transit2.name
elseif transit.name is null and transit2.name is Null
then transit3.name
elseif transit.name is null and transit2.name is Null and transit3.name
Then transit4.name

That is the kind of thing i wish to do!

quote:

Originally posted by vijayisonly

Try using COALESCE

SELECT
CASE 
WHEN vbig.name is null 
THEN ss.newuser
Else COALESCE( transit.name , transit2.name , transit3.name , transit4.name,'Other' ) 
END AS Username
from table

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 10:30:55

Yes. Thats what COALESCE does. It picks the first non-null value from the list.

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-28 : 10:38:05

Wow you are absolutely right.
I thougth "COALESCE" was meant to concatenate string.
I just learned another thing.