The FOR EACH, FOR FIRST, and FOR ANY statements process a set of records (or process a single record) from a CA Datacom/DB table. All of the statements in the FOR construct apply to each record selected.
The FOR construct is iterative. With each iteration, it returns the next record in the requested set. FOR FIRST, FOR EACH, and FOR ANY are the only constructs that update or delete a record.
To process data from the database, you must first define a dataview to CA Ideal for the data. The dataview defines the fields that are available to the application.
This statement has the following format:
[<<label>>]
[EACH ]
FOR [ALL ] dataview_name[NO UPDATE]
[[THE] FIRST [n] ]
[ANY n ]
[WHERE where condition]
[ [ASCENDING ] ]
[ORDERED BY [UNIQUE] [DESCENDING] id [[,]id]… ]
[ [[ASCENDING ] ]
[ [[DESCENDING] id [[,] id…] ]
statements
[WHEN NONE ]
[ statements]
[WHEN ERROR ]
[ statements]
ENDFOR
Specifies 1‑ to 15‑character name of the FOR construct. You can refer to the construct in QUIT and PROCESS NEXT statements and as the operand of certain functions such as $COUNT.
Indicate that the statements in the FOR construct apply to every record that satisfies the where condition. You can use the reserved words EACH and ALL interchangeably.
Specifies that the statements in the scope of the FOR construct apply to the first n records that satisfy the where condition. The value specified for n can be an identifier of a numeric field or a numeric literal that specifies the number of records to process. The default is FIRST 1. You can add the reserved word, THE, for readability.
When you use FOR FIRST n with a where condition and an ORDERED BY clause, all records that satisfy the where condition are ordered and then the first n ordered records are selected. The difference between FOR FIRST and FOR ANY is illustrated in the examples in this section.
Specifies that the statements in the scope of the FOR construct apply to any n records that satisfy the where condition. The value specified for n can be an identifier of a numeric field or a numeric literal that specifies the number of records to process. The value of n is required for FOR ANY.
When you use FOR ANY n with a WHERE condition and an ORDERED BY clause, the first n records that satisfy the WHERE condition are selected and then ordered. The difference between FOR FIRST and FOR ANY is illustrated in the examples in this section.
The name of the dataview processed.
Specifies that the records processed by this FOR construct are not updated and, therefore, are not held under exclusive control. This applies even if the dataview is defined as updatable in CA Datadictionary. If used, this clause must immediately follow the dataview name. You can use the SET RUN UPDATE command to temporarily suppress updates; however, SET RUN UPDATE is primarily intended for testing purposes. For more information, see the Command Reference Guide.
Specifies that the statements in the scope of the FOR construct apply to those records that satisfy the specified condition.
A condition (as defined in the PDL Language Elements section in chapter SQL Concepts and Language Elements with the following further qualifications:
The right‑hand operand of a relational‑expression can be any arithmetic or alphanumeric expression, but cannot reference any fields in the dataview named in the FOR clause.
A where‑condition is the only condition that can contain the special relational operators CONTAINS and NOT CONTAINS. For an explanation and examples of CONTAINS and NOT CONTAINS, see the definition of the where‑condition in the PDL Language Elements section in "SQL Concepts and Language Elements" chapter and the $FIXED‑MASK function in "Symbolic Debugger Commands" chapter.
If the condition is a condition name, it must be from the dataview being referenced. If the condition name is used for more than one data structure, the condition name must be qualified.
Where‑conditions cannot be Boolean functions or flags.
Any subscripts used in the where‑condition must not be numeric fields in the dataview being referenced.
Determines the logical order in which the records are processed. If this clause is omitted, the dataview records are processed in an optimal order.
Note: This optimal order is determined dynamically at program execution time and can change based on the contents of the database and by the release level of the DBMS.
Specifies that only one record with each unique value of the ORDERED BY identifiers is processed.
Specifies whether the identified fields are processed from low value to high value (ASCENDING) or high value to low value (DESCENDING). ASCENDING is the default and applies to each identified field until DESCENDING is specified. DESCENDING then remains in effect for each additional identified field until ASCENDING is specified again.
The effect of ASCENDING/DESCENDING depends on the type of the identified field. Type X fields are ordered in ascending or descending EBCDIC order. Type N and type D fields in ascending or descending numeric order.
The identifier of a numeric or alphanumeric field or alpha‑group. Identifiers can be subscripted, but not by fields in the dataview being referenced.
PDL statements. The statements in the logical scope of a FOR construct can reference any field in the record most recently processed by the FOR.
An optional postscript that specifies that when none of the records meets the where condition, the statements following the WHEN NONE are executed.
Specifies statements to execute when a dataview error is encountered in the scope of the FOR construct. If WHEN ERROR is not specified, user‑defined or default error procedures process the errors.
The statements specified following a WHEN ERROR clause can access $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 functions no longer are available. For more information about WHEN ERROR processing, see the following examples in this section.
Note: Only the WHEN ERROR clauses handles dataview errors ($ERROR‑CLASS=DVW). User‑specified or default error procedures handle system and internal errors.
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.
The actual update takes place at the ENDFOR for the current iteration for all changes except deletes.
Fields processed by each iteration of the FOR construct can be referenced in PDL statements. The identifier is the name of the field defined in the dataview or the field name with the dataview name as qualifier. For example, field 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 cannot make such references before the FOR is executed.
Sometimes it is convenient to first process a dataview record and then refer to its fields, rather than coding the actions in the FOR. For example, you can delegate finding the appropriate record to a lower level procedure.
Statements outside the logical scope of the FOR construct can access but not update data in the dataview record. The values of the fields processed by the most recent iteration of the FOR are still available after the ENDFOR (see examples at the end of this section), except when the record was deleted or no records were found (WHEN NONE).
Data in the dataview record is available until another FOR accesses the same dataview record.
Updates (changes and deletes) must be done in the logical scope of the FOR. Any update of a dataview field in the logical scope of a FOR virtually updates the database. For changes (but not for deletes), 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 record even for fields whose values already were changed with SET or MOVE statements and 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.
If statements outside the logical scope of the FOR construct attempt to update the record (with a SET, MOVE, and so on), an execution‑time error results.
CA Ideal suppresses database writes when it can determine that the data was not altered. If a call is made to a non‑ideal subprogram that updates the database, specify UPDATES DB or UPDATES DB2 on the subprogram identification panel (described in the Creating Programs Guide). This insures that CA Ideal does not suppress CHECKPOINT, BACKOUT, ROLLBACK, or SQL COMMIT statements in any transaction where the subprogram is called. If statements outside the logical scope of the FOR construct attempt to update the record (with a SET, MOVE, and so on), an execution‑time error results.
Changing the value of a field in the logical scope of a FOR construct has no impact on the selection of the next record since selection is made at the time the FOR block is initially entered.
You can nest any of the FOR constructs as long as each FOR construct refers to a different dataview. You cannot nest a FOR construct for a given dataview in another FOR construct for the same dataview.
If the dataview was defined as updateable, the logical scope of the FOR construct is implicitly the scope over which each successive record is held exclusively. A transmit in the FOR construct releases exclusive control. After the transmit, CA Ideal ensures the integrity of the record by rereading it and causing an error if it was changed. A transmit in the FOR construct releases exclusive control; after the transmit, CA Ideal/PC ensures the integrity of the record by rereading it and causing an error if it was changed.
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).
If more than one position (record) of a dataview is needed simultaneously, do one of the following:
You can use the WHERE and ORDERED BY clauses in either order.
You can improve efficiency by using fields that are keys in WHERE clauses. ORDERED BY clauses are most efficient when the sequence of the fields in the clause matches the sequence of the fields in a complete key.
Do not nest a FOR construct for a given dataview in another FOR construct for the same dataview. However, you can nest a FOR NEW for the same dataview in the WHEN NONE of the outer FOR.
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 = 'J'
DO CHECK‑GOOD‑EMP
IF ENOUGH‑GOOD‑EMP
QUIT EMP‑SEARCH
ENDIF
ENDFOR
Example
FOR FIRST INVENTORY‑ITEM
WHERE QOH > 50 AND PRICE < 500
DO PROCESS‑ITEM
ENDFOR
Example
FOR THE FIRST 5 INVEN
WHERE PRICE < 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
: 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
Example
<<FIND‑CUSTOMER>> PROCEDURE
FOR THE CUST
WHERE CUST‑NO = TRANS‑CUST‑NO
SET CUST‑FOUND = TRUE
WHEN NONE
SET CUST‑FOUND = FALSE
ENDFOR
Example
FOR FIRST 20 ITEMS
WHERE UNIT‑PRICE > 10
ORDERED BY SHORT‑DESC
LIST ITM‑ID, SHORT‑DESC, UNIT‑PRICE
WHEN NONE
DO INCREASE‑PRICE
ENDFOR
Returns...
A60009 ADAPTER 24.99 A70002 ANTENNA 19.99 A70003 ANTENNA 19.99 O10002 ARMCHAIR 304.00 H20000 BEDBOARD 54.99 H20002 BEDWEDGE 18.99
Example
FOR ANY 20 TIMES
WHERE UNIT_PRICE > 10
ORDERED BY SHORT_DESC
LIST ITEM_ID, SHORT_DESC, UNIT_PRICE
WHEN NONE
DO INCREASE_PRICE
ENDFOR
Returns...
A30001 CADDY 89.99 A30000 CONSOLE 39.99 A40001 COVER 19.99 A40002 COVER 199.99 A40003 COVER 199.99 A50001 CUSHION 14.99
Example
FOR FIRST CUSTOMER
WHERE CUSTID = PNL‑CUST
DELETE CUSTOMER
WHEN NONE
NOTIFY 'NO CUSTOMERS FOUND'
WHEN ERROR
SELECT FIRST ACTION
WHEN $ERROR‑DVW‑STATUS = 94 AND
$ERROR‑INTERNAL DVW‑STATUS = 31
LIST 'Constraint Error: ' $ERROR‑CONSTRAINT‑NAME
NOTIFY 'Customer ' CUSTID 'has open orders and cannot be deleted'
WHEN $ERROR‑DVW‑STATUS = 36
NOTIFY 'Contact Database Administrator with error information'
WHEN OTHER
DO ERROR
ENDSEL
ENDFOR
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|