case

case expression simplifies standard SQL expressions by allowing you to express a search condition using a when...then construct instead of an if statement. It supports conditional SQL expressions; can be used anywhere a value expression can be used.

Syntax

case and expression syntax:

case 
	when search_condition then expression 
	[when search_condition then expression]...
	[else expression]
end

case and value syntax:

case value
	when value then expression 
	[when value then expression]...
	[else expression]
end

Parameters

Examples

Usage

  • Use case with value when comparing values, where value is the value desired. If value equals expression, then the value of the case is result. If value1 does not equal express, valuet is compared to value2. If value equals value2, then the value of the CASE is result2. If none of the value1 ... valuen are equal to the desired valuet, then the value of the CASE is resultx. All of the resulti can be either a value expression or the keyword NULL. All of the valuei must be comparable types, and all of the results must have comparable datatypes.

  • If your query produces a variety of datatypes, the datatype of a case expression result is determined by datatype hierarchy. If you specify two datatypes that the SAP ASE server cannot implicitly convert (for example, char and int), the query fails.

See also if...else, select, where clause in Reference Manual: Commands.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute case.

Related concepts
Expressions
Datatypes of Mixed-Mode Expressions
Related reference
coalesce
nullif