FOR_EACH_SQL_QUERY

This activity queries a database using an SQL SELECT statement and iterates for each row in the result set.

It is very similar to the SQL_QUERY activity, but functions as an iterator activity, instead of populating sets of variable lists with all the results at once, as SQL_QUERY does.  Using this iterator activity might be more convenient or offer performance benefits over an alternative implementation using SQL_QUERY.

This activity does not support the use of SQL parameter markers (usually designated by a question mark) in the SQL statement.  If you need the SQL statement to be variable (for example, selection values in a WHERE clause), then you must dynamically construct the SQL statement to include the variable values as constants.

Note this activity is not restartable since it cannot guarantee that the result set after a restart will be the same as the result set in effect when a processing sequence error occurred.

For more information about the SQL database activities, refer to the description of the SQL_CONNECT activity.  For a list of supplied working (*) examples using the SQL database activities, refer to:

Example Processing Sequences using the SQL database activities

INPUT Parameters:

SQLHANDLE: Required

This parameter must specify the connection handle value that identifies the SQL connection upon which this activity should operate.  The connection handle value is returned by the SQL_CONNECT activity.

SQLQUERY: Required

This parameter must specify the SQL SELECT statement that will execute on the target database for the query.  The following are examples of SQL SELECT statements that might be specified in this parameter:

select distinct bchnum from tutordh

select bchnum, ordnum, cusnum, orddat from tutordh

where bchnum = '12345687890'

In some cases, a single value may not be sufficiently large to hold some queries.  For this reason, this parameter is defined as a variable list.  This means that you can split your long SQL statement into more than one part and provide the parts in this parameter using a variable list.  If you do this, then the SQL_QUERY activity will re-assemble them into a single statement before execution.

SQLMAXROWS: Optional

This parameter specifies a maximum number of rows to be returned.  This guards against the possibility of SELECT statements that select much more data than was intended.

If not specified, a default value of 100 (one hundred) is used.  Remember that the SQL database activities are not intended and not usually suitable for high-throughput, high-volume database operations.  If you expect that your query will return a large number of records, then you should possibly consider an alternate implementation, such as using a Transformation Map.

OUTPUT Parameters:

SQLCOLUMN1
SQLCOLUMN2
…
SQLCOLUMN25

Upon each iteration, these variables will contain the values for the corresponding column for the current row in the result set selected by the query.

You should specify the name of a variable that will contain the value for each column used in your query, up to a maximum of 25.

You may specify fewer output variables than in your query, however, if you specify more, then a run-time error will occur, for example:

ERROR – Descriptor index not valid, or

ERROR – The index 10 is out of range