Oracle PL/SQL Tutorial/Query Select/CASE

Материал из SQL эксперт
Перейти к: навигация, поиск

The following example illustrates the use of a searched CASE expression:

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SELECT id,

 2   CASE
 3    WHEN id = 1 THEN "E"
 4    WHEN id = 2 THEN "D"
 5    WHEN id = 3 THEN "C"
 6    WHEN id = 4 THEN "D"
 7    ELSE "Default"
 8   END
 9  FROM employee;

ID CASEWHE


-------

01 E 02 D 03 C 04 D 05 Default 06 Default 07 Default 08 Default 8 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>


The following example illustrates the use of a simple CASE expression:

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SELECT id,

 2   CASE id
 3    WHEN "01" THEN "A"
 4    WHEN "02" THEN "B"
 5    WHEN "03" THEN "C"
 6    WHEN "04" THEN "D"
 7    ELSE "Default"
 8   END
 9  FROM employee;

ID CASEIDW


-------

01 A 02 B 03 C 04 D 05 Default 06 Default 07 Default 08 Default 8 rows selected. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>


Use CASE statement to deal with NULL

   <source lang="sql">

SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060

725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860

221","YYYYMMDD"), 6661.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900

315","YYYYMMDD"), 6544.78, NULL,"Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990

421","YYYYMMDD"), 2344.78, "Vancouver","Manager")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980

808","YYYYMMDD"), 2334.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960

104","YYYYMMDD"), 4322.78,"New York", "Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980

212","YYYYMMDD"), 7897.78,"New York", "Manager")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020

415","YYYYMMDD"), 1232.78,NULL, "Tester")

 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Tester 8 rows selected. SQL> SQL> SQL> SELECT count(*),

 2    CASE NVL(city,"x")
 3      WHEN "x" then "Null"
 4      ELSE city
 5      END CITY -- CASE
 6  FROM employee
 7  GROUP BY city;
 COUNT(*) CITY

----------
        2 Null
        2 New York
        1 Toronto
        3 Vancouver

SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>


Use Case to output null value

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060

725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860

221","YYYYMMDD"), 6661.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900

315","YYYYMMDD"), 6544.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990

421","YYYYMMDD"), 2344.78, "Vancouver","Manager")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980

808","YYYYMMDD"), 2334.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960

104","YYYYMMDD"), 4322.78,"New York", "Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980

212","YYYYMMDD"), 7897.78,"New York", "Manager")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020

415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")

 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SELECT count(*),

 2    CASE City
 3      WHEN "Vancouver" then "No"
 4      WHEN "New York" then "Yes"
 5      WHEN null then "Null"
 6    END CITY -- CASE
 7  FROM employee
 8  GROUP BY city;
 COUNT(*) CITY

----
        2 Yes
        1
        5 No

SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL> SQL></source>


Use case when and grouping function together

   <source lang="sql">

SQL> SQL> create table emp

 2  ( empno      NUMBER(4)    constraint E_PK primary key
 3  , ename      VARCHAR2(8)
 4  , init       VARCHAR2(5)
 5  , job        VARCHAR2(8)
 6  , mgr        NUMBER(4)
 7  , bdate      DATE
 8  , sal       NUMBER(6,2)
 9  , comm       NUMBER(6,2)
10  , deptno     NUMBER(2)    default 10
11  ) ;

Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> select deptno

 2  ,        case grouping(job)
 3                when 0 then job
 4                when 1 then "total"
 5           end  job
 6  ,        count(empno) headcount
 7  from     emp
 8  group by ROLLUP(deptno, job);
   DEPTNO JOB       HEADCOUNT

-------- ----------
       10 Manager           1
       10 Designer          2
       10 total             3
       20 TRAINER           4
       20 Designer          1
       20 total             5
       30 Tester            4
       30 Manager           1
       30 Designer          1
       30 total             6
          total            14

11 rows selected. SQL> SQL> SQL> drop table emp; Table dropped. SQL></source>


Use case when clause to decode value

   <source lang="sql">

SQL> create table registrations

 2  ( attendee    NUMBER(4)
 3  , course      VARCHAR2(6)
 4  , begindate   DATE
 5  , evaluation  NUMBER(1)
 6  , constraint  R_PK        primary key (attendee,course,begindate)
 7  ) ;

