Variables that Conflict with Column Names

If you use the same identifier for an SPL variable and a column name, then within the scope of reference of the variable, the database server interprets any instance of the unqualified identifier as a variable. To use the identifier to specify a column name, use table.column notation to qualify the column name with the table name. In the following example, the procedure variable lname is the same as the column name. In the following SELECT statement, customer.lname is a column in the database and lname is an SPL variable:

CREATE PROCEDURE table_test()
DEFINE lname CHAR(15);
LET lname = "Miller";
SELECT customer.lname FROM customer INTO lname
   WHERE customer_num = 502;

This example is valid, but relying on the rules of precedence of HCL OneDB™ to resolve name conflicts between SPL variables and column names might make your code difficult for human readers to interpret and to maintain. An alternative to reusing the same identifier as a variable and as a column name is for the DEFINE statement to declare some prefix to the identifier, such as v_lname in this example, to indicate that this variable stores the value of the column lname.