Using Conditional Functions
Retrieving Data with Queries
2-26 Oracle Database 2 Day Developer's Guide
ROUND(salary * 12 + commission_pct * 300000, 2),
salary * 12) "With $300K Sales"
FROM employees
WHERE job_id LIKE '%_M%' AND department_id = 80;
The results of the query appear.
Name Comm Rate With $300K Sales
---------------------------------- ---------------------- -----------------
John Russell 40 288000
Karen Partners 30 252000
Alberto Errazuriz 30 234000
Gerald Cambrault 30 222000
Eleni Zlotkey 20 186000
5 rows selected
Your result shows that in the
Comm Rate
column, the
NVL
function replaces a
NULL
value by
0
. In the
With $300K Sales
column, the
NVL2
function generates values
from two different expressions, depending on the value of the
COMMISSION_PCT
value.
Using Conditional Functions
Oracle Database provides two functions that can return values based on multiple
condition values.
The
CASE
function is equivalent to nestled
IF ... THEN ... ELSE
statements, as it
compares a value, an expression, or a search condition, and returns a result when it
finds a match.
In
, you will use the
CASE
structure to view prospective salary increases
that would be awarded based on the length of service with the company.
Example 244 Using the CASE Function
SELECT first_name || ' ' || last_name "Name",
hire_date "Date Started", salary "Current Pay",
CASE
WHEN hire_date < TO_DATE('01-Jan-90') THEN TRUNC(salary*1.15, 0)
WHEN hire_date < TO_DATE('01-Jan-95') THEN TRUNC(salary*1.10, 0)
WHEN hire_date < TO_DATE('01-Jan-00') THEN TRUNC(salary*1.05, 0)
ELSE salary END "Proposed Salary"
FROM employees;
The results of the query appear.
Name Date Started Current Pay Proposed Salary
-------------------------- ---------------- --------------- -------------------
Steven King 17-JUN-87 24000 27600
Neena Kochhar 21-SEP-89 17000 19550
Lex De Haen 13-JAN-93 17000 18700
...
107 rows selected
Your result shows that the values in the
Proposed Salary
column have been
adjusted based on the values of
Date Started
.
The
DECODE
function compares a value or expression to search values, and returns a
result when it finds a match. If a match is not found, then
DECODE
returns the default
value, or
NULL
(if a default value is not specified).
