Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
  Case statement


Oracle (var)char functions
Instr function
Number format
Kill oracle session
to_date function
Oracle sysdate
Oracle substr
How to use the DECODE statement
How to use the CASE statement
How to use the NVL statement
Using XML functions
Oracle date format
Oracle numeric functions
Oracle date functions
Pl sql trim


How to use the case statement in Oracle

The CASE statement can be used in SQL for and IF-THEN-ELSE construction. It's an alternative for the decode statement and was introduced in Oracle 8. Syntax:
case( when condition then expr1 [when condition then expr2] ... [else exprN] ) end

Oracle searches for the first when condition that is true. The expr belonging the this condition is returned. If Oracle does not find such a condition, the expression belonging to the else statement is returned. If no else is specified the null value will be returned.
Sample code
select id
,      case (when status ='A' then 'Accepted'
             when status ='D' then 'Denied'
             else 'Other') end
from   contracts;

Will return for each id:
If status = 'A' : 'Accepted' 
If status = 'D' : 'Denied' 
Else            : 'Other'