Solved

Case in Where clause help

Posted on 2009-07-10
12
229 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

810 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