Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
Expressions, 5 of 14
CASE
expressions let you use IF
... THEN
... ELSE
logic in SQL statements without having to invoke procedures. The syntax is:
case_expression::=
case_expression
simple_case_expression::=
simple_case_expression
searched_case_expression::=
searched_case_expression
else_clause::=
else_clause
In a simple CASE
expression, Oracle searches for the first WHEN
... THEN
pair for which expr
is equal to comparison_expr
and returns return_expr
. If none of the WHEN
... THEN
pairs meet this condition, and an ELSE
clause exists, then Oracle returns else_expr
. Otherwise, Oracle returns null. You cannot specify the literal NULL
for all the return_expr
s and the else_expr
.
All of the expressions (expr
, comparison_expr
, and return_expr
) must of the same datatype, which can be CHAR
, VARCHAR2
, NCHAR
, or NVARCHAR2
.
In a searched CASE
expression, Oracle searches from left to right until it finds an occurrence of condition
that is true, and then returns return_expr
. If no condition
is found to be true, and an ELSE
clause exists, Oracle returns else_expr
. Otherwise Oracle returns null.
See Also:
|
For each customer in the sample oe.customers
table, the following statement lists the credit limit as "Low" if it equals $100, "High" if it equals $5000, and "Medium" if it equals anything else.
SELECT cust_last_name, CASE credit_limit WHEN 100 THEN 'Low' WHEN 5000 THEN 'High' ELSE 'Medium' END FROM customers; CUST_LAST_NAME CASECR -------------------- ------ ... Bogart Medium Nolte Medium Loren Medium Gueney Medium
The following statement finds the average salary of the employees in the sample table oe.employees
, using $2000 as the lowest salary possible:
SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary ELSE 2000 END) "Average Salary" from employees e; Average Salary -------------- 6425
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|