Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Case in Where clause help

Posted on 2009-07-10
12
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 17

Accepted Solution

by:
pssandhu earned 1200 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 75 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
 
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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 225 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 60

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 60

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

604 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