Case (SQL)

From Oracle FAQ
Jump to: navigation, search

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:

[edit] 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;

[edit] Also see