Table created. SQL> SQL> insert into registrations values (2,"SQL",date "2009-04-12",4 ); 1 row created. SQL> insert into registrations values (14,"SQL",date "2009-04-12",5 ); 1 row created. SQL> insert into registrations values (6,"SQL",date "2009-04-12",4 ); 1 row created. SQL> insert into registrations values (11,"SQL",date "2009-04-12",2 ); 1 row created. SQL> insert into registrations values (8,"SQL",date "2009-10-04",NULL); 1 row created. SQL> insert into registrations values (9,"SQL",date "2009-10-04",3 ); 1 row created. SQL> insert into registrations values (13,"SQL",date "2009-10-04",4 ); 1 row created. SQL> insert into registrations values (13,"SQL",date "2009-12-13",NULL); 1 row created. SQL> insert into registrations values (6,"SQL",date "2009-12-13",NULL); 1 row created. SQL> insert into registrations values (3,"OAU",date "2009-08-10",4 ); 1 row created. SQL> insert into registrations values (12,"OAU",date "2009-08-10",4 ); 1 row created. SQL> insert into registrations values (13,"OAU",date "2009-08-10",5 ); 1 row created. SQL> SQL> select attendee, begindate

 2  ,      case evaluation
 3              when 1 then "bad"
 4              when 2 then "mediocre"
 5              when 3 then "ok"
 6              when 4 then "good"
 7              when 5 then "excellent"
 8                     else "not filled in"
 9         end
10  from   registrations
11  where  course = "SQL";
 ATTENDEE BEGINDATE  CASEEVALUATIO

---------- -------------
        2 12-04-2009 good
       14 12-04-2009 excellent
        6 12-04-2009 good
       11 12-04-2009 mediocre
        8 04-10-2009 not filled in
        9 04-10-2009 ok
       13 04-10-2009 good
       13 13-12-2009 not filled in
        6 13-12-2009 not filled in

9 rows selected. SQL> SQL> SQL> drop table registrations; Table dropped.</source>


Use case when statement with between ... and

   <source lang="sql">

SQL> SQL> create table emp(

 2            emp_no                 integer         primary key
 3           ,lastname               varchar2(20)    not null
 4           ,firstname              varchar2(15)    not null
 5           ,midinit                varchar2(1)
 6           ,street                 varchar2(30)
 7           ,city                   varchar2(20)
 8           ,state                  varchar2(2)
 9           ,zip                    varchar2(5)
10           ,shortZipCode           varchar2(4)
11           ,area_code              varchar2(3)
12           ,phone                  varchar2(8)
13           ,salary                 number(5,2)
14            ,birthdate              date
15           ,startDate              date
16           ,title                  varchar2(20)
17           ,dept_no                integer
18           ,mgr                    integer
19           ,region                 number
20           ,division               number
21           ,total_sales            number
22    );

