CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Nilesh Barai
Nilesh Barai

Reputation: 1167

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?

Upvotes: 100

Views: 893041

Answers (8)

sbrbot
sbrbot

Reputation: 6447

DECODE(SUBSTR(STATUS,1,1),'a','Active','i','Inactive','t','Terminated','N/A')

Upvotes: 0

Alex Martian
Alex Martian

Reputation: 3782

Since web search for Oracle case tops to that link, I add here for case statement, though not answer to the question asked about case expression:

CASE
   WHEN grade = 'A' THEN dbms_output.put_line('Excellent');
   WHEN grade = 'B' THEN dbms_output.put_line('Very Good');
   WHEN grade = 'C' THEN dbms_output.put_line('Good');
   WHEN grade = 'D' THEN dbms_output.put_line('Fair');
   WHEN grade = 'F' THEN dbms_output.put_line('Poor');
   ELSE dbms_output.put_line('No such grade');
END CASE;

or other variant:

CASE grade
   WHEN 'A' THEN dbms_output.put_line('Excellent');
   WHEN 'B' THEN dbms_output.put_line('Very Good');
   WHEN 'C' THEN dbms_output.put_line('Good');
   WHEN 'D' THEN dbms_output.put_line('Fair');
   WHEN 'F' THEN dbms_output.put_line('Poor');
   ELSE dbms_output.put_line('No such grade');
END CASE;

Per Oracle docs: https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/04_struc.htm

Upvotes: 4

Thunder
Thunder

Reputation: 10986

It will be easier to do using decode.

SELECT
  status,
    decode ( status, 'a1','Active',
                     'a2','Active',
                     'a3','Active',
                     'i','Inactive',
                     't','Terminated',
                     'Default')STATUSTEXT
FROM STATUS

Upvotes: 9

Adriaan Stander
Adriaan Stander

Reputation: 166396

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

Upvotes: 157

Smart Coder
Smart Coder

Reputation: 1738

Following syntax would work :

....
where x.p_NBR =to_number(substr(y.k_str,11,5))
and x.q_nbr = 
 (case 
 when instr(substr(y.m_str,11,9),'_') = 6   then  to_number(substr(y.m_str,11,5))
 when instr(substr(y.m_str,11,9),'_') = 0   then  to_number(substr(y.m_str,11,9))
  else 
       1
  end
)

Upvotes: 1

Ben
Ben

Reputation: 52863

Of course...

select case substr(status,1,1) -- you're only interested in the first character.
            when 'a' then 'Active'
            when 'i' then 'Inactive'
            when 't' then 'Terminated'
       end as statustext
  from stage.tst

However, there's a few worrying things about this schema. Firstly if you have a column that means something, appending a number onto the end it not necessarily the best way to go. Also, depending on the number of status' you have you might want to consider turning this column into a foreign key to a separate table.

Based on your comment you definitely want to turn this into a foreign key. For instance

create table statuses ( -- Not a good table name :-)
    status varchar2(10)
  , description varchar2(10)
  , constraint pk_statuses primary key (status)
    )

create table tst (
    id number
  , status varchar2(10)
  , constraint pk_tst primary key (id)
  , constraint fk_tst foreign key (status) references statuses (status)
    )

Your query then becomes

select a.status, b.description
  from tst a
  left outer join statuses b
    on a.status = b.status

Here's a SQL Fiddle to demonstrate.

Upvotes: 21

Timo Hahn
Timo Hahn

Reputation: 2496

You can only check the first character of the status. For this you use substring function.

substr(status, 1,1)

In your case past.

Upvotes: 0

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

You can rewrite it to use the ELSE condition of a CASE:

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

Upvotes: 21

Related Questions