Statement-Local Variable Expressions

The Statement-Local Variable Expression specifies a statement-local variable (SLV) that you can use elsewhere in the same SELECT statement.


Element	Description	Restrictions	Syntax
SLV_variable	Statement-local variable (SLV) assigned in a call to a user-defined function in the same query	The SLV_variable exists only while the query is executing. Its name must be unique within the query	Identifier

You define an SLV in the call to a user-defined function in the WHERE clause of the SELECT statement. This user-defined function must be defined with one or more OUT or INOUT parameters. The call to the user-defined function assigns the value of the OUT or INOUT parameters to the SLVs. For more information, see Statement-Local Variable Declaration.

Once the user-defined function assigns its OUT or INOUT parameters to the SLVs, you can use these values in other parts of the same SELECT statement, subject to the following scope-of-reference rules:

The SLV is read-only throughout the query (or subquery) in which it is defined.
The scope of an SLV extends from the query in which the SLV is defined down into all nested subqueries.
In nested queries, the scope of an SLV does not extend upwards.
In other words, if a query contains one or more subqueries, an SLV that is defined in the query is also visible to all the subqueries of that query. But if the SLV is defined in the subquery, it is not visible to the parent query.
In queries that include the UNION operator, the SLV is only visible in the query in which it is defined.
The SLV is not visible to any other queries specified in the UNION.
For INSERT, DELETE, and UPDATE statements, an SLV is not visible outside the SELECT portion of the statement.
Within this SELECT portion of a DML statement, all the above scoping rules apply.

Important: A statement-local variable is in scope only for the duration of a single SQL statement.

The following SELECT statement calls the find_location( ) function in a WHERE clause and defines the rank SLV. Here find_location( ) accepts two values that represent a latitude and a longitude and return the name of the nearest city with an extra value of type INTEGER that represents the population rank of the city.

SELECT zip_code_t FROM address
   WHERE address.city = find_location(32.1, 35.7, rank # INT)
   AND rank < 101;

When execution of the find_location() function completes successfully, the function has initialized the rank SLV. The SELECT then uses this rank value in a second WHERE clause condition. In this example, the Statement-Local Variable Expression is the variable rank in the second WHERE clause condition:

rank < 101

The number of OUT and INOUT parameters and SLVs that a UDF can have is not restricted. (Releases of HCL OneDB™ earlier than Version 9.4 restricted user-defined functions to a single OUT parameter and no INOUT parameters, thereby restricting the number of SLVs to no more than one.)

If the user-defined function that initializes the SLVs is not executed in an iteration of the statement, the SLVs each have a value of NULL. Values of SLVs do not persist across iterations of the statement. At the start of each iteration, the database server sets the SLV values to NULL.

The following partial statement calls two user-defined functions with OUT parameters, whose values are referenced with the SLV names out1 and out2:

SELECT... 
   WHERE func_2(x, out1 # INTEGER) < 100 
   AND (out1 = 12 OR out1 = 13) 
   AND func_3(a, out2 # FLOAT) = "SAN FRANCISCO" 
   AND out2 = 3.1416;

If a function assigns one or more OUT or INOUT parameter values from another database of the local database server to SLVs, the values must be of built-in data types, or DISTINCT data types whose base types are built-in data types (and that you cast explicitly to built-in data types), or must be opaque UDTs that you cast explicitly to built-in data types. All the opaque UDTs, DISTINCT types, type hierarchies, and casts must be defined exactly the same way in all of the participating databases.

For more information on how to write a user-defined function with OUT or INOUT parameters, see HCL OneDB User-Defined Routines and Data Types Developer's Guide.