Solved

Case in Where clause help

Posted on 2009-07-10
12
231 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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 …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

696 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