Solved

sql where statement

Posted on 2014-01-31
4
453 Views
Last Modified: 2014-01-31
HI, NEED THIS TO WORK, PLEASE HELP



DECLARE @sumthing

SELECT * FROM table1
WHERE
    CASE WHEN @sumtin = 1
       THEN (table1.field1 IN ('A', 'B', 'AH') )
       ELSE (table1.field1 NOT IN ('A', 'B', 'AH') ) END
0
Comment
Question by:Ess Kay
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 400 total points
ID: 39825310
you need to write it as below

DECLARE @sumthing

SELECT * FROM table1
WHERE (@sumtin = 1 and (table1.field1 IN ('A', 'B', 'AH') ))
or (@sumtin <> 1 and (table1.field1 NOT IN ('A', 'B', 'AH'))

Open in new window

0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 100 total points
ID: 39825314
Hi esskay,

CASE returns a single value so the structure that you've defined isn't legal SQL.

But you can do it without the CASE construct.

SELECT * FROM table1
WHERE
    (@sumtin = 1 AND table1.field1 IN ('A', 'B', 'AH') )
OR (@sumtin <> 1 AND table1.field1 NOT IN ('A', 'B', 'AH') )


That should do the trick for you.

Good Luck,
Kent
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39825317
The above answer is correct.  CASE blocks work great, but in a WHERE clause it's more (effective, easier to read, ??) just to use logical expressions like AND or OR, and parentheses to set the execution.
0
 
LVL 15

Author Closing Comment

by:Ess Kay
ID: 39825329
Thanks, not enough coffee today
0

Featured Post

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.

Question has a verified solution.

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

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now