CASE

The function CASE evaluate a list of conditions and returns the related result expression.

SQL Syntax 1:
 
   CASE input-expression
   WHEN when-expression THEN result-expression
        [ ...n ]
 [ ELSE else-result-expression  ]
END
 
SQL Syntax 2:
 
  CASE
   WHEN boolean-expression THEN result-expression
        [ ...n ]
 [ ELSE else-result-expression  ]
END
 
Parameters:
 
  input-expression
Any expression of any type.

when-expression

A expression that is compared to the input-expression. The WHEN and THEN part can occur multiple times.

result-expression

The result of this expression will be return if the input-expression equals when-expression or the boolean-expression evaluate to TRUE.

else-result-expression

The result of this expression will be return if no WHEN part is TRUE.

boolean-expression

Any boolean expression.
Return Type:
 
  Depends the result-expression.

Examples:

  • SELECT surname
      WHEN 'Peter' THEN 1
      WHEN 'Steven' THEN 2
      ELSE 0
    END
     
  • SELECT
      WHEN surname = 'Peter' THEN 1
      WHEN surname = 'Steven' THEN 2
    END

see also:
SWITCH
SQL System Functions


100% pure Java DBMS
Mirror Site