CASE .. WHEN expression in Oracle SQL

Question

I have a table with 1 column and has the following data

Status a1 i t a2 a3 

I want to show the following result in my select query

 Status| STATUSTEXT a1 | Active i | Inactive t | Terminated a2 | Active a3 | Active 

One way I could think of is to use the Switch When statement 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 to do this when I do not need to write When the expression is 3 times for the active state and all the active status can be checked in one single expression?

Answer 1

You can use the 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 

Look at this demo

SQL Fiddle DEMO

Answer 2

Sure...

 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 are a few disturbing things about this scheme. First, if you have a column that means something, adding a number to the end is not necessarily the best way. In addition, depending on the number of statuses, you may consider turning this column into a foreign key into a separate table.

---

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

 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) ) 

Then your request will be

 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.

Answer 3

You can rewrite it to use the ELSE CASE clause:

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

Answer 4

It will be easier to use decode .

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

Answer 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; 

Answer 6

You can only check the first status character. To do this, you use the substring function.

substr (status, 1.1)

In your case, the past.

Answer 7

The following syntax will 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 ) 

Answer 8

 CASE TO_CHAR(META.RHCONTRATOSFOLHA.CONTRATO) WHEN '91' AND TO_CHAR(META.RHCONTRATOSFOLHA.UNIDADE) = '0001' THEN '91RJ' WHEN '91' AND TO_CHAR(META.RHCONTRATOSFOLHA.UNIDADE) = '0002' THEN '91SP' END CONTRATO, 00905. 00000 - "missing keyword" *Cause: *Action: Erro na linha: 15 Coluna: 11 

Answer 9

Since the web search for Oracle case follows this link, I add the case statement here, but I do not answer the question about the 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 another option:

 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; 

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