Simple CASE allows you to choose an alternate value instead of using the actual value.

          SELECT CITY,
                 CASE STATE
                      WHEN 'CA'
                           THEN 'CALIFORNIA'
                      WHEN 'FL'
                           THEN 'FLORIDA'
                      WHEN 'LU2'
                           THEN 'LAGRANGE POINT 2'
                      WHEN 'MS1'
                           THEN 'MARS REGION 1'
                      ELSE STATE
                 END
                      AS 'STATE'
            FROM CUSTOMER_TBL
           ORDER BY CITY;

Result:

                     

The CASE function gives you IF-THEN-ELSE capability. It evaluates a list of conditions and returns one value from several possible values. CASE can be used in two ways: simple and search.

Simple CASE compares one value (the input_value) to a list of other values and returns the first match:

                      CASE input_value
                      WHEN match_condition THEN result_value
                      ...
                      ELSE result_value
                      END

Search CASE compares to multiple conditions and returns a result from the first condition that is true:

                      CASE
                      WHEN search_condition THEN result_value
                      ...
                      ELSE result_value
                      END

> For the simple CASE, a match is found when input_value = match_condition.
> For the search CASE, search_condition compares two values with a boolean operator.
> Multiple WHEN conditions are used, but only one ELSE clause is allowed.
> All values must be of the same type or implicitly convertible to the same type.
> Access does not support CASE. Instead it uses SWITCH.
> DB2, MySQL, Oracle, SQL Server, and Sybase support CASE.
> Oracle uses CASE and DECODE.

For example:           SELECT EXTRACT(YEAR FROM DUE_DATE)
                         FROM ACCOUNTS_PAYABLE_TBL;

                       SELECT EXTRACT(YEAR FROM DUE_DATE)
                         FROM ACCOUNTS_PAYABLE_TBL
                        WHERE EXTRACT(YEAR FROM DUE_DATE) = 2051;

                                       or

                       SELECT EXTRACT(YEAR FROM DUE_DATE)
                         FROM ACCOUNTS_PAYABLE_TBL
                       HAVING EXTRACT(YEAR FROM DUE_DATE) = 2051;

The results of scalar functions do not have headings in the result table because they are not existing and unmodified columns in the base table. A scalar argument can be a column name, a column function, or another scalar function.

NULL values that are used in a scalar function will result in a NULL value. If actual values are to be returned, then the column must be defined as NOT NULL WITH DEFAULT so that a default of zero or spaces will be used in the scalar function or a VALUE scalar function may also be used for this purpose.

Built-in functions automate common processes and are identified either as column functions or as scalar functions.> Column functions perform an operation on a column value of each SELECTed row and return one value for all rows.

> Scalar functions can be used to manipulate numbers, strings, and date and time types, as well as retrieve system information. When performing an operation on a column value of each SELECTed row, it returns a value for each row.
Scalar functions can be used in a SELECT, WHERE, or HAVING clause.
Because a scalar function returns a value for each row, it can be included with other SELECTed columns or calculations, but it can not be intermixed with column functions.
A scalar function consists of the function name followed by a pair of parentheses.
Inside the parentheses is an argument which is usually a column name or another scalar function.

The ANSI standard supports a number of useful functions. However, over the years, the various DBMS vendors have implemented functions that were missing from the then existing SQL standards. These functions were developed and implemented differently by each vendor. Eventually, the SQL ANSI standards committees saw a community-wide need for a function and picked a standard which may or may not have matched the vendor’s version. Once the standard had been established, it would normally take several years for the vendors to adopt and implement these new standards. Therefore, each database vendor maintains a large number of their own functions that are outside the scope of the SQL ANSI standards, before the vendor has implemented the new standard. Many of these non-standard functions have been identified in the notes in this course. See the documentation of your vendor’s RDBMS system for details.

Some scalar functions retrieve data from the operating system such as current date, current time, and user ID. These system data functions can be included with normal table queries. When retrieving only systems data, some RDBMS systems do not require a FROM clause, but there are these exceptions:
> For DB2 add the clause FROM SYSIBM.SYSDUMMY1.
> For Oracle add the clause FROM DUAL.