Procedure Definition Language Statements › FOR Statement (SQL Access)

FOR Statement (SQL Access)

The FOR statement is used for reading and updating the database. It is an alternative to coding embedded SQL statements. The FOR construct begins with a FOR and ends with an ENDFOR.

To process data from the database using a FOR construct, you must first define a CA Ideal dataview that identifies an SQL object (table, view, or synonym).

The FOR EACH and FOR FIRST constructs retrieve and update rows from the table or view. These constructs are iterative. With each iteration, they return the next row in the requested set. If a row is updated in the logical scope of the FOR (and updating is allowed), the database is automatically updated at the ENDFOR for the current iteration. SQL UPDATE statements are not needed.

You can use the FOR NEW statement to insert a new row into the table.

A CA Ideal data structure is automatically generated for the row that each iteration of the FOR retrieves. The same data structure is used by any FOR accessing the same SQL object. The fields in this group are identified by the names of columns processed. PDL statements can use them and embedded SQL statements use them as host variables. Embedded SQL statements can also be used independently of a FOR construct to fetch data directly into host structures in working data, parameter data, or panels and to update the database.

For more information about establishing and maintaining dataviews for SQL access, see the Creating Dataviews Guide. For more information about describes preparing and maintaining application plans and packages for DB2 access or access plans for CA Datacom SQL access, see the Administration Guide. For a description about SQL syntax and language elements, see the "Procedure Definition Language Statements" chapter.