CASE, DECODE

Previous Next

CASE and DECODE return output values dependent on value comparisons.

CASE is a native function on all RDBMSs. DECODE is native on Oracle. DECODE is convertible in USoft for all other platforms. USoft converts DECODE to CASE on non-Oracle platforms.

CASE

Syntax 1

This syntax applies to CASE only.

CASE

WHEN condition THEN output-expression

WHEN condition THEN output-expression ...

ELSE default-output-expression

END

If condition evaluates to true, output-expression is returned. Otherwise, default-output-expression (if any) is returned. Otherwise, NULL is returned.

One WHEN clause is required. Further WHEN clauses are optional. If there are multiple WHEN clauses, they are evaluated in the order in which they appear. As soon as a condition applies, the output-expression is returned and further clauses are ignored.

Syntax 2

This syntax has a DECODE counterpart (see below). The syntax between CASE and DECODE is different but the functionality is otherwise equivalent.

CASE evaluated-expression

WHEN input-expression THEN output-expression

WHEN input-expression THEN output-expression ...

ELSE default-output-expression

END

If input-expression is equal to evaluated-expression, output-expression is returned. Otherwise, default-output-expression (if any) is returned. Otherwise, NULL is returned.

One WHEN clause is required. Further WHEN clauses are optional. If there are multiple WHEN clauses, they are evaluated in the order in which they appear. As soon as input-expression matches evaluated-expression, output-expression is returned and further clauses are ignored.

DECODE

Syntax

DECODE(

      evaluated-expression

,     input-expression, output-expression

,     input-expression, output-expression

,     default-output-expression

)

If input-expression is equal to evaluated-expression, output-expression is returned. Otherwise, default-output-expression (if any) is returned. Otherwise, NULL is returned.

One input-expression, output-expression pair is required. Further pairs are optional. If there are multiple pairs, they are evaluated in the order in which they appear. As soon as input-expression matches evaluated-expression, output-expression is returned and further pairs are ignored.

 

 

collapseExample 1: SELECT clause, CASE with conditions
collapseExample 2a: SELECT clause, CASE with evaluated expression
collapseExample 2b: SELECT clause, DECODE
collapseExample 3: WHERE clause, CASE with conditions
collapseExample 4: Default NULL values

 

See also

SQL Functions