Datasheet

44 Chapter 1

Introducing SQL
parentheses is evaluated first. Here is an example of a compound expression: ((2*4)/
(3+1))*10
. The result of 2*4 is divided by the result of 3+1. Then the result from the divi-
sion operation is multiplied by 10.
The CASE Expression
You can use the CASE expression to derive the IF…THEN…ELSE logic in SQL. Here is the syn-
tax of the simple
CASE expression:
CASE <expression>
WHEN <compare value> THEN <return value> … … …
[ELSE <return value>]
END
The CASE expression begins with the keyword CASE and ends with the keyword END. The
ELSE clause is optional. The maximum number of arguments in a CASE expression is 255.
The following query displays a description for the
REGION_ID column based on the value:
SELECT country_name, region_id,
CASE region_id WHEN 1 THEN ‘Europe’
WHEN 2 THEN ‘America’
WHEN 3 THEN ‘Asia’
ELSE ‘Other’ END Continent
FROM countries
WHERE country_name LIKE ‘I%’;
COUNTRY_NAME REGION_ID CONTINE
-------------------- ---------- -------
Israel 4 Other
India 3 Asia
Italy 1 Europe
SQL>
The other form of the CASE expression is the searched CASE, where the values are derived
based on a condition. Oracle evaluates the conditions top to bottom; when a condition
evaluates to true, the rest of the
WHEN clauses are not evaluated. This version has the follow-
ing syntax:
CASE
WHEN <condition> THEN <return value> … … …
[ELSE <return value>]
END
95127c01.indd 44 2/18/09 6:37:11 AM