Case statement with 'OR' statement

Question

SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' or 'r' THEN 'Road'
         WHEN 'M' or 'm' THEN 'Mountain'
         WHEN 'T' or 't' THEN 'Touring'
         WHEN 'S' or 's' THEN 'Other sale items'
         ELSE 'Not for sale'

My basic requirement is to use 'OR' with CASE (if possible) , Please suggest if i am doing anything wrong.

you are missing the `END` on the `CASE` statement, but it looks fine. — Taryn, Oct 25 '12 at 16:31
I agree with bluefeet, you are simply missing `END` in your `CASE` syntax — Anthony Hatzopoulos, Oct 25 '12 at 16:39
If this list grows any longer, create another table to use as a lookup. You won't have to alter your code for each new sales item. — JeffO, Oct 25 '12 at 17:04

score 5 · Accepted Answer · answered Oct 25 '12 at 16:32

5

A different way to write case comes close, but then you have to repeat the variable:

CASE
  WHEN ProductLine in ('R', 'r') THEN 'Road'
  WHEN ProductLine in ('M', 'm') THEN 'Mountain'

Or a like character class:

CASE
  WHEN ProductLine like '[Rr]' THEN 'Road'
  WHEN ProductLine like '[Mm]' THEN 'Mountain'

Note that in SQL, string comparison is case-insensitive by default. You can alter the default by setting a case-sensitive database collation, but that's fairly unusual.

answered Oct 25 '12 at 16:32

Andomar

232,371
49
380
404

You're also missing `END`. The syntax is `CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END ` – Anthony Hatzopoulos Oct 25 '12 at 16:38
Thanks Andomar, it seems gud and my prob will be solved if i use case in this way ... THANKS – ashish Oct 25 '12 at 16:44

score 0 · Answer 2 · answered Oct 25 '12 at 16:35

0

you can also write

select
    ProductNumber,
    case upper(ProductLine)
        when 'R' then 'Road'
        when 'M' then 'Mountain'
        when 'T' then 'Touring'
        when 'S' then 'Other sale items'
        else 'Not for sale'
    end as Category

You can set your collation to case insensitive and just compare ProductLine with letters

answered Oct 25 '12 at 16:35

Roman Pekar

107,110
28
195
197

Thanks Roman Pekar , but my requirement will be solved if i use 'OR' with case statement as i have mentioned in the sample code, My real problem is different than i mentioned in the sample code .. – ashish Oct 25 '12 at 16:40

Kaf · Answer 3 · 2012-10-25T16:43:44.900

0

Simply you could do as;

SELECT   ProductNumber, CASE LOWER(ProductLine) 
                            WHEN 'r' THEN 'Road'
                            WHEN 'm' THEN 'Mountain'
                        END AS Category
FROM TableName

For more details: Click here

edited Oct 25 '12 at 16:43

answered Oct 25 '12 at 16:37

Kaf

33,101
7
58
78

score 0 · Answer 4 · edited May 23 '17 at 11:48

0

I would recommend case fall-through, but that's not allowed in SQL. However, there's a very good answer on SQL's work-around for fall-through: Can the SQL Case Statment fall through?

edited May 23 '17 at 11:48

Community

1
1

answered Oct 25 '12 at 16:37

LConrad

816
1
11
20

Case statement with 'OR' statement

4 Answers4