Iterator Functions (IDS)

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > SELECT >

Iterator Functions (IDS)

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 several times, each time returning a value.

You can query the returned result set of an iterator UDR using a virtual table interface. Use this syntax to invoke an iterator function in the FROM clause:

Iterator:
 
|--TABLE-------------------------------------------------------->
 
>--(--FUNCTION--iterator--(--+---------------------------------+--)--)-->
                             |                            (1)  |
                             '-| Routine Parameter List |------'
 
>--+-------------------------------------+----------------------|
   '-+----+--table--+------------------+-'
     '-AS-'         |    .-,------.    |
                    |    V        |    |
                    '-(----column-+--)-'

Notes:

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. 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 iterator result set	Cannot include qualifiers	Identifier

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.

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 Projection list of the SELECT clause:

SELECT * FROM ...

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