Informix Dynamic Server V10 . . . Extended Functionality for Modern Business

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

DBMS.

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,

CASE

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

ORDER BY 1

;

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"