(Scroll to see more)
- Concat with ||
- ROUND (dates)
- ROUND (numbers)
- TRUNC (dates)
- TRUNC (numbers)
Oracle/PLSQL: CASE Statement
Learn how to use the Oracle/PLSQL CASE statement with syntax and examples.
The Oracle/PLSQL CASE statement has the functionality of an IF-THEN-ELSE statement. Starting in Oracle 9i, you can use the CASE statement within a SQL statement.
The syntax for the Oracle/PLSQL CASE statement is:
CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END
Parameters or Arguments
expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further.
result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.
If no condition is found to be true, then the CASE statement will return the value in the ELSE clause.
If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.
You can have up to 255 comparisons in a CASE statement. Each WHEN ... THEN clause is considered 2 comparisons.
The CASE statement can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
The CASE statement can be used in Oracle/PLSQL.
You could use the CASE statement in a 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 FROM all_tables;
Or you could 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;
The above two CASE statements are equivalent to the following IF-THEN-ELSE statement:
IF owner = 'SYS' THEN result := 'The owner is SYS'; ELSIF owner = 'SYSTEM' THEN result := 'The owner is SYSTEM''; ELSE result := 'The owner is another value'; END IF;
The CASE statement will compare each owner value, one by one.
One thing to note is that the ELSE clause within the CASE statement is optional. You could have omitted it. Let's look at the SQL statement above with the ELSE clause omitted.
Your SQL statement would look as follows:
SELECT table_name, CASE owner WHEN 'SYS' THEN 'The owner is SYS' WHEN 'SYSTEM' THEN 'The owner is SYSTEM' END FROM all_tables;
With the ELSE clause omitted, if no condition was found to be true, the CASE statement would return NULL.
Comparing 2 Conditions
Here is an example that demonstrates how to use the CASE statement to compare different conditions:
SELECT CASE WHEN a < b THEN 'hello' WHEN d < e THEN 'goodbye' END FROM suppliers;
Frequently Asked Questions
Question: Can you create a CASE statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.
Answer: Yes, below is an example of a case statement that evaluates two different fields.
SELECT supplier_id, CASE WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office' WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office' END FROM suppliers;
So if supplier_name field is IBM and the supplier_type field is Hardware, then the CASE statement will return North office. If the supplier_name field is IBM and the supplier_type is Software, the CASE statement will return South office.