There are many situations where you might find it useful to alter
    the result of an expression. For example, you might have a table
    column called WORK_STATUS containing the data 0, 1, and 2 meaning
    Inactive, Full time, and Part time, respectively. The single
    character is more efficient to store than the definition of the
    character in the database. However, the definition of the single
    character is not always intuitive.

    There may also be times when you want to generate null
    values based on the information derived from the database or,
    conversely, convert a null value into a more concrete value like
    zero (0).  The CASE expressions provide an easy solution to these
    problems.

    There are two types of CASE expressions:

    o  Simple-matches two value expressions for equality

    o  Searched-allows complex predicate, including subqueries

    An example of the simple case expression follows:

    SQL> SELECT LAST_NAME, FIRST_NAME,
    cont>   CASE STATUS_CODE
    cont>     WHEN '1' THEN 'Full time'
    cont>     WHEN '2' THEN 'Part time'
    cont>     WHEN NULL THEN 'Unknown'
    cont>     ELSE 'Inactive'
    cont>   END
    cont> FROM EMPLOYEES;
     LAST_NAME        FIRST_NAME
     Smith            Terry        Part time
     O'Sullivan       Rick         Full time
       .
       .
       .
     Sarkisian        Dean         Part time
     Stornelli        James        Full time
     Hall             Lawrence     Full time
     Mistretta        Kathleen     Full time
     James            Eric         Inactive
     MacDonald        Johanna      Full time
     Dement           Alvin        Full time
     Blount           Peter        Full time
     Herbener         James        Full time
     Ames             Louie        Full time
    100 rows selected

    When SQL encounters the first WHEN clause that matches the
    primary value expression following the CASE keyword, it evaluates
    the THEN clause. If no matching values are found, the ELSE clause
    is evaluated. If the ELSE clause is missing, NULL is the returned
    value. For example:

    SQL> SELECT PRODUCT_NAME,
    cont>   CASE
    cont>      WHEN QUANTITY <= 0 THEN 'On back order'
    cont>      WHEN QUANTITY > 0 THEN
    cont>         CAST(QUANTITY AS VARCHAR(10)) || ' in stock'
    cont>   END
    cont> FROM INVENTORY;
     PRODUCT_NAME
     Staples-boxes     20 in stock
     Staplers-each     3 in stock
     Tape-rolls        On back order
     Calendars-each    25 in stock
     Tape disp.-each   On back order
     Desk cleaner      NULL
    6 rows selected

    An example of the searched case expression follows:

    SQL> SELECT PRODUCT_NAME,
    cont>   CASE
    cont>      WHEN QUANTITY <= 0 THEN 'On back order'
    cont>      WHEN QUANTITY > 0 THEN
    cont>         CAST(QUANTITY AS VARCHAR(10)) || ' in stock'
    cont>      ELSE                                          -- must be NULL
    cont>        'New Item - awaiting stock'
    cont>   END
    cont> FROM INVENTORY;
     PRODUCT_NAME
     Staples-boxes     20 in stock
     Staplers-each     3 in stock
     Tape-rolls        On back order
     Calendars-each    25 in stock
     Tape disp.-each   On back order
     Desk cleaner      New Item - awaiting stock
    6 rows selected

    The searched case expression allows arbitrary expressions in each
    WHEN clause, as shown in the previous example. The simple case
    expression is a shorthand method of specifying the searched case
    expression.

    For the simple and searched case expressions, the data types of
    the value expressions of the WHEN clause must be comparable, and
    the data types of the value expressions of the THEN clause must
    be comparable.

    All subqueries in a CASE expression are evaluated. It is the
    results of these subqueries that are conditionalized by the CASE
    expression and not the actual evaluation.

    If any subquery (which must return at most a single row and
    column) returns more than one row, the following exception is
    generated:

    %RDB-E-MULTIPLE_MATCH, record selection criteria should identify only one
    record; more than one record found

    A workaround is to add one of the following clauses to the
    subquery:

    o  LIMIT TO 1 ROW

       This ensures that only one row is returned. For example:

          .
          .
          .
       cont> WHEN A IS NOT NULL
       cont> THEN (SELECT A FROM T WHERE B = Y
       cont>       LIMIT TO 1 ROW)
          .
          .
          .

       The WHEN condition ignores this row if it is not valid.

    o  Duplicate the WHEN clause Boolean inside the subquery
       predicate

       For example:

       SQL> --
       SQL> -- Change the following syntax from
       SQL> --
          .
          .
          .
       cont> WHEN A IS NOT NULL
       cont> THEN (SELECT A FROM T WHERE B = Y)
          .
          .
          .
       SQL> --
       SQL> -- to include the Boolean inside the subquery
       SQL> --
          .
          .
          .
       cont> WHEN A IS NOT NULL
       cont> THEN (SELECT A FROM T WHERE B = Y AND A IS NOT NULL)
          .
          .
          .

       In this example, when the WHEN clause evaluates as FALSE, so
       will the WHERE predicate from the subquery and, therefore,
       will return no rows.

    In either of the above cases, the correct results are returned
    from the query.