SELECT using 'CASE' in SQL

Question

I have a set of one to one mappings A -> apple, B-> Banana and like that.. My table has a column with values as A,B,C..

Now I'm trying to use a select statement which will give me the direct result

SELECT 
  CASE 
     WHEN FRUIT = 'A' THEN FRUIT ='APPLE' 
     ELSE WHEN FRUIT ='B' THEN FRUIT ='BANANA'     
 FROM FRUIT_TABLE;

But I'm not getting the correct result, please help me..

aren't you missing an end and an alias at the end of the case — provençal le breton, Jan 23 '14 at 15:52
look at this http://stackoverflow.com/questions/14189216/case-in-select-statement — Zzyrk, Jan 23 '14 at 15:53
This is basic SQL syntax that you could easily Google about a million examples of. Also, you should probably be using a reference/translation table and a `JOIN` here instead of a `CASE` expression. — RBarryYoung, Jan 23 '14 at 15:54
i think you are missing 'end'. Please paste the result what you are getting — Shiwangini, Jul 14 '19 at 03:44

score 47 · Accepted Answer · answered Jan 23 '14 at 15:52

47

This is just the syntax of the case statement, it looks like this.

SELECT 
  CASE 
    WHEN FRUIT = 'A' THEN 'APPLE' 
    WHEN FRUIT = 'B' THEN 'BANANA'     
  END AS FRUIT
FROM FRUIT_TABLE;

As a reminder remember; no assignment is performed the value becomes the column contents. (If you wanted to assign that to a variable you would put it before the CASE statement).

answered Jan 23 '14 at 15:52

Hogan

65,989
10
76
113

6

This could also be written with `CASE FRUIT WHEN 'A'...` – Taryn Jan 23 '14 at 15:57
I had to convert to Upper, because of the Case Sensitive and bingo. – Mauro Candido Aug 28 '20 at 19:57

everton · Answer 2 · 2014-01-24T11:59:41.053

5

Change to:

SELECT 
  CASE 
    WHEN FRUIT = 'A' THEN 'APPLE' 
    WHEN FRUIT = 'B' THEN 'BANANA'     
  END
FROM FRUIT_TABLE;

edited Jan 24 '14 at 11:59

answered Jan 23 '14 at 15:52

everton

7,223
2
27
41

this won't have a column name. – Hogan Jan 23 '14 at 15:54
2

Not a requirement – everton Apr 15 '17 at 02:52

peter.petrov · Answer 3 · 2014-01-23T16:01:24.977

3

Try this.

SELECT 
  CASE 
     WHEN FRUIT = 'A' THEN 'APPLE'
     WHEN FRUIT = 'B' THEN 'BANANA'
     ELSE 'UNKNOWN FRUIT'
  END AS FRUIT
FROM FRUIT_TABLE;

edited Jan 23 '14 at 16:01

answered Jan 23 '14 at 15:54

peter.petrov

36,377
12
75
137

score 1 · Answer 4 · answered Jan 23 '14 at 15:54

1

which platform ?

SELECT 
  CASE 
    WHEN FRUIT = 'A' THEN 'APPLE' 
    ELSE FRUIT ='B' THEN 'BANANA' 
   END AS FRUIT     
FROM FRUIT_TABLE;

answered Jan 23 '14 at 15:54

venergiac

7,051
2
42
69

SELECT using 'CASE' in SQL

4 Answers4

Linked

Related