Using CASE Statements to Improve Transactions

The CASE statement in SPL routines improves the performance of transactions or queries that use IF...THEN...ELSE...END IF statements.

For example, consider a transaction or query that calls an SPL routine that contains the following set of IF...THEN...ELSE...END IF statements:

IF i < 5 THEN                    -- 1 2 3 4
   IF i < 3 THEN                -- 1 2
      IF i < 2 THEN            -- 1
         LET i_id1, whse_id1 =
            i_id,whse_id;
      ELSE                     -- 2
         LET i_id2, whse_id2 = 
            i_id, whse_id;
      END IF;
   ELSE                         -- 3 4
      IF i < 4 THEN            -- 3
         LET i_id3, whse_id3 = 
            i_id, whse_id;
      ELSE                     -- 4
         LET i_id4, whse_id4 = 
            i_id, whse_id;
      END IF;
   END IF;

You can rewrite this set of statements as a CASE statement as follows:

CASE (i)
   WHEN 1 THEN
      LET i_id1, whse_id1 =
         i_id,whse_id;
   WHEN 2 THEN
      LET i_id2, whse_id2 = 
         i_id, whse_id;
   WHEN 3 THEN
      LET i_id3, whse_id3 = 
         i_id, whse_id;
   WHEN 4 THEN
      LET i_id4, whse_id4 = 
         i_id, whse_id;
   ELSE:
      RAISE EXCEPTION 100; -- Illegal value
END CASE

The CASE statement is easier to maintain, and it also reduces processing overhead because each condition is evaluated only once.

For detailed information about the CASE statement, refer to the IBM Informix: Guide to SQL Syntax.