Nilesh Barai Nilesh Barai - 7 months ago 23
SQL Question

CASE .. WHEN expression in Oracle SQL

I have the table with 1 column and has following data

Status
a1
i
t
a2
a3


I want to display the following result in my select query

Status| STATUSTEXT
a1 | Active
i | Inactive
t | Terminated
a2 | Active
a3 | Active


One way I could think was using a Switch When expression in select query

SELECT
status,
CASE status
WHEN 'a1' THEN 'Active'
WHEN 'a2' THEN 'Active'
WHEN 'a3' THEN 'Active'
WHEN 'i' THEN 'Inactive'
WHEN 't' THEN 'Terminated'
END AS StatusText
FROM stage.tst


Is there any other way of doing this where I don't need to write When expression 3 times for Active Status and the entire active status can be checked in one single expression?

Answer

You could use an IN clause

Something like

SELECT
  status,
  CASE
    WHEN STATUS IN('a1','a2','a3')
    THEN 'Active'
    WHEN STATUS = 'i'
    THEN 'Inactive'
    WHEN STATUS = 't'
    THEN 'Terminated'
  END AS STATUSTEXT
FROM
  STATUS

Have a look at this demo

SQL Fiddle DEMO

Comments