CASE, DECODE SQL Functions

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.

Syntax 1

This syntax applies to CASE only.

CASE

   rule rule ...]

   ELSE default-expression

END

 

rule  ::=   WHEN condition THEN output-expression

If condition in the required rule applies, then the corresponding output-expression is returned. If none of the rules apply, default-expression (if present) is returned.

Syntax 2

This syntax applies to CASE and DECODE. The syntax between CASE and DECODE is different but the functionality is otherwise equivalent.

CASE syntax:

CASE

   evaluated-expression

   rule rule ...]

   ELSE default-expression

END

 

rule  ::=   WHEN input-expression THEN output-expression

DECODE syntax:

DECODE(

     evaluated-expression

  ,  rule

[ ,  rule ... ]

[ ,  default-expression

)

 

rule  ::=   input-expression, output-expression

If evaluated-expression equals the input-expression of the rule, then the output-expression of the rule is returned. If none of the rules apply, default-expression (if present) is returned.

 

 

collapseMultiple rules
collapseDefault expression and NULL result
collapseExample 1: SELECT clause, CASE with conditions (Syntax 1)
collapseExample 2a: SELECT clause, CASE with evaluated expression (Syntax 2)
collapseExample 2b: SELECT clause, DECODE with evaluated expression (Syntax 2)
collapseExample 3: WHERE clause, CASE with conditions (Syntax 1)
collapseExample 4: Default NULL values

 

 

See also

SQL Functions