Oracle CASE expression allows you to add if-else logic to SQL statements without having to call a procedure. The CASE expression evaluates a list of conditions and returns one of the multiple possible results.
Oracle CASE expression has two formats: the simple CASE expression and the searched CASE expression. Both formats support an optional ELSE clause.
simple CASE expression:
The following illustrates the syntax of the simple CASE expression:
CASE e
WHEN e1 THEN
r1
WHEN e2 THEN
r2
WHEN en THEN
rn
[ ELSE r_else ]
END
Example:
Select *from app_list where parent_id = (CASE when :P25_PARENT_ID is NULL THEN 0
else :P25_PARENT_ID
END)
Searched CASE expression:
The Oracle searched CASE expression evaluates a list of Boolean expressions to determine the result.
The searched CASE statement has the following syntax:
CASE
WHEN e1 THEN r1
[ WHEN e2 THEN r2]
...
[ELSE
r_else]
select name,
alias,
(CASE
WHEN parent_id==0 THEN 'Parent'
ELSE 'Child'
END) list_type
from app_lists;