Case (SQL)

CASE provides similar functionality to an IF-THEN-ELSE or decode statement. The CASE-clause is supported from Oracle 9i.

Note that DECODE only works with expressions which are scalar values. CASE works with predicates and subqueries in searchable form:

Examples

Scalar values:

SELECT customer_id,
      CASE status
        WHEN 'A' THEN 'ACTIVE'
        WHEN 'I' THEN 'INACTIVE'
        ELSE 'UNKNOWN'
      END
FROM  customers;

Predicates and subqueries in searchable form:

SELECT customer_id,
      CASE 
        WHEN status = 'A' THEN 'ACTIVE'
        WHEN status = 'I' THEN 'INACTIVE'
        ELSE 'UNKNOWN'
      END
FROM  customers;

Also see

• DECODE