Solved

Case in Where clause help

Posted on 2009-07-10
12
232 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 60

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

726 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