CASE Expressions in SQL. by Joe Celko copyright 2006

(1)

CASE Expressions in SQL

by

Joe Celko

(2)

Joe Celko

-

Articles

z

Member of ANSI X3H2 since 1987

z

SQL for Smarties - DBMS Magazine

z

Celko on SQL - DBP&D

z

SQL Puzzle - Boxes & ArRecords

z

DBMS/Report - Systems Integration

z

WATCOM SQL Column - PBDJ

z

Celko on Software - COMPUTING(UK)

z

Celko - Intelligent Enterprise

(3)

Joe Celko

-

Books

z

SQL for Smarties - 1995, 1999, 2005

z

Instant SQL (Wrox Press)

z

SQL Puzzles & Answers – 1997

z

Data & Databases - 1999

z

Trees & Hierarchies in SQL- 2003

z

SQL Programming Style – 2005

z

Analytics & OLAP in SQL - 2006

(4)

CASE

-

1

z

The CASE expression is an *expression* and

not a control statement; that is, it returns a

value of one datatype

z

SQL-92 stole the idea and the syntax from

the ADA programming language

z

Two kinds of CASE

– simple case

(5)

Simple CASE

-

1

<simple case> ::=

CASE <case operand>

WHEN <value expression> THEN <result>

...

[ELSE <result> ]

END

z

Like the SWITCH in ‘C’ or the SELECT in

Pascal

(6)

Simple CASE

-

2

z The <simple case expression> is a searched CASE expression where

the WHEN clauses are equality comparisons against the <case operand>.

CASE iso_sex_code

WHEN 0 THEN 'Unknown' WHEN 1 THEN 'Male'

WHEN 2 THEN 'Female' WHEN 9 THEN 'N/A' ELSE NULL END

z could also be written as:

CASE

WHEN iso_sex_code = 0 THEN 'Unknown' WHEN iso_sex_code = 1 THEN 'Male'

WHEN iso_sex_code = 2 THEN 'Female' WHEN iso_sex_code = 9 THEN 'N/A' ELSE NULL END

(7)

Simple CASE

-

3

z There is a gimmick in this definition, however. The

expression CASE foo

WHEN 1 THEN 'bar'

WHEN NULL THEN 'no bar' END

z becomes

CASE WHEN foo = 1 THEN 'bar'

WHEN foo = NULL THEN 'no_bar' -- error! ELSE NULL END

(8)

Searched CASE

-

1

<searched case> ::=

CASE

WHEN <search condition> THEN <result>

...

[ ELSE <result> ]

END

z

More like a chain of IF-THEN statements, but

not really

(9)

Searched CASE

-

2

z The WHEN ... THEN ... clauses are executed in left to right order

z The first WHEN clause that tests TRUE returns the value given in its THEN clause. Execution stops

z If the WHEN clauses all fail, then the ELSE clause value is returned

z If no explicit ELSE clause is given for the CASE

expression, then the database will insert a default ELSE NULL clause.

(10)

Searched CASE

-

3

z Order is important!!

CASE WHEN a= 1 THEN ‘X1’

WHEN a= 1 AND b = 1 THEN ‘X2’ WHEN a= 1 AND b = 1 AND c = 1 THEN ‘X3’ ELSE NULL END

z Is not the same as:

CASE WHEN a= 1 AND b = 1 AND c = 1 THEN ‘X3’

WHEN a= 1 AND b = 1 THEN ‘X2’ WHEN a= 1 THEN ‘X1’

(11)

Searched CASE

-

4

z You can nest CASE expressions inside each other

z The data type of the CASE expression is the highest data type of all the THEN clauses

– Even if some of the THEN clauses are impossible to execute

– You can use CAST (NULL AS <datatype>) to force a particular data type; sometimes you must do this

z I recommend always giving the ELSE clause, so that you can change it later when you find something

(12)

Searched CASE

-

5

z This is valid SQL CASE WHEN 1=1

THEN NULL ELSE CAST(NULL AS INTEGER) END z This is valid SQL

CASE WHEN 1=1

THEN CAST(NULL AS INTEGER) ELSE NULL END z This is an error – cannot determine data type

CASE WHEN 1=1

(13)

Coalesce()

z Member of the CASE expression family

z In English, “Pick the first non-NULL from the list” z Defined recursively

1) COALESCE (<value exp #1>) = (<value exp #1>) 2) COALESCE (<value exp #1>, <value exp #2>)

= CASE WHEN <value exp #1> IS NOT NULL THEN <value exp #1>

ELSE <value exp #2> END 3) (n) expressions, where (n >= 3)

COALESCE (<value exp #1>, <value exp #2>, . . ., n) = CASE WHEN <value exp #1> IS NOT NULL

THEN <value exp #1>

ELSE COALESCE (<value exp #2>, . . ., n) END

(14)

NULLIF()

z

Member of the CASE family

z

In English, it converts a particular value to a

NULL

z

NULLIF (<value exp #1>, <value exp #2>)

=

CASE WHEN <value exp #1> = <value exp #2>

THEN CAST (NULL AS <datatype #1>) ELSE <value exp #1> END

(15)

Programming Tricks

-

1

z A trick with CASE in the WHERE clause to make complex logic easier

WHERE CASE

WHEN <search condition #1> THEN ‘t’

WHEN <search condition #2> THEN ‘f’

...

ELSE ‘f’ END = ‘t’

(16)

Programming Tricks

-

2

z

CASE in aggregate functions

SELECT department_name,

SUM(CASE WHEN SEX = ‘m’ THEN 1 ELSE 0 END) AS male_count, SUM(CASE WHEN SEX = ‘f’ THEN 1 ELSE 0 END) AS female_count FROM Personnel

(17)

Programming Tricks

-

3

z Aggregate functions in CASE

z Assume a table of employee skills, with a flag that tells you his primary skill

SELECT employee_id,

CASE WHEN COUNT(*) = 1 THEN MAX(skill_id)

ELSE MAX(CASE WHEN primary_skill_flag = ‘y’ THEN skill_id ELSE NULL END) end AS main_skill

FROM PersonnelSkills GROUP BY employee_id;

(18)

Programming Tricks

-

3

z Aggregate functions in CASE

z Assume a table of employee skills, with a flag that tells you his primary skill

SELECT employee_id,

CASE WHEN COUNT(*) = 1 THEN MAX(skill_id)

ELSE MAX(CASE WHEN primary_skill_flag = ‘y’ THEN skill_id ELSE NULL END) end AS main_skill

FROM PersonnelSkills GROUP BY employee_id;

(19)

Programming Tricks

-

4

z CASE can return any data type, can be used anywhere any

other expression can be used

z Strings:

client_name LIKE CASE client_type

WHEN 1 THEN ‘A_%’ WHEN 2 THEN ‘B_%’

ELSE NULL END

z Temporal:

(20)

Questions & Answers