Solved

Case in Where clause help

Posted on 2009-07-10
12
228 Views
Last Modified: 2012-05-07
I keep getting an error at the ">" in this statement.  What am I doing wrong?

Thaks,
-Matt
select vUser_Info.UserID, First_Name, Last_Name, EMail, vUser_Roles.Role_Name, Status, Corporate_Name

from dbo.vUser_Info, dbo.vCorporates, dbo.vUser_Roles

where vUser_Info.Corporate = vCorporates.CorporateID

and vUser_Info.RoleID = vUser_Roles.RoleID

and vCorporates.CorporateID = 3

and vUser_Info.RoleID = case 

when @RoleID = 0 then vUser_Info.RoleID > 0

when @RoleID <> 0 then vUser_Info.RoleID = vUser_Info.RoleID

end

order by vUser_Info.UserID

Open in new window

0
Comment
Question by:mattkovo
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 17

Accepted Solution

by:
pssandhu earned 400 total points
ID: 24826375
Try it this way:
select vUser_Info.UserID, First_Name, Last_Name, EMail, vUser_Roles.Role_Name, Status, Corporate_Name

from dbo.vUser_Info, dbo.vCorporates, dbo.vUser_Roles

where vUser_Info.Corporate = vCorporates.CorporateID

	and vUser_Info.RoleID = vUser_Roles.RoleID

	and vCorporates.CorporateID = 3

	and (case when @RoleID = 0 then vUser_Info.RoleID > 0

		when @RoleID <> 0 then vUser_Info.RoleID = vUser_Info.RoleID

		end)

order by vUser_Info.UserID

Open in new window

0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24826394
Oopss... sorry thtas wrong. Following up...
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24826415
Can you please explain what are you trying to achieve with the CASE statement?
P.
0
 

Author Comment

by:mattkovo
ID: 24826436
I am trying to display a list of items based on the variable passed. I could use the case statement in a stored procedure to determine the select statement to use. But...I'd rater use one statement if possible.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24826507
So would something like this work for you:
IF @RoleID = 0

	BEGIN

		select	vUser_Info.UserID, 

				First_Name, Last_Name, 

				EMail, 

				vUser_Roles.Role_Name, 

				Status, 

				Corporate_Name

		from	dbo.vUser_Info, dbo.vCorporates, dbo.vUser_Roles

		where	vUser_Info.Corporate = vCorporates.CorporateID

				and vUser_Info.RoleID = vUser_Roles.RoleID

				and vCorporates.CorporateID = 3

				and vUser_Info.RoleID > 0

		order by vUser_Info.UserID

	END

ELSE

	BEGIN

		select	vUser_Info.UserID, 

				First_Name, Last_Name, 

				EMail, 

				vUser_Roles.Role_Name, 

				Status, 

				Corporate_Name

		from	dbo.vUser_Info, dbo.vCorporates, dbo.vUser_Roles

		where	vUser_Info.Corporate = vCorporates.CorporateID

				and vUser_Info.RoleID = vUser_Roles.RoleID

				and vCorporates.CorporateID = 3

		order by vUser_Info.UserID

	END

Open in new window

0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 25 total points
ID: 24826541
Maybe pssandhu has a lock on your problem but I must admit I still don't understand the requirements.

If @RoleID = 0 then what?

If @RoleID is not 0 then what?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:pssandhu
ID: 24826622
Yes, it is a bit confusing. But when I look at his CASE statment he wants to pick the role ids that are greater than 0 when the variable value is 0 and if not then roleid = roleid, which to me means that it does not matter.
May be I am reading it wrong as well!! :-)
P.
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 75 total points
ID: 24826646
It looks like you just need this:
select vUser_Info.UserID, First_Name, Last_Name, EMail, vUser_Roles.Role_Name, Status, Corporate_Name

from dbo.vUser_Info, dbo.vCorporates, dbo.vUser_Roles

where vUser_Info.Corporate = vCorporates.CorporateID

and vUser_Info.RoleID = vUser_Roles.RoleID

and vCorporates.CorporateID = 3

and (@RoleID <> 0 OR vUser_Info.RoleID > 0)

order by vUser_Info.UserID

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24826657
That is how I am reading it too P.
0
 

Author Comment

by:mattkovo
ID: 24826785
P.  You are correct as what I'm trying to accomplish.  I think I found a way to do it but will get back soon. Thanks all!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24826810
It is a simple statement if that is what you want to do.  Don't need a CASE at all.  See http:#24826646.
0
 

Author Closing Comment

by:mattkovo
ID: 31602222
I used a combo of ideas and came up with a different way to solve the problem.  Thanks guys!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now