CASE WHEN expression in Oracle SQL

0 votes

I have a table with the information below in its only column.

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

I could think of one method to do it by utilizing a Switch When expression in a 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?

Sep 16 in Database by Kithuzzz
• 12,240 points
12 views

1 answer to this question.

0 votes

Use an IN clause.

Example:

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
answered Sep 17 by narikkadan
• 20,880 points

Related Questions In Database

0 votes
1 answer

SQL Server CASE .. WHEN .. IN statement

Two forms of CASE statements are getting ...READ MORE

answered Feb 8 in Database by Vaani
• 7,020 points
78 views
0 votes
1 answer

two back slash in SQL procedure with Regular expression

It's the escape prefix in regular expressions: ...READ MORE

answered Feb 2 in Database by Vaani
• 7,020 points
511 views
0 votes
1 answer

SQL Switch/Case in 'where' clause

Without a case statement: SELECT column1, ...READ MORE

answered Feb 7 in Database by Vaani
• 7,020 points
119 views
0 votes
0 answers

SQL use CASE statement in WHERE IN clause

Can you use a case statement in ...READ MORE

Aug 9 in Database by Kithuzzz
• 12,240 points
37 views
0 votes
1 answer

How to insert date values into table?

You have to convert the literal to ...READ MORE

answered Feb 18 in Database by Vaani
• 7,020 points
196 views
0 votes
1 answer

How to find top three highest salary in emp table in oracle?

Use this: SELECT *FROM ...READ MORE

answered Sep 18 in Database by narikkadan
• 20,880 points
60 views
0 votes
1 answer

Automating Oracle script with nolio

Depending upon the details of your script ...READ MORE

answered Jul 17, 2018 in Other DevOps Questions by ajs3033
• 7,280 points
360 views
0 votes
1 answer

Is there any boolean type in Oracle database?

Nope. I don't think there is one But ...READ MORE

answered Oct 10, 2018 in Database by Neha
• 6,300 points
325 views
0 votes
1 answer

How to display databases in Oracle 11g using SQL*Plus

A MySQL "database" can be compared to ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
51 views
0 votes
1 answer

Oracle PL/SQL - ORA-01403 "No data found" when using "SELECT INTO"

Just populate the field directly, using ordering ...READ MORE

answered Sep 18 in Database by narikkadan
• 20,880 points
42 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP