SQL case expressions

An SQL case expression offers a simple way to add conditional evaluation to an SQL statement. It can often simplify what would otherwise be a difficult, or even impossible task.

The syntax can take two forms, which can be summarized as follows. (See the SQL Reference Manual for complete details.)

CASE
WHEN search-condition THEN result-expression
ELSE result-expression
END

-- or --

CASE expression
WHEN expression THEN result-expression
ELSE result-expression
END

The WHEN clause can be repeated any number of times. The ELSE clause can be omitted. The value of the case expression is the value of the result-expression following the first WHEN clause that evaluates to true. If no WHEN clause is true, the result is the value of the ELSE result-expression. If the ELSE clause is omitted, the result is null. All result-expressions must have compatible data types.

Below are some examples of the case expression. These techniques can be used in both interactive and embedded SQL.

1. Handle divide by zero.

Select CASE
When field1 = 0 then 0
Else field2/field1
END
From filename

Any appropriate value (including null) can be specified in the THEN expression.

2. Map field value.

Select OrdNo, ShipDate, CASE
When ShipDate < curdate() then 'Overdue'
When ShipDate = curdate() then 'Processing'
Else 'Active'
END
From OrdFile

3. Obtain month name from month number.

When ShipDate is a Date or Timestamp field:

Select OrdNo, CASE Month(ShipDate)
When 1 then 'Jan'
When 2 then 'Feb'
When 3 then 'Mar'
When 4 then 'Apr'
When 5 then 'May'
When 6 then 'Jun'
When 7 then 'Jul'
When 8 then 'Aug'
When 9 then 'Sep'
...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT iSeries programmer tips Enhancing RPG with external SQL stored procedures Tracking data changes on IBM i with triggers Introduction to SQLRPGLE on IBM i: Making a report Implementing a browser interface in COBOL: Displaying database fields Taking advantage of CL advancements, starting with V5R3 TAATOOL: Useful tools for programmers on IBM i Implementing a browser interface in COBOL: Creating your graphic Web page Implementing a browser interface in COBOL: Getting started Making the most of RPG data handling on IBM i Groovy programming on IBM i iSeries CL programming Taking advantage of CL advancements, starting with V5R3 Checking in on your IBM i authorization lists Running PHP open source applications: NOBODY needs authority Simplify the process of converting a spool file from iSeries into an Excel spreadsheet CL program for daily backups An automated CL method of moving a query from AS/400 to Excel Changing user password expiration Eight steps for creating program documentation using AS/400 utilities DAYSPAST CLLE program for AS/400: Compares object creation date with today's date Advanced Job Scheduler help iSeries ILE programming Tracking data changes on IBM i with triggers Introduction to SQLRPGLE on IBM i: Making a report How to use an embedded SQL statement and display the result in a subfile Eight steps for creating program documentation using AS/400 utilities Searching fields for values Searching part of a name or address in AS/400 Top 10 programmer tips YTD How to use the binder language to manage service programs -- Part 3: Examples and pitfalls Top 10 programmer tips of 2005 How to use the binder language to manage service programs -- Part 2: Understanding the binder language

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

When 10 then 'Oct'
When 11 then 'Nov'
When 12 then 'Dec'
END
From OrdFile

When ShipDate is a numeric (8,0) YYYYMMDD field:

Select OrdNo, CASE Substr(Digits(ShipDate), 5, 2)
When '01' then 'Jan'
When '02' then 'Feb'
When '03' then 'Mar'
When '04' then 'Apr'
When '05' then 'May'
When '06' then 'Jun'
When '07' then 'Jul'
When '08' then 'Aug'
When '09' then 'Sep'
When '10' then 'Oct'
When '11' then 'Nov'
When '12' then 'Dec'
Else '???'
END
From OrdFile

4. Update based on mapped field value.

Update filename set field1 = CASE
When field1 = 'X1' then 'Y1'
When field1 = 'X2' then 'Y2'
Else 'Y3'
END

-- or ?

Update filename set field1 = CASE field1
When 'X1' then 'Y1'
When 'X2' then 'Y2'
Else 'Y3'
END

5. Multiple updates based on multiple conditions, in one pass.

The following two SQL statements can be combined into one. The single statement may run faster, especially against a large file, since it makes only one pass.

Update filename set field1 = 'Y1' where field1 = 'X1'
Update filename set field2 = 'Y2' where field2 = 'X2'

Single statement:

Update filename set
field1 = CASE
When field1 = 'X1' then 'Y1'
Else field1
END,
field2 = CASE
When field2 = 'X2' then 'Y2'
Else field2
END

Where field1 = 'X1' or field2 = 'X2'

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.