Using the CASE Expression : CASE « Query Select « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Query Select
  3. CASE

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:

CASE search_expression
  WHEN expression1 THEN result1
  WHEN expression2 THEN result2
  ...
  WHEN expressionN THEN resultN
  ELSE default_result
END

where

  1. search_expression is the expression to be evaluated.
  2. expression1, expression2, ..., expressionN are the expressions to be evaluated against search_expression.
  3. result1, result2, ..., resultN are the returned results (one for each possible expression). If expression1 evaluates to search_expression, result1 is returned, and so on.
  4. default_result is the default result returned when no matching expression is found.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813

SQL>
SQL> CREATE TABLE book(
  2    title_id   CHAR(3)      NOT NULL,
  3    title_name VARCHAR(40)  NOT NULL,
  4    type       VARCHAR(10)  NULL    ,
  5    pub_id     CHAR(3)      NOT NULL,
  6    pages      INTEGER      NULL    ,
  7    price      DECIMAL(5,2) NULL    ,
  8    sales      INTEGER      NULL    ,
  9    pubdate    DATE         NULL    ,
 10    contract   SMALLINT     NOT NULL
 11  );

Table created.

SQL>
SQL>
SQL>
SQL> INSERT INTO book VALUES('T01','Java','history','P01',111,21.99,566,DATE '2000-08-01',1);

1 row created.

SQL> INSERT INTO book VALUES('T02','Oracle','history','P03', 114,19.95,9566,DATE '1998-04-01',1);

1 row created.

SQL> INSERT INTO book VALUES('T03','SQL','computer','P02', 122,39.95,25667,DATE '2000-09-01',1);

1 row created.

SQL> INSERT INTO book VALUES('T04','C++','psychology','P04', 511,12.99,13001,DATE '1999-05-31',1);

1 row created.

SQL> INSERT INTO book VALUES('T05','Python','psychology','P04', 101,6.95,201440,DATE '2001-01-01',1);

1 row created.

SQL> INSERT INTO book VALUES('T06','JavaScript','biography','P01', 173,19.95,11320,DATE '2000-07-31',1);

1 row created.

SQL> INSERT INTO book VALUES('T07','LINQ','biography','P03', 331,23.95,1500200,DATE '1999-10-01',1);

1 row created.

SQL> INSERT INTO book VALUES('T08','C#','children','P04', 861,10.00,4095,DATE '2001-06-01',1);

1 row created.

SQL> INSERT INTO book VALUES('T09','SQL Server','children','P04', 212,13.95,5000,DATE '2002-05-31',1);

1 row created.

SQL> INSERT INTO book VALUES('T10','AJAX','biography','P01', NULL,NULL,NULL,NULL,0);

1 row created.

SQL> INSERT INTO book VALUES('T11','VB','psychology','P04', 821,7.99,94123,DATE '2000-11-30',1);

1 row created.

SQL> INSERT INTO book VALUES('T12','Office','biography','P01', 507,12.99,100001,DATE '2000-08-31',1);

1 row created.

SQL> INSERT INTO book VALUES('T13','VBA','history','P03', 812,29.99,10467,DATE '1999-05-31',1);

1 row created.

SQL>
SQL>
SQL> SELECT
  2      title_id,
  3      type,
  4      price,
  5      CASE type
  6        WHEN 'history'
  7          THEN price * 1.10
  8        WHEN 'psychology'
  9          THEN price * 1.20
 10        ELSE price
 11      END
 12        AS "New price"
 13    FROM book
 14    ORDER BY type ASC, title_id ASC;

TIT TYPE            PRICE  New price
--- ---------- ---------- ----------
T06 biography       19.95      19.95
T07 biography       23.95      23.95
T10 biography
T12 biography       12.99      12.99
T08 children           10         10
T09 children        13.95      13.95
T03 computer        39.95      39.95
T01 history         21.99     24.189
T02 history         19.95     21.945
T13 history         29.99     32.989
T04 psychology      12.99     15.588

TIT TYPE            PRICE  New price
--- ---------- ---------- ----------
T05 psychology       6.95       8.34
T11 psychology       7.99      9.588

13 rows selected.

SQL>
SQL> drop table book;

Table dropped.

SQL>
SQL>








2.15.CASE
2.15.1.Using the CASE Expression
2.15.2.The following example illustrates the use of a simple CASE expression:
2.15.3.Using Searched CASE Expressions
2.15.4.The following example illustrates the use of a searched CASE expression:
2.15.5.Use logical operators in a searched CASE expression
2.15.6.Use CASE statement to deal with NULL
2.15.7.Use case when and grouping function together
2.15.8.Use case when clause to decode value
2.15.9.Use case when statement with between ... and
2.15.10.Use case when statement with exists and subquery
2.15.11.Use case when statement with in()
2.15.12.Use case when statement with to_char() like
2.15.13.Use case when with comparasion operator
2.15.14.Use Case to output null value
2.15.15.Wrap case when into sum() function