An SPL variable was referenced or a row field was set before a value was assigned.
Example of error:
DEFINE my_row_var1 (last_name CHAR(20), id INTEGER); DEFINE i, j INT;
LET i = j + 1; -- error LET my_row_var1.id = new_id; -- error
While
LET my_row_var1 = ROW(NULL::CHAR(20), NULL::INTEGER)::person_rt; LET my_row_var1.id = new_id; -- success
Correction: Assign all variables and parameters valid values before you use them.
Row type variables are allowed in SPL routines. Such variables must be initialized before they are used. To initialize a row type variable, you must initialize the entire variable. Initializing each field will not substitute for initializing the entire variable, and in fact you cannot initialize the individual fields until after you have initialized the entire variable. The following example shows correct and incorrect initialization of row type variables in SPL.
-- Example showing that row type variables in SPL must have the entire -- variable initialized. It is not sufficient to initialize the fields -- without initializing the entire variable. CREATE DATABASE tango;
drop table table1; drop row type person_rt restrict; drop function create_person_rt; CREATE ROW TYPE person_rt (last_name CHAR(20), id INTEGER); CREATE FUNCTION create_person_rt(new_name CHAR(20), new_id INTEGER) RETURNING person_rt; DEFINE my_row_var1 person_rt; -- You cannot initialize the individual fields of the variable until you -- have initialized the entire variable. If the following statement were -- executed at this point, it would fail with error -696. -- LET my_row_var1.id = new_id; -- Initialize the entire variable. LET my_row_var1 = ROW(NULL::CHAR(20), NULL::INTEGER)::person_rt; -- Now set the individual fields. LET my_row_var1.id = new_id; LET my_row_var1.last_name = new_name; RETURN my_row_var1; END FUNCTION; -- Sample usage: CREATE TABLE table1 (person_column person_rt); INSERT INTO table1 (person_column) VALUES (create_person_rt('Uhuru', 128)); CLOSE DATABASE; You must initialize the entire variable because the engine can track whether or not the entire variable has been initialized but cannot track initialization on a field-by-field basis.