Solved

sql server using case statement in where clause

Posted on 2016-08-08
3
69 Views
Last Modified: 2016-08-08
currently I have a where clause that looks like this:
where rs.status in ('Open', 'Confirmed', 'Arrived')

this is what I'm trying:
where case
     when @hp=1 then in ('Open', 'Confirmed', 'Arrived')
     else in ('Open', 'Confirmed', 'Arrived', 'High Probability')
     end

what am I doing wrong?
0
Comment
Question by:dhenderson12
  • 2
3 Comments
 

Author Comment

by:dhenderson12
ID: 41747505
sorry, mis-typed.
this is what I'm trying:
where case
     when @hp=1 then rs.status in ('Open', 'Confirmed', 'Arrived')
     else rs.status in ('Open', 'Confirmed', 'Arrived', 'High Probability')
     end
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41747518
Give this a whirl..
WHERE
   ( @hp = 1 AND rs.status in ('Open', 'Confirmed', 'Arrived')) OR 
   ( @hp <> 1 AND rs.status in ('Open', 'Confirmed', 'Arrived', 'High Probability'))

Open in new window

0
 

Author Comment

by:dhenderson12
ID: 41747592
sorry, that didn't work ... got the same records.  ended up doing an "IF / ELSE" statement .
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

832 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