CASE

The Oracle and PL/SQL CASE statement has the functionality of an IF-THEN-ELSE in SQL statements.

CASE WHEN expr1 THEN val1 WHEN expr2 THEN val2 ... ELSE valn END

Example 1:

SELECT CASE WHEN STATUS<=0 THEN 'good' WHEN STATUS>=1 AND STATUS<=5 THEN 'medium' ELSE 'bad' END FROM INVOICE;

returns:

bad
bad
good
medium

Example 2:

SELECT table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is a Developer'
END
FROM all_tables;