Using NULL and SELECT in a Condition

If you declare a variable with the name null or select, including it in a condition that uses the IN keyword is ambiguous. The following example shows three conditions that cause problems: in an IF statement, in a WHERE clause of a SELECT statement, and in a WHILE condition:

CREATE PROCEDURE problem()
. . .
DEFINE x,y,select, null, INT;
DEFINE pfname CHAR[15];
LET x = 3; LET select = 300;
LET null = 1;
IF x IN (select, 10, 12) THEN LET y = 1; -- problem if

IF x IN (1, 2, 4) THEN
SELECT customer_num, fname INTO y, pfname FROM customer
   WHERE customer IN (select , 301 , 302, 303); -- problem in

WHILE x IN (null, 2)     -- problem while
. . .
END WHILE;

You can use the variable select in an IN list if you ensure it is not the first element in the list. The workaround in the following example corrects the IF statement that the preceding example shows:

IF x IN (10, select, 12) THEN LET y = 1; -- problem if