CASE .. WHEN expression in Oracle SQL

Question

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 1

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