CODEWITHSUNDEEP
sqlsql-server-2008
user2125348
user2125348

Reputation: 450

SQL CASE statement with OR?

Say I have to following: 

Select OrderID = 
    Case OrderID 
        When 1 Then 'Customer1'
        When 2 Then 'Customer2'
        When 3 Then 'Customer2'
        Else 'Unknown Customer'
    End 
From OrdersPlaced

Is it possible to add an or and do something along the lines of: 

Select OrderID = 
    Case OrderID 
        When 1 Then 'Customer1'
        When 2 Or 3 Then 'Customer2'
        Else 'Unknown Customer'
    End 
From OrdersPlaced

Upvotes: 10

Views: 100235

Answers (4)

Siddhardha
Siddhardha

Reputation: 21

Try this. This would work for your scenario.

Select OrderID = 
    Case
        When OrderID  = 1 Then 'Customer1'
        When OrderID in (2, 3) Then 'Customer2'
        Else 'Unknown Customer'
    End 
From OrdersPlaced

Upvotes: 1

p.s.w.g
p.s.w.g

Reputation: 149040

There are two forms of CASE expression, 'searched' and 'simple'. You can't use an OR with a 'simple' CASE expression, but you can with the 'searched' form:

Case  
    When OrderID = 1 Then 'Customer1'
    When OrderID = 2 Or
         OrderID = 3 Then 'Customer2'
    Else 'Unknown Customer'
End

Or even

Case  
    When OrderID = 1 Then 'Customer1'
    When OrderID IN (2, 3) Then 'Customer2'
    Else 'Unknown Customer'
End

Upvotes: 29

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726839

CASE expression has two kinds of syntax - the simple (i.e. one that you are showing), and the searched, with multiple logical conditions. If you would like to use an OR, you need the second kind:

Select OrderID = 
    Case 
        When OrderID =1 Then 'Customer1'
        When OrderID =2 Or OrderID =3 Then 'Customer2'
        Else 'Unknown Customer'
    End 
From OrdersPlaced

Here is the description from the documentation:

  • The simple CASE expression compares an expression to a set of simple expressions to determine the result.
  • The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Upvotes: 2

Yuriy Galanter
Yuriy Galanter

Reputation: 39777

You can use alternative form of CASE

Select OrderID = 
    Case 
        When OrderID = 1 Then 'Customer1'
        When OrderID = 2 Or OrderID = 3 Then 'Customer2'
        Else 'Unknown Customer'
    End 
From OrdersPlaced

Upvotes: 4

Related Questions