select case null issue - 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)

select case null issue

Author

Topic

c854104912
Starting Member

2 Posts

Posted - 2009-06-23 : 00:29:11

Hey Guys,

I have query where I'm making several joins across Microsoft's membership, role and profile provider tables. The goal is to return a list of users that includes a column denoting if the user has a record in the aspnet_profiles table but my case statement always evaluates to false. This example will work in SQL 2000 but not in 2005. I'm assuming something implicit is going on here and need a hand.

Thanks,
Chris



select	u.userid
		, UserName
		, LastActivityDate
		, Email
		, IsLockedOut
		, case p.userid
			when null then 0
			else 1
			end HasProfile
from aspnet_Users u
left join aspnet_Membership m
	on u.userid = m.userid
left join aspnet_UsersInRoles uir
	on m.userid = uir.userid
left join aspnet_Roles r
	on uir.roleid = r.roleid
left join dbo.aspnet_Profile p
	on u.userid = p.userid

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-23 : 02:14:11

Change the case statement to the following and try


case NullIf(p.userid,Null)is null then 0
			else 1
			end HasProfile

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-23 : 02:20:44

try this



select	u.userid
		, UserName
		, LastActivityDate
		, Email
		, IsLockedOut
		, case 
			when p.userid is null then 0
			else 1
			end HasProfile
from aspnet_Users u
left join aspnet_Membership m
	on u.userid = m.userid
left join aspnet_UsersInRoles uir
	on m.userid = uir.userid
left join aspnet_Roles r
	on uir.roleid = r.roleid
left join dbo.aspnet_Profile p
	on u.userid = p.userid

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-06-23 : 02:55:57

Problem is with
CASE p.userid WHEN NULL

You can use this only when there is = comparison, for all the conditions (<, >, IN, NOT IN, IS NULL, NOT NULL) you need to change the CASE statment to as suggested by Rakesh
CASE WHEN p.userid IS NULL

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

c854104912
Starting Member

2 Posts

Posted - 2009-06-23 : 09:41:49

raky's solution solved the problem. Thanks!

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-23 : 09:47:14

Welcome...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources