The FOR EACH/FIRST statement processes a set of rows (or process a single row) from an SQL object. All of the statements in the logical scope of the FOR construct apply to each row selected. The FOR construct is iterative. With each iteration, it returns the next row in the requested set.
To process data from the database, you must first define a CA Ideal dataview for the data. The dataview identifies the table, view, or synonym to CA Ideal.
This statement has the following format:
[<<label>>]
[EACH ]
FOR [ALL ] dataview_name [NO UPDATE]
[[THE] FIRST [n]]
[WHERE search‑condition]
[ [ASCENDING ] column [[,] column]... ]
[ORDERED BY [DESCENDING ] ]
[ ]
[[[ASCENDING ] ] ]
[[[DESCENDING ] column [[,] column...] ] ]
statements
[WHEN NONE ]
[ statements]
[WHEN ERROR ]
[ statements]
ENDFOR
An optional 1‑ to 15‑character name of the FOR construct. You can use this label to refer to the construct in QUIT and PROCESS NEXT statements and as the operand of certain functions such as $COUNT.
Indicates that the statements in the scope of the FOR construct apply to every row that satisfies the search condition. The reserved words EACH and ALL can be used interchangeably.
Specifies that the statements in the scope of the FOR construct apply to the first n rows that satisfy the search condition. The value specified for n can be an identifier of a numeric field or a numeric literal that specifies the number of rows to process. The default is FOR FIRST 1. You can add the reserved word THE for readability.
When you use FOR FIRST n with an ORDERED BY clause, the rows that satisfy the search condition are ordered and then the first n ordered rows are selected.
FOR FIRST generates an OPTIMIZE FOR clause on the SQL SELECT statement. If you specify a literal for n, the literal is used in the OPTIMIZE FOR clause. If you specify a host variable for n, the number generated for the OPTIMIZE FOR clause depends on the defined size of the host variable:
The name of the dataview defined for the table, view, or CA Datacom/DB synonym processed.
Note: Do not qualify the dataview name with an authorization ID.
Specifies that the rows processed by this FOR construct are not updated. If NO UPDATE is specified in the FOR construct, FOR FETCH ONLY is included in the generated SQL statements. If used, this clause must immediately follow the dataview name. See also the SET RUN UPDATE command.
Specifies that the statements in the scope of the FOR construct apply to those rows that satisfy the search condition.
Specifies a condition that conforms to the SQL syntax for a search condition with the following qualifications:
You can use SQL functions in a search condition where SQL rules allow the functions. You cannot use PDL built‑in functions in a search condition.
You can use the following predicates in a search condition: IN, BETWEEN, LIKE, NOT IN, NOT BETWEEN, NOT LIKE, IS NULL, and IS NOT NULL. You cannot use the CONTAINS predicate in a search condition.
The LIKE condition can include the ESCAPE option to change the mask character. This allows the default SQL mask characters % and _ to be present in the data being searched.
You can use the keyword CONCAT in place of the concatenation operator, ||.
You cannot include SQL subqueries in a search condition. For this reason, you cannot use the EXISTS predicate.
A search condition requires the CA Ideal syntax for alphanumeric literals and comments rather than the SQL syntax. Hexadecimal literals, for example X'FFFF' are not supported in search conditions.
Host variable names are specified without an initial colon in search conditions.
The search condition can include column names from the specified object and host variable names on either the left or right side of the predicate.
Column names can only be qualified by table or view names to two levels (table_name.col_name) in the search condition.
You cannot use implied predicate subjects and operators or subscripted identifiers in a search condition. You cannot use numeric dynamic match parameters in a search condition unless a default precision was specified.
Determines the logical order in which the rows are processed. If this clause is omitted, the rows are processed in an order the database management system chooses.
Specifies the order, by column values, in which rows are processed: as low value to high value (ASCENDING) or high value to low value (DESCENDING). ASCENDING is the default and applies until DESCENDING is specified. DESCENDING then remains in effect until ASCENDING is specified again. The effect of ASCENDING/DESCENDING depends on the type of the column value and the database management system.
An identifier of a column in the SQL object processed by the FOR. All column identifiers available in an SQL ORDER BY clause are valid (except for the use of an integer representing a column position).
A FOR with an ORDERED BY clause can update the database, unlike an SQL DECLARE CURSOR with an ORDER BY clause. If a FOR with an ORDERED BY clause is needed to update the database, you must define the underlying SQL table with at least one unique index.
You can use the WHERE and ORDERED BY clauses in either order.
PDL statements or SQL statements. The group of statements in the logical scope of a FOR construct can reference or update any column in the row retrieved by the current iteration of the FOR.
You can reference column values for a row only after that row was retrieved by the FOR. You can update column values for a row only in the logical scope of an updateable FOR that processes the row.
An optional postscript that specifies that when none of the rows meets the search condition, the statements following the WHEN NONE execute.
Specifies statements to execute when a dataview error is encountered in the scope of the FOR construct. If WHEN ERROR is not specified, errors are processed by the user‑defined or default error procedure.
The statements specified following a WHEN ERROR clause can access the SQLCA and $ERROR functions and should resolve the error with either a PROCESS NEXT or DO ERROR statement. If processing falls through to the ENDFOR, the $ERROR and SQLCA functions are no longer available. For an example of WHEN ERROR processing, see the examples in this section.
Note: Only dataview errors ($ERROR‑CLASS=DVW) are handled by the WHEN ERROR clause. System and internal errors are handled by the user‑specified or default error procedure.
A reserved word that marks the end of the FOR construct. If FOR statements are nested, the most recent undelimited FOR construct is delimited by the first occurrence of ENDFOR. Each FOR in a nested FOR construct must have a corresponding ENDFOR.
If a row is updated in the logical scope of the FOR construct, the database is updated at the ENDFOR of the current iteration.
You can nest any of the FOR constructs as long as each FOR construct refers to a different dataview. Do not nest a FOR construct for a given dataview in the logical scope of another FOR construct for the same dataview.
When a QUIT is executed in the logical scope of a FOR, the next statement executed is the statement after the ENDFOR. When FOR and LOOP constructs are nested, any construct can be abandoned by referencing the optional label in a QUIT statement. See the QUIT statement in this chapter.
Columns processed in the FOR construct can be referenced in PDL statements using the column name and, if necessary, the dataview name as qualifier.
For example, column ACCT_NO in dataview ACCT can be compared to the field ACCT_NO in panel PNL1:
IF ACCT.ACCT_NO EQ PNL1.ACCT_NO ...
You can also use these data items, in addition to working data, parameter data, and panel fields as host variables in embedded SQL. For example, you can use ACCT.ACCT_NO as a host variable in the WHERE clause of an SQL statement.
You cannot make such references before the FOR is executed.
Statements outside the logical scope of the FOR construct can access, but not update, data from the last row processed for the dataview. The values of the columns processed by the most recent iteration of the FOR are still available after the ENDFOR (see examples in this section), except when the row was deleted or no rows were found (WHEN NONE). This data is available until another FOR accesses the same dataview record.
For example, it might be convenient to first find a row and then refer to its columns rather than nesting the actions in the FOR. You can delegate finding the appropriate row to a lower level procedure.
Updates (changes and deletes) in the logical scope of the FOR. Any update of a column value in the scope of a FOR virtually updates the database. The actual update takes place at the ENDFOR for the current iteration: Therefore, any QUIT or PROCESS NEXT executed in the scope of a FOR abandons the update of the current row even for columns whose values already were changed. A checkpoint in the logical scope of the FOR does not commit the current update because the update does not take place until the ENDFOR. CA Ideal suppresses database writes when it can determine that the database was not altered.
If statements outside the logical scope of the FOR construct attempt to update this data (with a SET, MOVE, and so on), then an execution‑time error results.
These rules apply equally to PDL statements and SQL statements. For example, an embedded SQL FETCH statement can update a host variable referencing a column in the logical scope of a FOR construct. The FOR construct must be updateable and access the table containing that column.
Changing the value of a column for the current row in the scope of a FOR construct has no impact on the selection of the next row because selection is made at the time the FOR construct is initially entered.
You cannot use a FOR construct to update an SQL view defined by the database management system as read‑only.
This note applies to FOR constructs for SQL access that contain a TRANSMIT, CHECKPOINT, or BACKOUT statement, an embedded SQL COMMIT or ROLLBACK statement, or a Debugger breakpoint.
Only a FOR FIRST 1 construct for SQL can contain a TRANSMIT, CHECKPOINT, or BACKOUT statement (or an embedded SQL COMMIT or ROLLBACK statement). If the SQL object is updated, you must define the underlying table with at least one non‑nullable, unique index comprised of 64 columns or less.
If any other type of FOR construct for SQL access contains one of these statements, it must quit processing the specified set of rows after the statement is executed because the set is lost at the commit point. For example, an ERROR PROCEDURE invoked from in a FOR EACH can transmit a panel, but it must then QUIT the FOR.
Only the BACKOUT statement requires that a QUIT be coded to exit the FOR construct.
Performance Implications
You can improve efficiency by using indexed columns in WHERE and ORDERED BY clauses.
As a rule, modify or access only the columns your application needs. Modifying an entire row that was retrieved by a FOR can have significant performance implications. This can happen if you use
In these cases, CA Ideal assumes that all columns are updated, including indexed columns. The database cannot allow access using the index if the indexed column is going to be updated.
SQL Errors
SQL errors and warnings resulting from the execution of either embedded SQL statements or the SQL that CA Ideal generated for a FOR construct are available in the SQL communications area, SQLCA. You can test SQLCA fields for warnings or errors using:
For more information about SQLCA, see the section $SQL Functions (SQL Access Only) in the "Symbolic Debugger Commands" chapter.
If the current iteration of a FOR construct causes an SQL error, control passes to the WHEN ERROR statement at the point of the error. If no WHEN ERROR is coded, control passes to the error procedure.
To include the SQL generated by the FOR construct in a compiler listing, use the LSQL option on the COMPILE or SET COMPILE command.
If a FOR EACH or FOR FIRST construct is determined to be read‑only, regardless of whether NO UPDATE is coded, the FOR FETCH ONLY clause is generated to ensure that the generated SQL performs read‑only access.
Example
FOR EACH DELINQUENT_ACCT
WHERE BALANCE > 200
DO CONTACT_COLLECTOR : if qualification needed,
ENDFOR : use DELINQUENT_ACCT.field
Example
<<EMP_SEARCH>>
FOR EACH EMPLOYEE
WHERE DEPT='D' AND JOB_CODE IN ('J','K','L')
DO CHECK_GOOD_EMP
IF ENOUGH_GOOD_EMP
QUIT EMP_SEARCH
ENDIF
ENDFOR
Example
FOR FIRST INVENTORY_ITEM
WHERE QOH > 50 AND PRICE BETWEEN 100 AND 500
DO PROCESS_ITEM
ENDFOR
FOR THE FIRST 5 INVEN
WHERE PRICE < COST + 100
DO P_5_CHEAP_ITEMS
ENDFOR
Example
FOR EACH EMPLOYEE
WHERE DEPT = 'D'
FOR EACH PAY_REC
WHERE PAY_REC.EMP_NO = EMPLOYEE.EMP_NO
DO PROCESS_PAY
ENDFOR
ENDFOR
Example
FOR FIRST ACCT
WHERE PAST_DUE > 90
ORDERED BY ACCT_NO
: you can refer to or update "ACCT.field" here
WHEN NONE
DO NO_DELINQ_ACCT
ENDFOR
: or you can now refer to "ACCT.field" if present
: you cannot update "ACCT.field" here (unless this
: is a procedure performed by a DO from in the
: FOR)
DO FIND_CUSTOMER
IF CUST_FOUND
: you can refer to "CUST.field" here
ELSE
DO CUST_NOT_FOUND
ENDIF
<<FIND_CUSTOMER>> PROCEDURE
FOR THE CUST
WHERE CUST_NO = TRANS_CUST_NO
SET CUST_FOUND = TRUE
WHEN NONE
SET CUST_FOUND = FALSE
ENDFOR
ENDPROC
Example
For CA Datacom SQL access
FOR FIRST CUSTOMER
WHERE CUSTID = PNL‑CUST
DELETE CUSTOMER
WHEN NONE
NOTIFY 'NO CUSTOMERS FOUND IN' STATE
WHEN ERROR
SELECT FIRST ACTION
WHEN $ERROR‑DVW‑STATUS = ‑175
LIST 'Referential Integrity Error: ' $ERROR‑CONSTRAINT‑NAME
NOTIFY 'Customer ' CUSTID 'has open orders and cannot be deleted'
WHEN OTHER
DO ERROR
ENDSEL
ENDFOR
For DB2
FOR FIRST CUSTOMER
WHERE CUSTID = PNL‑CUST
DELETE CUSTOMER
WHEN NONE
NOTIFY 'NO CUSTOMERS FOUND IN ' STATE
WHEN ERROR
SELECT FIRST ACTION
WHEN $ERROR‑DVW‑STATUS = ‑530
LIST 'Referential Integrity Error: ' $ERROR‑CONSTRAINT‑NAME
NOTIFY 'Customer ' CUSTID 'has open orders and cannot be deleted'
WHEN OTHER
DO ERROR
ENDSEL
ENDFOR
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|