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	Scholastic
Abaecherli	David	Scholastic
Abebe	Marta	Foreign Study
Abel	James	Scholastic
Abgoon	Bahram	Foreign Study
Abken	Richard	None
Abu	Austin	Foreign Study
Abuali	Ibrahim	AJ-44 Funds
Acabbo	Joseph	Scholastic
Acar	Dennis	Foreign Study

The following example show 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 show 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

CASE (expression)

Syntax

Arguments

Remarks

Examples

See Also