Evaluates a series of boolean expressions and returns the matching result value for the first boolean expression that evaluates to True.

Syntax

---

CASE
WHEN <BooleanExpression> THEN <Expression>
[WHEN <BooleanExpression> THEN <Expression>]
[ELSE] <Expression>
END

Short-hand syntax:

CASE <Expression>
WHEN <Expression> THEN <Expression>
[WHEN <Expression> THEN <Expression>]
[ELSE] <Expression>
END

<BooleanExpression> =

Type of:

BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT

<Expression> =

Type of:

CHARACTER|CHAR
CHARACTER VARYING|VARCHAR
GUID
BYTE
BYTE VARYING|VARBYTE
BINARY LARGE OBJECT|BLOB
CHARACTER LARGE OBJECT|CLOB
BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT
FLOAT
DECIMAL|NUMERIC
DATE
TIME
TIMESTAMP
INTERVAL YEAR
INTERVAL YEAR TO MONTH
INTERVAL MONTH
INTERVAL DAY
INTERVAL DAY TO HOUR
INTERVAL DAY TO MINUTE
INTERVAL DAY TO SECOND
INTERVAL DAY TO MSECOND
INTERVAL HOUR
INTERVAL HOUR TO MINUTE
INTERVAL HOUR TO SECOND
INTERVAL HOUR TO MSECOND
INTERVAL MINUTE
INTERVAL MINUTE TO SECOND
INTERVAL MINUTE TO MSECOND
INTERVAL SECOND
INTERVAL SECOND TO MSECOND
INTERVAL MSECOND

Returns

---

Same as input

Usage

---

The CASE expression is not actually a function, but it behaves like one so it is included with the functions. The CASE expression can be used in with two different syntaxes, one being the normal syntax while the other being a short-hand syntax. The normal syntax evaluates a series of boolean expressions and returns the matching result expression associated with the first boolean expression that evaluates to True. The primary difference between the short-hand syntax and the normal syntax is the inclusion of the expression directly after the CASE keyword itself. It is used as the comparison value for every WHEN expression. All WHEN expressions must be type-compatible with this expression, unlike the normal syntax which requires boolean expressions. The rest of the short-hand syntax is the same as the normal syntax.

Examples

---

SELECT CAST(TransDateTime AS VARCHAR(25)) + ':' +
CASE
   WHEN Amount < 0 THEN
      '(' + CAST(Amount AS VARCHAR(20)) + ')'
   ELSE
      CAST(Amount AS VARCHAR(20))
END
AS Entry
FROM Transactions

SQL 2003 Standard Deviations

---

This function deviates from the SQL 2003 standard in the following ways: