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?

回答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

回答2:

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.

回答3:

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 

回答4:

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

回答5:

SELECT
  STATUS,
  CASE
    WHEN STATUS IN('a1','a2','a3') 
     THEN 'Active'
    WHEN STATUS = 'i' 
     THEN 'Inactive'
    WHEN STATUS = 't'
     THEN 'Terminated'  ELSE null
  END AS STATUSTEXT
FROM
  stage.tst;

回答6:

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

substr(status, 1,1)

In your case past.

回答7:

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
)