Built-In Functions: CASE

Submitted by:David Villa

Date added:28 June, 2014

Category:PL SQL

Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.

Tags: sql statement , case statement

Code Snippet:

SELECT size_field, 
CASE WHEN (size_field BETWEEN 1 AND 10) THEN 'One'
WHEN (size_field BETWEEN 11 AND 100) THEN 'Big'
ELSE 'Bigger'
END
FROM test_table;


-- You can use the case statement in an SQL statement as
-- follows: (includes the expression clause)

SELECT table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END


-- you can write the SQL statement using the case statement
-- like this: (omits the expression clause)

SELECT table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
FROM all_tables;
 
 

Comments