CASE expressions

A CASE expression is a conditional expression, which is similar to the concept of the CASE statement in programming languages. You can use a CASE expression when you want to change the way data is represented. The CASE expression allows a statement to return one of several possible results, depending on which of several condition tests evaluates to TRUE.

TEXT or BYTE values are not allowed in a CASE expression.

Consider a column that represents marital status numerically as 1,2,3,4 with the corresponding values meaning single, married, divorced, widowed. In some cases, you might prefer to store the short values (1,2,3,4) for database efficiency, but employees in human resources might prefer the more descriptive values (single, married, divorced, widowed). The CASE expression makes such conversions between different sets of values easy.

In HCL Informix®, the CASE expression also supports extended data types and cast expressions.

The following example shows a CASE expression with multiple WHEN clauses that returns more descriptive values for the manu_code column of the stock table. If none of the WHEN conditions is true, NULL is the default result. (You can omit the ELSE NULL clause.)

SELECT
   CASE
      WHEN manu_code = "HRO" THEN "Hero"
      WHEN manu_code = "SHM" THEN "Shimara"
      WHEN manu_code = "PRC" THEN "ProCycle"
      WHEN manu_code = "ANZ" THEN "Anza"
      ELSE NULL
   END
   FROM stock;

You must include at least one WHEN clause within the CASE expression; subsequent WHEN clauses and the ELSE clause are optional. If no WHEN condition evaluates to true, the resulting value is NULL. You can use the IS NULL expression to handle NULL results. For information on handling NULL values, see the HCL Informix Guide to SQL: Syntax.

The following query shows a simple CASE expression that returns a character string value to flag any orders from the orders table that have not been shipped to the customer.

Figure 1. Query

SELECT order_num, order_date,
      CASE 
         WHEN ship_date IS NULL
         THEN "order not shipped"
      END 
   FROM orders;

Figure 2. Query result

order_num order_date (expression)

       1001 05/20/1998
       1002 05/21/1998
       1003 05/22/1998
       1004 05/22/1998
       1005 05/24/1998
       1006 05/30/1998 order not shipped
       1007 05/31/1998
      ⋮
       1019 07/11/1998
       1020 07/11/1998
       1021 07/23/1998
       1022 07/24/1998
       1023 07/24/1998

For information about how to use the CASE expression to update a column, see CASE expression to update a column.