CASE

Specifies a conditional value. Both simple and searched case expressions are supported. Case expression can be specified anywhere an expression can be and can be used as often as needed.

Instead of using a series of if statements, case expression allows you to use a series of conditions that return the appropriate values when the conditions are met. With CASE expression, you can simplify queries and write more efficient code.

SQL syntax

The syntax for a searched CASE expression is:

CASE
  {WHEN SearchCondition THEN Expression1}[…] 
   [ELSE Expression2]
END

The syntax for a simple CASE expression is:

CASE Expression 
  {WHEN CompExpression THEN Expression1}[…]
   [ELSE Expression2] 
END

Parameters

CASE has the parameters:

Parameter	Description
`WHEN` `SearchCondition`	Specifies the search criteria. This clause cannot specify a subquery.
`WHEN` `CompExpression`	Specifies the operand to be compared.
`Expression`	Specifies the first operand to be compared with each CompExpression.
`THEN` `Expression1`	Specifies the resulting expression.
`ELSE` `Expression2`	If condition is not met, specifies the resulting expression. If no ELSE clause is specified, TimesTen adds an ELSE NULL clause to the expression.

Description

CASE expression can not be specified in the value clause of an INSERT statement.

Examples

To specify a searched CASE statement that specifies the value of a color, use:

SELECT CASE 
  WHEN color=1 THEN 'red' 
  WHEN color=2 THEN 'blue' 
  ELSE 'yellow' 
END FROM cars;

To specify a simple CASE statement that specifies the value of a color, use:

SELECT CASE color 
  WHEN 1 THEN 'red' 
  WHEN 2 THEN 'blue' 
  ELSE 'yellow' 
END FROM cars;