Table created. SQL> SQL> SQL> SQL> SQL> -- emp Table Inserts: SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values

 2                       (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");

1 row created. SQL> SQL> SQL> SQL> SQL> create table avg_sal

 2   as select avg(salary) AS avg_Sal from emp;

Table created. SQL> SQL> SQL> select

 2   case when salary between 6 and 8 then "6-8"
 3        when salary in (9,10)       then "9-10"
 4        when exists (select null from avg_sal where avg_sal = salary)
 5                                    then "EXISTS"
 6        when to_char(salary) like "2%" then "Like2"
 7        when salary is null then "Null"
 8        else "ELSE Empno: "|| emp_no
 9        end
10        AS case_test
11   from emp
12   /

CASE_TEST


Null SQL> SQL> SQL> drop table avg_sal; Table dropped. SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL></source>


Use case when statement with exists and subquery

   <source lang="sql">

SQL> SQL> create table emp(

 2            emp_no                 integer         primary key
 3           ,lastname               varchar2(20)    not null
 4           ,firstname              varchar2(15)    not null
 5           ,midinit                varchar2(1)
 6           ,street                 varchar2(30)
 7           ,city                   varchar2(20)
 8           ,state                  varchar2(2)
 9           ,zip                    varchar2(5)
10           ,shortZipCode           varchar2(4)
11           ,area_code              varchar2(3)
12           ,phone                  varchar2(8)
13           ,salary                 number(5,2)
14            ,birthdate              date
15           ,startDate              date
16           ,title                  varchar2(20)
17           ,dept_no                integer
18           ,mgr                    integer
19           ,region                 number
20           ,division               number
21           ,total_sales            number
22    );

Table created. SQL> SQL> SQL> SQL> SQL> -- emp Table Inserts: SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values

 2                       (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");

1 row created. SQL> SQL> SQL> SQL> SQL> create table avg_sal

 2   as select avg(salary) AS avg_Sal from emp;

Table created. SQL> SQL> SQL> select

 2   case when salary between 6 and 8 then "6-8"
 3        when salary in (9,10)       then "9-10"
 4        when exists (select null from avg_sal where avg_sal = salary)
 5                                    then "EXISTS"
 6        when to_char(salary) like "2%" then "Like2"
 7        when salary is null then "Null"
 8        else "ELSE Empno: "|| emp_no
 9        end
10        AS case_test
11   from emp
12   /

CASE_TEST


Null SQL> SQL> SQL> drop table avg_sal; Table dropped. SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL></source>


Use case when statement with in()

   <source lang="sql">

SQL> SQL> create table emp(

 2            emp_no                 integer         primary key
 3           ,lastname               varchar2(20)    not null
 4           ,firstname              varchar2(15)    not null
 5           ,midinit                varchar2(1)
 6           ,street                 varchar2(30)
 7           ,city                   varchar2(20)
 8           ,state                  varchar2(2)
 9           ,zip                    varchar2(5)
10           ,shortZipCode           varchar2(4)
11           ,area_code              varchar2(3)
12           ,phone                  varchar2(8)
13           ,salary                 number(5,2)
14            ,birthdate              date
15           ,startDate              date
16           ,title                  varchar2(20)
17           ,dept_no                integer
18           ,mgr                    integer
19           ,region                 number
20           ,division               number
21           ,total_sales            number
22    );

Table created. SQL> SQL> SQL> SQL> SQL> -- emp Table Inserts: SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values

 2                       (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");

1 row created. SQL> SQL> SQL> SQL> SQL> create table avg_sal

 2   as select avg(salary) AS avg_Sal from emp;

Table created. SQL> SQL> SQL> select

 2   case when salary between 6 and 8 then "6-8"
 3        when salary in (9,10)       then "9-10"
 4        when exists (select null from avg_sal where avg_sal = salary)
 5                                    then "EXISTS"
 6        when to_char(salary) like "2%" then "Like2"
 7        when salary is null then "Null"
 8        else "ELSE Empno: "|| emp_no
 9        end
10        AS case_test
11   from emp
12   /

CASE_TEST


Null SQL> SQL> SQL> drop table avg_sal; Table dropped. SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL></source>


Use case when statement with to_char() like

   <source lang="sql">

SQL> SQL> create table emp(

 2            emp_no                 integer         primary key
 3           ,lastname               varchar2(20)    not null
 4           ,firstname              varchar2(15)    not null
 5           ,midinit                varchar2(1)
 6           ,street                 varchar2(30)
 7           ,city                   varchar2(20)
 8           ,state                  varchar2(2)
 9           ,zip                    varchar2(5)
10           ,shortZipCode           varchar2(4)
11           ,area_code              varchar2(3)
12           ,phone                  varchar2(8)
13           ,salary                 number(5,2)
14            ,birthdate              date
15           ,startDate              date
16           ,title                  varchar2(20)
17           ,dept_no                integer
18           ,mgr                    integer
19           ,region                 number
20           ,division               number
21           ,total_sales            number
22    );

Table created. SQL> SQL> SQL> SQL> SQL> -- emp Table Inserts: SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values

 2                       (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");

1 row created. SQL> SQL> SQL> SQL> SQL> create table avg_sal

 2   as select avg(salary) AS avg_Sal from emp;

Table created. SQL> SQL> SQL> select

 2   case when salary between 6 and 8 then "6-8"
 3        when salary in (9,10)       then "9-10"
 4        when exists (select null from avg_sal where avg_sal = salary)
 5                                    then "EXISTS"
 6        when to_char(salary) like "2%" then "Like2"
 7        when salary is null then "Null"
 8        else "ELSE Empno: "|| emp_no
 9        end
10        AS case_test
11   from emp
12   /

CASE_TEST


Null SQL> SQL> SQL> drop table avg_sal; Table dropped. SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL></source>


Use case when with comparasion operator

   <source lang="sql">

SQL> SQL> create table emp(

 2            emp_no                 integer         primary key
 3           ,lastname               varchar2(20)    not null
 4           ,firstname              varchar2(15)    not null
 5           ,midinit                varchar2(1)
 6           ,street                 varchar2(30)
 7           ,city                   varchar2(20)
 8           ,state                  varchar2(2)
 9           ,zip                    varchar2(5)
10           ,shortZipCode                  varchar2(4)
11           ,area_code              varchar2(3)
12           ,phone                  varchar2(8)
13           ,salary                 number(5,2)
14            ,birthdate              date
15           ,startDate               date
16           ,title                  varchar2(20)
17           ,dept_no                integer
18           ,mgr                    integer
19           ,region                 number
20           ,division               number
21           ,total_sales            number
22    );

Table created. SQL> SQL> SQL> SQL> SQL> SQL> create table avg_sal

 2   as select avg(salary) AS avg_Sal from emp;

Table created. SQL> SQL> select lastname, salary,

 2   CASE WHEN salary > avg_sal THEN "> Average of " || to_char(avg_sal, "99.99")
 3        WHEN salary < avg_sal THEN "< Average of " || to_char(avg_sal, "99.99")
 4        ELSE "= Average of " || to_char(avg_sal, "99.99")
 5        END
 6   from emp, avg_sal
 7   /

no rows selected SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL> SQL> SQL> drop table avg_Sal; Table dropped. SQL></source>


Use logical operators in a searched CASE expression

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SELECT id, salary,

 2   CASE
 3    WHEN salary > 45000 THEN "HIGH"
 4    ELSE "LOW"
 5   END
 6  FROM employee;

ID SALARY CASE


---------- ----

01 1234.56 LOW 02 6661.78 LOW 03 6544.78 LOW 04 2344.78 LOW 05 2334.78 LOW 06 4322.78 LOW 07 7897.78 LOW 08 1232.78 LOW 8 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>


Using Searched CASE Expressions

Searched CASE expressions use conditions to determine the returned value and have the following syntax:



   <source lang="sql">

CASE

 WHEN condition1 THEN result1
 WHEN condition2 THEN result2
 ...
 WHEN conditionN THEN resultN
 ELSE default_result

END</source>


where

  1. condition1, condition2, ..., conditionN are the expressions to be evaluated.
  2. result1, result2, ..., resultN are the returned results (one for each possible condition).
  3. If condition1 is true, result1 is returned, and so on.
  4. default_result is the default result returned when no true condition is found.

Using the CASE Expression

CASE expression perform if-then-else logic in SQL without having to use PL/SQL.

CASE works in a similar manner to DECODE().

CASE is ANSI-compliant.

There are two types of CASE expressions:

  1. Simple case expressions use expressions to determine the returned value.
  2. Searched case expressions use conditions to determine the returned value.

Simple CASE expressions use expressions to determine the returned value and have the following syntax:



   <source lang="sql">

CASE search_expression

 WHEN expression1 THEN result1
 WHEN expression2 THEN result2
 ...
 WHEN expressionN THEN resultN
 ELSE default_result

END</source>


Wrap case when into sum() function

   <source lang="sql">

SQL> SQL> create table emp(

 2           emp_no                 integer         primary key
 3          ,lastname               varchar2(20)    not null
 4          ,firstname              varchar2(15)    not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,shortZipCode           varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,salary                 number(5,2)
14          ,birthdate              date
15          ,startDate              date
16          ,title                  varchar2(20)
17          ,dept_no                integer
18          ,mgr                    integer
19          ,region                 number
20          ,division               number
21          ,total_sales            number
22  );

Table created. SQL> SQL> -- emp Table Inserts: SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values

 2                      (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");

1 row created. SQL> SQL> SQL> select

 2   sum ( case when salary between 6 and 10 then 1 else 0 end )
 3        as sal_6_10,
 4   sum ( case when salary between 11 and 15 then 1 else 0 end )
 5        as sal_11_15,
 6   sum ( case when salary between 16 and 20 then 1 else 0 end )
 7        as sal_16_20,
 8   sum ( case when salary between 21 and 25 then 1 else 0 end )
 9        as sal_21_25
10  from emp;
 SAL_6_10  SAL_11_15  SAL_16_20  SAL_21_25

---------- ---------- ----------
        0          0          0          0

1 row selected. SQL> drop table emp; Table dropped.</source>