Informix Guide to SQL: Syntax SPL Statements

Use the LET statement to assign values to variables or to call a user-defined function from an SPL routine and assign the return value or values to variables.

If you assign a value to a single variable, it is called a simple assignment; if you assign values to two or more variables, it is called a compound assignment.

You can also assign the value of an expression to a variable. At run time, the value of the SPL expression is computed first. The resulting value is cast to the data type of variable, if possible, and the assignment occurs. If conversion is not possible, an error occurs, and the value of variable name is undefined.

A compound assignment assigns multiple expressions to multiple variables.The data types of expressions in the expression list does not need to match the data types of the corresponding variables in the variable list, because the database server automatically converts the data types. (For a detailed discussion of casting, see the Informix Guide to SQL: Reference.)

The following example shows several LET statements that assign values to SPL variables:

LET a   = c + d ;

LET a,b = c,d ;

LET expire_dt = end_dt + 7 UNITS DAY; 

LET name = 'Brunhilda';

LET sname = DBSERVERNAME;

LET this_day = TODAY;

You cannot use multiple values to operate on other values. For example, the following statement is illegal:

LET a,b = (c,d) + (10,15); -- ILLEGAL EXPRESSION

The diagram for LET refers to this section.

The examples in this section use a SELECT statement in a LET statement. You can use a SELECT statement to assign values to one or more variables on the left side of the equals (=) operator, as the following example shows:

LET a,b = (SELECT c1,c2 FROM t WHERE id = 1);	

LET a,b,c = (SELECT c1,c2 FROM t WHERE id = 1), 15;

You cannot use a SELECT statement to make multiple values operate on other values. The following example is illegal:

LET a,b = (SELECT c1,c2 FROM t) + (10,15); -- ILLEGAL CODE

Because a LET statement is equivalent to a SELECT...INTO statement, the two statements in the following example have the same results: a=c and b=d:

CREATE PROCEDURE proof()

	DEFINE a, b, c, d INT;

	LET a,b = (SELECT c1,c2 FROM t WHERE id = 1);	

	SELECT c1, c2 INTO c, d FROM t WHERE id = 1

END PROCEDURE 

If the SELECT statement returns more than one row, you must enclose the SELECT statement in a FOREACH loop.

For a complete description of SELECT syntax and usage, see SELECT.

You can call a user-defined function in a LET statement and assign the return values to an SPL variable. The SPL variable receives the returned values from the called function.

An SPL function can return multiple values (that is, values from multiple columns in the same row) into a list of variable names. In other words, the function can have multiple values in its RETURN statement and the LET statement can have multiple variables to receive the returned values.

When you call the function, you must specify all the necessary arguments to the function unless the arguments of the function have default values. If you name one of the parameters in the called function, with syntax such as name = 'smith', you must name all of the parameters.

An SPL function that selects and returns more than one row must be enclosed in a FOREACH loop.

The following two example show valid LET statements.

LET a, b, c = func1(name = 'grok', age = 17);

LET a, b, c = 7, func2('orange', 'green');

The following LET statement is not legal, because it tries to add the output of two functions and then assign the sum to two variables, a and b. You can easily split this LET statement into two legal LET statements.

LET a, b = func1() + func2(); -- ILLEGAL CODE

A function called in a LET statement can have an argument of COLLECTION, SET, MULTISET, or LIST. You can assign the value returned by the function to a variable, for example:

LET d = function1(collection1);

LET a = function2(set1);

In the first statement, the SPL function function1 accepts collection1 (that is, any collection data type) as an argument and returns its value to the variable d. In the second statement, the SPL function function2 accepts set1 as an argument and returns a value to the variable a.