Problem solve Get help with specific problems with your technologies, process and projects.

SQL case expressions

An SQL case expression offers a simple way to add conditional evaluation to an SQL statement.

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'
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.


Dig Deeper on iSeries CL programming

Join the conversation

4 comments

Send me notifications when other members comment.

Please create a username to comment.

Looking for information about how CASE expressions compare to Oracle's DECODE function?

Check out this article: CASE expressions in Oracle SQL
Cancel
The blog was moved to a different domain.  This is the new URL: CASE Expressions in Oracle SQL
Cancel
Iseries need assistance with Null

I have this in Infinium AS400 
CASE A12.NHLIN# WHEN 0 THEN 'Header' ELSE 'DETAIL' END NHLIN#2,

I need to make it
CASE A12.NHLIN# WHEN 0 THEN 'Header' WHEN NULL THEN '' ELSE 'DETAIL' END NHLIN#2,
it does not like anything I do with Null Can you assist?
Cancel

You need a "searched" case expression.

Try this:

CASE 
   WHEN A12.NHLIN# = 0 THEN 'Header' 
   WHEN A12.NHLIN# IS NULL THEN ''
   ELSE 'DETAIL' 
END NHLIN#2,

Cancel

-ADS BY GOOGLE

SearchDataCenter

Close