Using Conditional Functions

Oracle Database XE provides conditional functions that you can use in your SQL statements to return a value based on multiple search conditions values.

Example: Using the SQL CASE Function shows the use of the SQL CASE functions.

Using the SQL CASE Function

-- CASE can compare a column or expression or search conditions, returning
-- a result when there is a match. CASE is similar to IF_THEN-ELSE logic.
-- In the following, the value of the hire_date column is compared against various
-- dates. When there is a match, the corresponding calculated result is returned, 
-- otherwise the default calculated salary is returned.
SELECT employee_id, hire_date , salary,
  CASE WHEN hire_date < TO_DATE('01-JAN-90') THEN salary*1.20
       WHEN hire_date < TO_DATE('01-JAN-92') THEN salary*1.15
       WHEN hire_date < TO_DATE('01-JAN-94') THEN salary*1.10 
       ELSE salary*1.05 END  "Revised Salary"
  FROM employees;

Example: Using the SQL DECODE Function shows the use of the SQL DECODE functions.

Using the SQL DECODE Function

-- DECODE compares a column or expression to search values, returning a result
-- when there is a match. DECODE is similar to IF_THEN-ELSE logic.
-- In the following, the value of the job_id column is compared against PU_CLERK,
-- SH_CLERK, and ST_CLERK. 
-- When there is a match, the corresponding calculated result is returned,
-- otherwise the original salary is returned unchanged.
SELECT employee_id, job_id , salary,
  DECODE(job_id, 'PU_CLERK', salary*1.05,
                 'SH_CLERK', salary*1.10,
                 'ST_CLERK', salary*1.15,
                             salary) "Revised Salary"
  FROM employees;