CASE WHEN expression in Oracle SQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can only check the first character of the status  For this you use substring function    substr status  1 1   In your case past

User · Answer

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

User · Answer

DECODE SUBSTR STATUS 1 1   a   Active   i   Inactive   t   Terminated   N A

User · Answer

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

User · Answer

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

User · Answer

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

[sql] CASE .. WHEN expression in Oracle SQL

Examples related to sql

Examples related to oracle

Examples related to oracle10g