Iterator Functions

The FROM clause can include a call to an iterator function to specify the source for a query. An iterator function is a user-defined function that returns to its calling SQL statement multiple times, each time returning at least one value.

You can query the returned result set of an iterator UDR using a virtual table interface. This syntax fragment is part of the FROM Clause.

Iterator

Notes:

¹ HCL OneDB™ extension
² See Routine Parameter List


Element	Description	Restrictions	Syntax
column	Name declared here for a virtual column in table	Must be unique among column names in table, and cannot include qualifiers.	Identifier
iterator	Name of the iterator function	Must be registered in the database	Identifier
table	Name declared here for virtual table holding the iterator result set	Cannot include qualifiers	Identifier

The keyword FUNCTION (or PROCEDURE) was required in releases earlier than HCL OneDB 10.5. These keyword extensions to the ANSI/ISO standard for SQL are optional in this release, and have no effect. The following two query specifications, which specify fibGen( ) as an iterator function, are equivalent:

SELECT * FROM TABLE FUNCTION ( fibGen(10)); 
SELECT * FROM TABLE ( fibGen(10));

The table can only be referenced within the context of this query. After the SELECT statement terminates, the virtual table no longer exists.

The number of columns must match the number of values returned by the iterator. An external function can return no more than one value (but that can be of a collection data type). An SPL routine can return multiple values.

The database server issues error -595, however, if any argument to the iterator table function is an aggregate expression.

To reference the virtual table columns in other parts of the SELECT statement, for example, in the WHERE clause or HAVING clause, you must declare its name and the virtual column names in the FROM clause. You do not need to declare the table name or column names in the FROM clause if you use the asterisk notation in the Select list of the Projection clause:

SELECT * FROM ...

For more information and examples of using iterator functions in queries, see HCL OneDB User-Defined Routines and Data Types Developer's Guide.