Tutorial 6 - The Oracle "CASE" Statement

The "case" statement can be used much like an if-then-else statement but with less syntax.



SELECT BIKE_NAMES,

CASE
WHEN 'TREK' THEN 'Lance''s Trek'
WHEN 'LITESPEED' THEN 'Lance''s other Trek'
ELSE 'Obsolete Bike'

END
FROM BIKES;


Transalation: Basically the statement above says select the column "BIKE_NAMES" from the table "BIKES". When the column shows "TREK" replace with "Lance's Trek" (notice the intentional double apostrophe to display "Lance's"). The statement continues to say when the column "BIKE_NAMES" contains "LITESPEED" replace the row with "Lance's other Trek" (notice the intentional double apostrophe to display "Lance's"). Finally, the statement has an else that displays "Obsolete Bike" should there be ANY other values in the "BIKE_NAMES" column. I think "END" speaks for itself with the table "BIKES" being selected AFTER the "CASE" statement.

-Andy

Notes: Programmatically speaking the "CASE" statement is cleaner and more forward then using if-then-else, especially if you might be creating a basic view.

Applies to:Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g