CASE WHEN in WHERE clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

CASE WHEN in WHERE clause

Author

Topic

ArnoldG
Starting Member

36 Posts

Posted - 2014-06-12 : 08:20:48

Hi,

I would like to set up a conditional WHERE clause.
I have a field "Items.condition" that can contain an 'A', 'D', or several other values.

I would like to filter using 'PARAM1' having possible 2 values:

1. 'PARAM1' = 'ACTIVE' then a field Items.condition should filter on only 'A'
2. 'PARAM1' = 'ALL' then a field Items.condition should filter on 'A' or 'D'

I have figured this out, but that ends in an error:


AND   Items.condition =
  CASE 'PARAM1' 
     WHEN 'Active' THEN 'A'	
	ELSE ('A') OR ('D') -- I know this is not right...
  END

What ELSE syntax should I use here?

Thanks for helping me out...
Arnold

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-12 : 08:24:29

where
(param1 = 'Active' and condition = 'A') or (param1 ='All' and condition in('A','D'))

Too old to Rock'n'Roll too young to die.

ArnoldG
Starting Member

36 Posts

Posted - 2014-06-12 : 09:12:24

Thanks for your help Webfred,

The code works fine, however when I use this WHERE on Items.condition, only the first part returns the right amount of rows.

All rows (so no filter) should be 28102
Items.condition = 'A' should return 20065 rows

Just for testing purposes I simplified the WHERE clause and hard coded the 'PARAM1':

AND ('Active' = 'Active' and Items.condition = 'A') or ('Active' = 'All' and items.condition = 'A') -- returns 20065 rows

AND ('All' = 'Active' and Items.condition = 'A') or ('All' = 'All' and items.condition = 'A') -- returns 23720 rows

In my opinion this should return exactly the same amount of rows.
Any idea how this can happen?

ArnoldG
Starting Member

36 Posts

Posted - 2014-06-12 : 10:07:12

I figured it out myself.
I forgot to put the clause between brackets, so it conflicted with other clauses.
Issue solved.
Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources