74 Informix Dynamic Server V10 . . . Extended Functionality for Modern Business
3.1 The CASE clause
The CASE expression clause is a bit of procedural logic in an otherwise
declarative language. By using the CASE clause you might be able to condense
multiple SQL statements into a single statement or simplify the SQL in other
ways. It is part of the SQL:1999 standard, and it is supported by all the major
A CASE expression is a conditional expression which is very similar to the
switch statement in the C programming language. Apart from the standard data
types, the CASE clause allows usage of expression involving cast expressions
and extended data types, however currently it does not allow expressions that
compare BYTE or TEXT values.
Example 3-1 shows the syntax. You have the choice of two forms, one for simple
operations and one for more complex operations. As per the syntax, you must
include at least one when clause within the CASE expression, but any
subsequent when clause and the else clause are optional. If the CASE clause
does not handle the else clause and if none of the when conditions evaluate to
true, the resulting value is null.
The first statement uses the CASE clause to translate the integer column type
values into their corresponding character meanings. That makes the meaning of
the result set much more understandable to a reader. For a program, this can
protect the program from changes to the values or their meanings.
The second statement shows the simpler form of the same statement. In this one
we include the else condition to handle any values that are not included in any of
the other conditions of the clause. The else could have been included in the first
form as well.
Using the else protects against inadvertent null values being included in the
result set, which can relieve programs of having to check for nulls. If none of the
conditions in the case clause are valid or true, then a null value is returned. So
the else ensures that at least one condition is always met.
If more than one condition is true, the first true condition is used.
Example 3-1 The CASE clause
SELECT a.tabname[1,18] table,
b.colname[1,18] AS column,
WHEN b.coltype = 0 OR b.coltype = 256 THEN 'char'
WHEN b.coltype = 1 OR b.coltype = 257 THEN 'smallint'
WHEN b.coltype = 2 OR b.coltype = 258 THEN 'integer'
Chapter 3. The SQL language 75
WHEN b.coltype = 3 OR b.coltype = 259 THEN 'float'
WHEN b.coltype = 4 OR b.coltype = 260 THEN 'smallfloat'
WHEN b.coltype = 5 OR b.coltype = 261 THEN 'decimal'
WHEN b.coltype = 6 OR b.coltype = 262 THEN 'serial'
WHEN b.coltype = 7 OR b.coltype = 263 THEN 'date'
WHEN b.coltype = 8 OR b.coltype = 264 THEN 'money'
WHEN b.coltype = 9 OR b.coltype = 265 THEN 'null'
WHEN b.coltype = 10 OR b.coltype = 266 THEN 'datetime'
WHEN b.coltype = 11 OR b.coltype = 267 THEN 'byte'
WHEN b.coltype = 12 OR b.coltype = 268 THEN 'text'
WHEN b.coltype = 13 OR b.coltype = 269 THEN 'varchar'
WHEN b.coltype = 14 OR b.coltype = 270 THEN 'interval'
WHEN b.coltype = 15 OR b.coltype = 271 THEN 'nchar'
WHEN b.coltype = 16 OR b.coltype = 272 THEN 'nvchar'
WHEN b.coltype = 17 OR b.coltype = 273 THEN 'int8'
WHEN b.coltype = 18 OR b.coltype = 274 THEN 'serial8'
WHEN b.coltype = 19 OR b.coltype = 275 THEN 'set'
WHEN b.coltype = 20 OR b.coltype = 276 THEN 'multiset'
WHEN b.coltype = 21 OR b.coltype = 277 THEN 'list'
WHEN b.coltype = 22 OR b.coltype = 278 THEN 'row'
WHEN b.coltype = 23 OR b.coltype = 279 THEN 'collection'
WHEN b.coltype = 24 OR b.coltype = 280 THEN 'rowref'
WHEN b.coltype = 40 OR b.coltype = 296 THEN 'blob'
WHEN b.coltype = 41 OR b.coltype = 297 THEN 'lvarchar'
end AS columntype,
HEX(collength) AS size
FROM systables a,
syscolumns b
WHERE a.tabid = b.tabid AND
b.tabid > 99
GROUP BY a.tabname, b.colname, b.coltype, b.collength
SELECT a.tabname[1,18] table,
b.colname[1,18] AS column,
CASE coltype
WHEN 0 THEN "char"
WHEN 1 THEN "smallint"
WHEN 2 THEN "integer"
WHEN 3 THEN "float"
WHEN 4 THEN "smallfloat"
WHEN 5 THEN "decimal"
WHEN 6 THEN "serial"
WHEN 7 THEN "date"
WHEN 8 THEN "money"
WHEN 9 THEN "null"

Get Informix Dynamic Server V10 . . . Extended Functionality for Modern Business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.