case

Description

Supports conditional SQL expressions; can be used anywhere a value expression can be used.

Syntax

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

case and values syntax:

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

Parameters

case: begins the case expression.

when: precedes the search condition or the expression to be compared.

search_condition: is used to set conditions for the results that are selected. Search conditions for case expressions are similar to the search conditions in a where clause. Search conditions are detailed in the Transact-SQL User’s Guide.

then: precedes the expression that specifies a result value of case.

expression: is a column name, a constant, a function, a subquery, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. For more information about expressions, see “Expressions”.

Examples

Example 1

Selects all the authors from the authors table and, for certain authors, specifies the city in which they live:

select au_lname, postalcode, 
     case
            when postalcode = "94705" 
                then "Berkeley Author"
            when postalcode = "94609" 
                then "Oakland Author"
            when postalcode = "94612" 
                then "Oakland Author"
            when postalcode = "97330" 
                then "Corvallis Author"
        end 
from authors

Example 2

Returns the first occurrence of a non-NULL value in either the lowqty or highqty column of the discounts table:

select stor_id, discount,
        coalesce (lowqty, highqty)
from discounts

Example 3

This is an alternative way of writing Example 2:

select stor_id, discount, 
        case
            when lowqty is not NULL then lowqty
            else highqty
        end
from discounts

Example 4

Selects the titles and type from the titles table. If the book type is UNDECIDED, nullif returns a NULL value:

select title,
        nullif(type, "UNDECIDED")
from titles

Example 5

This is an alternative way of writing Example 4:

select title, 
        case
            when type = "UNDECIDED" then NULL
            else type
        end
from titles

Usage

case expression simplifies standard SQL expressions by allowing you to express a search condition using a when...then construct instead of an if statement.
case expressions can be used anywhere an expression can be used in SQL.
At least one expression must be something other than the null keyword. This example produces the following error message:
```
select price, coalesce (NULL, NULL, NULL)
from titles
```
```
All result expressions in a CASE expression must not be NULL.
```
If your query produces a variety of datatypes, the datatype of a case expression result is determined by datatype hierarchy, as described in Datatype of mixed-mode expressions in Chapter 1, “System and User-Defined Datatypes,” in Volume 1, Building Blocks. If you specify two datatypes that Adaptive Server cannot implicitly convert (for example, char and int), the query fails.
coalesce is an abbreviated form of a case expression. Example 3 describes an alternative way of writing the coalesce statement.

coalesce must be followed by at least two expressions. This example produces the following error message:

select stor_id, discount, coalesce (highqty)
from discounts

A single coalesce element is illegal in a COALESCE expression.

nullif is an abbreviated form of a case expression. Example 5 describes an alternative way of writing nullif.

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

case permission defaults to all users. No permission is required to use it.