case

Description

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

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 and value: 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”.

else: is optional. When not specified, else null is implied.

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

Yuo can also use the following format to produce the same result, since coalesce is an abbreviated form of a case expression:

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

Example 3

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

You can also use the following format to produce the same result, since nullif is an abbreviated form of a case expression:

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

Example 4

Produces an error message, because at least one expression must be something other than the null keyword:

select price, coalesce (NULL, NULL, NULL)
from titles

All result expressions in a CASE expression must not be NULL.

Example 5

Produces an error message, because at least two expressions must follow coalesce:

select stor_id, discount, coalesce (highqty) from discounts

A single coalesce element is illegal in a COALESCE expression.

Example 6

This case with values example updates salary information for employees:

update employees
    set salary =
        case dept
         when 'Video' then salary * 1.1
         when 'Music' then salary * 1.2
         else 0
        end

Example 7

In the movie_titles table, the movie_type column is encoded with an integer rather than the cha(10) needed to spell out “Horror,” “Comedy,” “Romance,” and “Western.” However, a text string is returned to applications through the use of case expression:

select title,
    case movie_type
        when 1 then 'Horror'
        when 2 then 'Comedy'
        when 3 then 'Romance'
        when 4 then 'Western'
        else null
    end,
    our_cost
from movie_titles

Usage

Use:

case expression simplifies standard SQL expressions by allowing you to express a search condition using a when...then construct instead of an if statement.
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. The data type of the
case expressions can be used anywhere an expression can be used in SQL.
If your query produces a variety of datatypes, the datatype of a case expression result is determined by datatype hierarchy, as described in “Datatypes of mixed-mode expressions” in. If you specify two datatypes that Adaptive Server cannot implicitly convert (for example, char and int), the query fails.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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