Evaluate multiple expressions in case when

[MrGandalf]

Hi all,

How can I use multiple (boolean) expressions in the where clause? For example:

Select *
From Client
Where case when age>18
then redhair and blueeyes
else blond and greeneyes
end = 1

This gives an error. Can I write in another way?

 

[JamesLean]

For that example you could use:

SELECT * FROM client
WHERE (age > 18
  AND hair = 'red'
  AND eyes = 'blue')
  OR (age <= 18
  AND hair = 'blonde'
  AND eyes = 'green')

--James

 

[MisterMo]

The case clause must be in the select part of he statement

eg

select case when 1 then 'AAA' else 'BBB' end as field1, field2
from my table
where bla bla bla

Mo

 

[SQLBill]

Mo,

Says who? The CASE works just fine in the WHERE clause. I know because I have a couple of scripts that run each day using CASE in the WHERE.

-SQLBill

Posting advice: FAQ481-4875

 

[MrGandalf]

Indeed the case statement can be used in a where clause.

JamesLean. Tx for your input. It is an other way of writing it but I want to keep the CASE WHEN statement.
This is because my actual SQL has nested CASE statements. For each nesting I would have to write ₂n OR statements. Isn't it possible to keep the CASE statement?

 

[Catadmin]

What error are you getting with this? Syntax or other?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[MrGandalf]

CatAdmin. the error: Incorrect syntax near the keyword 'and'

When I try this (without the AND):

Select *
From Client
Where case when age>18
then redhair
else blond
end = 1

it doesn't give an error and returns the result.

 

[Pattycake245]

This could work:

Select *
From Client
Where case when age>18
      then redhair
      else blond
      end = 1
and   case when age>18
      then blueeyes
      else greeneyes
      end = 1

Tim

 

[jbenson001]

Hi all. I have been following this post. What is the end = 1 syntax? I have never seen that.

Thanks.... Jim

 

[Pattycake245]

Jim,

the = 1 is the continuation of the where statement and is outside of the case statement. In the case of my post, if the age of a record was 20 the select statement would look like this, without the case statement:

Select *
From Client
Where redhair = 1
and blueeyes = 1

Tim

 

[jbenson001]

Tim,

Thanks.. took me a little but now I got it. Thanks for the explination.

Jim

 

[MrGandalf]

Tx Patty, will give you the credits. Although i find it a limitation that I can't use multiple boolean expression in a CASE WHEN. e.g. CASE WHEN Y THEN X AND Y...