Developer Reference : Data Access Methods : SQL Engine Reference : SQL Syntax Reference : CASE (expression)
 
CASE (expression)
A CASE expression returns a value. CASE expression has two formats:
Simple When/Then. This format compares a value expression to a set of value expressions to determine a result. The value expressions are evaluated in their order listed. If a value expression evaluates to TRUE, CASE returns the value expression for the THEN clause.
Searched When/Then. This format evaluates a set of Boolean expressions to determine a result. The Boolean expressions are evaluated in their order listed. If a Boolean expression evaluates to TRUE, CASE returns the expression for the THEN clause.
Both formats support an optional ELSE argument. If no ELSE clause is used, then ELSE NULL is implied.
Syntax
Simple When/Then:
CASE case_value_expression
WHEN when_expression THEN then_expression [...]
[ ELSE else_expression ]
END
 
Searched When/Then:
CASE
WHEN search_expression THEN then_expression [...]
[ ELSE else_expression ]
END
Arguments
case_value_expression ::= the expression evaluated by the simple When/Then CASE format.
 
when_expression ::= The expression to which case_value_expression is compared. The data types of case_value_expression and each when_expression must be the same or must be an implicit conversion.
 
then_expression ::= the expression returned when case_value_expression equals when_expression evaluates to TRUE.
 
else_expression ::= the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL.
 
search_expression ::= the Boolean expression evaluated by the searched CASE format. Search_expression may be any valid Boolean expression.
Remarks
A CASE expression must be used within a SELECT statement. The SELECT statement may be within a stored procedure or within a view.
Examples
The following statement uses the simple When/Then format to report the prerequisites for the art courses listed in the Course table.
SELECT name 'Course ID', description 'Course Title',
CASE name
WHEN 'Art 101' THEN 'None'
WHEN 'Art 102' THEN 'Art 101 or instructor approval'
WHEN 'Art 203' THEN 'Art 102'
WHEN 'Art 204' THEN 'Art 203'
WHEN 'Art 305' THEN 'Art 101'
WHEN 'Art 406' THEN 'None'
WHEN 'Art 407' THEN 'Art 305'
END
AS 'Prerequisites' FROM Course WHERE Dept_Name = 'Art' ORDER BY name
The query returns the following:
Course ID
Course Title
Prerequisites
Art 101
Drawing I
None
Art 102
Drawing II
Art 101 or instructor approval
Art 203
Drawing III
Art 102
Art 204
Drawing IV
Art 203
Art 305
Sculpture
Art 101
Art 406
Modern Art
None
Art 407
Baroque Art
Art 305
============ 
The previous statement can be changed to include an ELSE clause:
SELECT name 'Course ID', description 'Course Title',
CASE name
WHEN 'Art 101' THEN 'None'
WHEN 'Art 102' THEN 'Art 101 or instructor approval'
WHEN 'Art 203' THEN 'Art 102'
WHEN 'Art 204' THEN 'Art 203'
WHEN 'Art 305' THEN 'Art 101'
ELSE 'Curriculum plan for Art History majors'
END
AS 'Prerequisites' FROM Course WHERE Dept_Name = 'Art' ORDER BY name
The query now returns the following:
Course ID
Course Title
Prerequisites
Art 101
Drawing I
None
Art 102
Drawing II
Art 101 or instructor approval
Art 203
Drawing III
Art 102
Art 204
Drawing IV
Art 203
Art 305
Sculpture
Art 101
Art 406
Modern Art
Curriculum plan for Art History majors
Art 407
Baroque Art
Curriculum plan for Art History majors
============ 
The following statement uses the searched When/Then format to report the funding program for which a person may be eligible.
SELECT last_name, first_name,
CASE
WHEN scholarship = 1 THEN 'Scholastic'
WHEN citizenship <> 'United States' THEN 'Foreign Study'
WHEN (date_of_birth >= '1960-01-01' AND date_of_birth <= '1970-01-01') THEN 'AJ-44 Funds'
ELSE 'NONE'
END
AS 'Funding Program' FROM Person ORDER BY last_name
Here is a partial listing of what the query returns:
Last_Name
First_Name
Funding Program
Abad
Alicia
NONE
Abaecherli
David
Foreign Study
Abebe
Marta
Foreign Study
Abel
James
AJ-44 Funds
Abgoon
Bahram
Foreign Study
Abken
Richard
NONE
Abu
Austin
Foreign Study
Abuali
Ibrahim
AJ-44 Funds
Acabbo
Joseph
NONE
Acar
Dennis
Foreign Study
============ 
The following example shows how a CASE expression may be used within a stored procedure.
CREATE PROCEDURE pcasetest() RETURNS (d1 CHAR(10), d2 CHAR(10));
BEGIN
SELECT c1, CASE WHEN c1 = 1 THEN c4
WHEN c1 = 2 THEN c5
ELSE
CASE WHEN c2 = 100.22 THEN c4
WHEN c2 = 101.22 THEN c5 END END
FROM tcasetest;
END
 
CALL pcasetest
============ 
The following example shows how a CASE expression may be used within a view.
CREATE VIEW vcasetest (vc1, vc2) AS
SELECT c1, CASE WHEN c1 = 1 THEN c4
WHEN c1 = 2 THEN c5
ELSE
CASE WHEN c2 = 100.22 THEN c4
WHEN c2 = 101.22 THEN c5 END END
FROM TCASEWHEN
 
SELECT * FROM vcasetest
See Also
COALESCE, SELECT