An IF - ELIF - ELSE structure

The following SPL routine uses an IF - ELIF - ELSE structure to compare the two arguments that the routine accepts.

Figure 1. An IF - ELIF - ELSE structure to compare two arguments.

CREATE FUNCTION str_compare( str1 CHAR(20), str2 CHAR(20))
   RETURNING INTEGER;

   DEFINE result INTEGER;

   IF str1 > str2 THEN
      LET result = 1;
   ELIF str2 > str1 THEN
      LET result = -1;
   ELSE
      LET result = 0;
   END IF
   RETURN result;
END FUNCTION;

Suppose you define a table named manager with the columns that the following figure shows.

Figure 2. Define the manager table.

CREATE TABLE manager
(
   mgr_name   VARCHAR(30),
   department VARCHAR(12),
   dept_no    SMALLINT,
   direct_reports SET( VARCHAR(30) NOT NULL ),
   projects LIST( ROW ( pro_name VARCHAR(15),
   pro_members SET( VARCHAR(20) NOT NULL ) )
                    NOT NULL),
   salary     INTEGER,
);

The following SPL routine uses an IF - ELIF - ELSE structure to check the number of elements in the SET in the direct_reports column and call various external routines based on the results.

Figure 3. An IF - ELIF - ELSE structure to check the number of elements in the SET.

CREATE FUNCTION checklist( d SMALLINT )
   RETURNING VARCHAR(30), VARCHAR(12), INTEGER;

   DEFINE name VARCHAR(30);
   DEFINE dept VARCHAR(12);
   DEFINE num INTEGER;

   SELECT mgr_name, department,
          CARDINALITY(direct_reports) 
      FROM manager INTO name, dept, num
      WHERE dept_no = d;
   IF num > 20 THEN
      EXECUTE FUNCTION add_mgr(dept);
   ELIF num = 0 THEN
      EXECUTE FUNCTION del_mgr(dept);
   ELSE
      RETURN name, dept, num;
   END IF;

END FUNCTION;

The cardinality() function counts the number of elements that a collection contains. For more information, see Cardinality function.

An IF - ELIF - ELSE structure in an SPL routine has up to the following four parts:

An IF THEN condition
If the condition following the IF statement is TRUE, the routine executes the statements in the IF block. If the condition is false, the routine evaluates the ELIF condition.

The expression in an IF statement can be any valid condition, as the Condition segment of the HCL Informix® Guide to SQL: Syntax describes. For the complete syntax and a detailed discussion of the IF statement, see the HCL Informix Guide to SQL: Syntax.
One or more ELIF conditions (optional)
The routine evaluates the ELIF condition only if the IF condition is false. If the ELIF condition is true, the routine executes the statements in the ELIF block. If the ELIF condition is false, the routine either evaluates the next ELIF block or executes the ELSE statement.
An ELSE condition (optional)
The routine executes the statements in the ELSE block if the IF condition and all of the ELIF conditions are false.
An END IF statement
The END IF statement ends the statement block.