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..

Solution 1

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).

Solution 2

Change to:

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

Solution 3

Try this.

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

Solution 4

which platform ?

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

View more solutions

Comments

[Hogan]

this won't have a column name.

[Taryn]

This could also be written with CASE FRUIT WHEN 'A'...

[everton]

Not a requirement

[Mauro Candido]

I had to convert to Upper, because of the Case Sensitive and bingo.