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

Statement-Local Variable Expressions:
 
|--SLV_variable-------------------------------------------------|
 

You define an SLV in the call to a user-defined function in the WHERE clause of the SQL 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 (IDS).

Once the user-defined function assigns its OUT or INOUT parameters to the SLVs, you can use these values in other parts of the SQL 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 other words, if a query contains a subquery, an SLV that is visible in the query is also visible to all subqueries of that query.

• In nested queries, the scope of an SLV does not extend upwards.

  In other words, if a query contains a subquery and the SLV is defined in the subquery, it is not visible to the parent query.

• In queries that involve UNION, the SLV is only visible in the query in which it is defined.

  The SLV is not visible to all other queries involved 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, all the above scoping rules apply.

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 Dynamic Server 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 explicitly cast to built-in data types), or must be UDTs that you explicitly cast to built-in data types. All the UDTs, DISTINCT types, and casts must be defined in all of the participating databases.

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

An aggregate expression uses an aggregate function to summarize selected database data. The built-in aggregate functions have the following syntax.

Aggregate Expressions:
 
|--+-+-COUNT(-+-*)--------------------------------+-------------------+-+--|
   | |        '-| Aggregate Scope Qualifiers |--)-'                   | |
   | '-+-AVG------+--(--+-------------------------------------------+-' |
   |   +-MAX------+     +-| Aggregate Scope Qualifiers |--)---------+   |
   |   +-MIN------+     | .-ALL-.                           (1)     |   |
   |   +-SUM------+     '-+-----+--| Subset of Expression |-------)-'   |
   |   +-RANGE----+                                                     |
   |   +-STDEV----+                                                     |
   |   '-VARIANCE-'                                                     |
   |  (2)                               (3)                             |
   '--------| User-Defined Aggregates |---------------------------------'
 
Aggregate Scope Qualifiers:
 
   .-ALL------.
|--+----------+--+----------+--column---------------------------|
   +-DISTINCT-+  +-table .--+
   '-UNIQUE---'  +-view.----+
                 '-synonym.-'
 

1. See page Subset of Expressions Valid in an Aggregate Expression
2. Dynamic Server only
3. See page User-Defined Aggregates

You cannot use an aggregate expression in a condition that is part of a WHERE clause unless you use the aggregate expression within a subquery. You cannot apply an aggregate function to a BYTE or TEXT column. For other general restrictions, see Subset of Expressions Valid in an Aggregate Expression.

An aggregate function returns one value for a set of queried rows. The following examples show aggregate functions in SELECT statements:

SELECT SUM(total_price) FROM items WHERE order_num = 1013

SELECT COUNT(*) FROM orders WHERE order_num = 1001

SELECT MAX(LENGTH(fname) + LENGTH(lname)) FROM customer

If you use an aggregate function and one or more columns in the projection list of the Projection clause, you must put all the column names that are not used as part of an aggregate or time expression in the GROUP BY clause.