Solved

Case in Where clause help

Posted on 2009-07-10
12
230 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
New and Previous Values in a Query 7 30
Union 2 queries to a cte (temp table perhaps) 9 41
What's wrong with this T-SQL Foreign Key? 7 51
TSQL query to generate xml 4 46
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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