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, 2022 in Database by Kithuzzz
• 38,020 points
570 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, 2022 by narikkadan
• 63,680 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, 2022 in Database by Vaani
• 7,070 points
674 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, 2022 in Database by Vaani
• 7,070 points
1,620 views
0 votes
1 answer

SQL Switch/Case in 'where' clause

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

answered Feb 7, 2022 in Database by Vaani
• 7,070 points
1,415 views
0 votes
0 answers

SQL use CASE statement in WHERE IN clause

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

Aug 9, 2022 in Database by Kithuzzz
• 38,020 points
634 views
0 votes
1 answer

How to insert date values into table?

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

answered Feb 18, 2022 in Database by Vaani
• 7,070 points
3,125 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, 2022 in Database by narikkadan
• 63,680 points
8,997 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,300 points
1,007 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
965 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, 2022 in Database by narikkadan
• 63,680 points
3,223 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, 2022 in Database by narikkadan
• 63,680 points
5,492 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