Starts an iterating block that reads a record from each of one or more tables at the start of each block iteration. Use an END statement to end a FOR block.
[ label: ] FOR [ EACH | FIRST | LAST ]record-phrase [ , [ EACH | FIRST | LAST ]record-phrase ]... [query-tuning-phrase ] [ BREAK ] [ BY expression[ DESCENDING ] | COLLATE ( string , strength[ , collation] ) [ DESCENDING ] ]... [variable = expression1 TO expression2[ BY k ]] [ WHILE expression ] [ TRANSACTION ] [ STOP-AFTER expression ] [on-error-phrase ] [on-endkey-phrase ] [on-quit-phrase ] [on-stop-phrase ] [frame-phrase ] : for-body |
Uses the criteria in the record-phrase to find the first record in the table that meets that criteria. The AVM finds the first record before any sorting.
The following statement displays Customer 1 (CustNum is the primary index of the Customer table), not the Customer with the lowest CreditLimit:
The following statement displays the Customer with the lowest CreditLimit:
See the Notes section for more information on using this option.
Uses the criteria in the record-phrase to find the last record in the table that meets that criteria. The AVM finds the last record before sorting.
The procedure above displays the Customer with the highest Customer number (CustNum is the primary index of the Customer table), not the Customer with the highest CreditLimit.
A procedure that displays the Customer with the highest CreditLimit looks like the following:
See the Notes section for more information on using this option.
Identifies the set of records you want to retrieve. This can also be the built-in buffer name, proc-text-buffer, that you can use to return table rows from a stored procedure.
To use FOR EACH/FIRST/LAST to access a record in a table defined for multiple databases, you must qualify the record's table name with the database name.
This is the syntax for record-phrase:
record
[ constant ] [ OF table]
[ USE-INDEX index | TABLE-SCAN ]
[ USING [ FRAME frame ] field
[ AND [ FRAME frame ] field ] ...
]
[ WHERE expression ]
[ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ]
[ NO-PREFETCH ]
|
Specifying multiple occurrences of record-phrase selects the tables using an inner join.
For more information on record-phrase and inner joins, see the Record phrase reference entry.
Allows programmatic control over the execution of a DataServer query. Following is the syntax for the query-tuning-phrase:
QUERY-TUNING
(
{[ LOOKAHEAD [ CACHE-SIZE integer ]
| NO-LOOKAHEAD
]
[ DEBUG { SQL | EXTENDED }| NO-DEBUG ]
[ SEPARATE-CONNECTION | NO-SEPARATE-CONNECTION ]
[ JOIN-BY-SQLDB | NO-JOIN-BY-SQLDB ]
[ BIND-WHERE | NO-BIND-WHERE ]
[ INDEX-HINT | NO-INDEX-HINT ]
}
)
|
For more information on the query-tuning-phrase, refer to the appropriate DataServer Guide (OpenEdge Data Management: DataServer for ODBC, OpenEdge Data Management: DataServer for Oracle, or OpenEdge Data Management: DataServer for Microsoft SQL Server).
Over a series of block iterations, you might want to do some work based on whether the value of a certain field changes. This field defines a break group. For example, you might be accumulating some value, such as a total. You use the BREAK option to define State as the break group. For example:
FOR EACH Customer BREAK BY Customer.State: DISPLAY Customer.State Customer.Name Customer.CreditLimit (TOTAL BY state). END. |
Here, the AVM accumulates the total CreditLimit for all the customers in the Customer table. Each time the value of the State field changes, the AVM displays a subtotal of the CreditLimit values for customers in that state.
You can use the BREAK option anywhere in the block header, but you must also use the BY option to name a sort field.
You can use the BREAK option in conjunction with the ACCUMULATE statement and ACCUM function. For more information, see the reference entries for those language elements.
Sorts the selected records by the value of expression. If you do not use the BY option, the AVM retrieves records in the order of the index used to satisfy the record-phrase criteria, or the primary index if no criteria is given. The DESCENDING option sorts the records in descending order (not in the default ascending order).
You can use multiple BY options to do multi-level sorting. For example:
Here, the Customers are sorted in order by CreditLimit. Within each CreditLimit value, Customers are sorted alphabetically by Name.
There is a performance benefit if an index on expression exists: BREAK BY does not have to perform the sort that is otherwise required to evaluate FIRST, LAST, FIRST-OF, and LAST-OF expressions.
Generates the collation value of a string after applying a particular strength, and optionally, a particular collation. The DESCENDING option sorts the records in descending order (not in default ascending order).
A CHARACTER expression that evaluates to an ABL comparison strength or an International Components for Unicode (ICU) comparison strength.
The ABL comparison strengths include:
The ICU comparison strengths include:
A CHARACTER expression that evaluates to the name of an ABL collation table or ICU collation. If collation does not appear, COLLATE uses the collation table of the client.
The AVM reports an error and stops execution if one of the following occurs:
Identifies the name of a field or variable whose value you are incrementing in a loop. The expression1 is the starting value for variable on the first iteration of the loop. The k is the amount to add to variable after each iteration and must be a constant. It (k) defaults to 1. The variable, expression1, and expression2 parameters must be integers.
When variable exceeds expression2 (or is less than expression2 if k is negative) the loop ends. Since expression1 is compared to expression2 at the start of the first iteration of the block, the block can be executed 0 times. The AVM re-evaluates expression2 on each iteration of the block.
The STOP-AFTER phrase specifies a time-out value for a DO, FOR, or REPEAT block. The integer expression specifies the number of seconds each iteration of a block has until a time-out occurs. If a time-out occurs, the AVM raises the STOP condition and default STOP condition handling occurs. Use an ON STOP phrase on the block (or an enclosing block) to alter the default STOP condition handling.
If the block iteration completes before the specified time expires, the timer resets to expression for the next iteration. In other words, the timer is limited to the scope of a single block iteration. If a block with a STOP-AFTER phrase encloses another block or calls another block, the timer continues while the inner blocks execute.
If a block with a STOP-AFTER phrase contains a nested block with a STOP-AFTER phrase, then each has a timer in effect. If the outer block timer expires while the inner block is executing, the STOP condition is raised even if the timer for the inner block has not expired.
If the STOP condition is handled and execution resumes within the scope of a block with a STOP-AFTER phrase, no timer is in effect until the next iteration of a block with a STOP-AFTER phrase. In other words, all old timers are dismissed but new timers can now be established.
When the timer expires, the STOP condition is raised on the current statement.
Two important use cases for the STOP-AFTER phrase are to time-limit dynamic queries and to time-limit a procedure call. The following example time-limits a procedure call using a RUN statement:
DEFINE VARIABLE cnt as INTEGER INITIAL 0.
PROCEDURE bumpCnt:
cnt = cnt + 1.
END.
DO STOP-AFTER 5:
RUN bumpCnt.
END.
|
Use this technique to also make timed calls to class methods and user-defined functions.
The following example is simplified code that lets you try different STOP-AFTER cases.
DEFINE VARIABLE EndlessCount AS INTEGER INITIAL 0.
DO STOP-AFTER 5 ON STOP UNDO, LEAVE:
FOR EACH Customer STOP-AFTER 1:
ASSIGN EndlessCount = EndlessCount + 1.
/* Try a complex operation on a Customer record to use up
the timer in a single iteration and raise the
STOP condition in the inner block */
END.
MESSAGE
"Procedure half complete. Endlesscount = " EndlessCount ".".
REPEAT STOP-AFTER 1:
ASSIGN EndlessCount = EndlessCount + 1.
/*IF EndlessCount > 2000 THEN LEAVE. */
END.
MESSAGE
"Procedure nearly complete. Endlesscount = " EndlessCount "." .
END.
MESSAGE "Procedure complete. Endlesscount = " EndlessCount "." .
|
If you run this code as is, the outer DO block establishes a 5 second time limit for the work of the DO block and all inner blocks. When the inner FOR EACH block starts, another timer is established for the first iteration of this block. When the first FOR EACH iteration completes, its timer is reset to 1 second for the next iteration. Meanwhile, the outer timer on the DO block continues without interruption.
The FOR EACH block completes and execution continues forward to the REPEAT block, which is an endless loop. The REPEAT block also has a 1 second timer for each iteration of the block. At some point, the outer 5 second timer elapses and the AVM raises the STOP condition. The STOP condition is raised on the statement the AVM was executing when the timer elapsed. Normal STOP handling proceeds from that point.
As the stack unwinds during STOP processing, the AVM encounters the ON STOP phrase on the DO block. The ON STOP phrase dismisses the STOP condition and resumes normal execution with the next statement following the DO block, as directed by the LEAVE option.
If you remove the comments from the IF statement in the REPEAT block, the block will complete within the outer time limit and the STOP condition is not raised.
If you want to experiment with elapsed timers on an inner block, insert a complex operation inside the FOR EACH block.
In the following example, the STOP-AFTER expression is modified during program execution:
DEFINE VARIABLE ix AS INTEGER NO-UNDO. DEFINE VARIABLE stopTime AS INTEGER NO-UNDO INITIAL 30. DO WHILE TRUE STOP-AFTER stopTime ON STOP UNDO, LEAVE: RUN spinHere (10000). stopTime = stopTime / 2. END. MESSAGE "program finished". PROCEDURE spinHere: DEFINE INPUT PARAMETER spinLimit AS INT64 NO-UNDO. DEFINE VARIABLE endTime AS INT64 NO-UNDO. DEFINE VARIABLE loopFlag AS LOGICAL NO-UNDO. ASSIGN loopFlag = TRUE endTime = ETIME(FALSE) + spinLimit. DO WHILE loopFlag: IF (ETIME(FALSE) > endTime) THEN loopFlag = FALSE. END. END PROCEDURE. |
Because the STOP-AFTER expression is re-evaluated for each iteration of a looping block, any changes made to the expression during the iteration effect the timer for the block. In the example, the STOP-AFTER time limit is specified by the variable stopTime, which is initially set to 30 seconds. The procedure contains an iterating block which runs a procedure that executes for 10 seconds.
On the first iteration of the DO WHILE TRUE loop, stopTime is 30 seconds. The loop executes for 10 seconds, and then divides stopTime by 2. On the second iteration, the stopTime is 15 seconds; again the loop executes for 10 seconds, and then divides stopTime by 2. On the third iteration, the stopTime is 8 seconds. This time, the procedure spinHere runs for 8 seconds and then raises STOP. The STOP condition is handled by the DO block, and then the program displays the message program finished.
If a code block is called with a time limit of zero, the block is executed as if the STOP-AFTER phrase was omitted from the block declaration.
Consider the following example:
DEFINE VARIABLE barLimit AS INTEGER NO-UNDO. DEFINE VARIABLE ix AS INTEGER NO-UNDO INITIAL 1. DO STOP-AFTER 10 ON STOP UNDO, LEAVE: RUN foo. END. PROCEDURE foo: RUN bar. END PROCEDURE. PROCEDURE bar: DO WHILE ix > 0 STOP-AFTER barLimit: ix = ix + 1. END. END PROCEDURE. |
In this example, procedure foo is run from within a timed block with a 10 second time limit; procedure bar is called from within the timed block, and contains an iterating block that specifies the STOP-AFTER phrase. Because the value of the STOP-AFTER expression evaluates to zero (that is, the current value of the barLimit variable), the block within bar is executed as an untimed block. However, the rules for execution of an untimed block within a timed block apply, so the untimed block in bar is executed with an implicit iteration time limit of 10 seconds.
Other points to consider are:
Describes the processing that takes place when there is an error during a block. This is the syntax for the ON ERROR phrase:
ON ERROR UNDO
[label1 ]
[ , LEAVE [ label2 ]
| , NEXT [ label2 ]
| , RETRY [ label1 ]
| , RETURN [ return-value |
ERROR [ return-value | error-object-expression]|
NO-APPLY ]
| , THROW
]
|
For more information, see the ON ERROR phrase reference entry.
Describes the processing that takes place when the ENDKEY condition occurs during a block. This is the syntax for the ON ENDKEY phrase:
ON ENDKEY UNDO
[ label1 ]
[ , LEAVE [ label2 ]
| , NEXT [ label2 ]
| , RETRY [ label1 ]
| , RETURN [ return-value |
ERROR [ return-value | error-object-expression ]|
NO-APPLY ]
|
For more information, see the ON ENDKEY phrase reference entry.
Describes the processing that takes place when a QUIT statement is executed during a block. This is the syntax for the ON QUIT phrase:
ON QUIT
[ UNDO [ label1 ]]
[ , LEAVE [ label2 ]
| , NEXT [ label2 ]
| , RETRY [ label1 ]
| , RETURN [ return-value |
ERROR [ return-value | error-object-expression ]|
NO-APPLY ]
]
|
For more information, see the ON QUIT phrase reference entry.
Describes the processing that takes place when the STOP conditions occurs during a block. This is the syntax for the ON STOP phrase:
ON STOP UNDO
[ label1 ]
[ , LEAVE [ label2 ]
| , NEXT [ label2 ]
| , RETRY [ label1 ]
| , RETURN [ return-value |
ERROR [ return-value |error-object-expression]|
NO-APPLY ]
]
|
For more information, see the ON STOP phrase reference entry.
The body of the FOR block. Define for-body using the following syntax:
The logic of the FOR block. This logic can contain the ABL statements allowed within the routine-level block (e.g., procedure, user-defined, or method) where the FOR statement is defined.
Each logic statement must end with a period.
This procedure reads Customer records that have a CustNum less than 12, sorting the records in order by State before displaying them:
r-fore.p
FOR EACH Customer NO-LOCK WHERE Customer.CustNum < 12 BY Customer.State: DISPLAY Customer.CustNum Customer.Name Customer.City Customer.State. END. |
The next procedure gets information from four related tables (Customer, Order, OrderLine, and Item) and displays some information from each. Before displaying the information, the FOR EACH statement sorts it in order by the PromiseDate field, then, within that field, in order by CustNum. Within the CustNum field, the data is sorted by the LineNum field.
r-fore2.p
FOR EACH Customer NO-LOCK, EACH Order OF Customer NO-LOCK, EACH OrderLine OF Order NO-LOCK, Item OF OrderLine NO-LOCK BY Order.PromiseDate BY Customer.CustNum BY OrderLine.LineNum: DISPLAY Order.PromiseDate Customer.CustNum Order.OrderNum OrderLine.LineNum Item.ItemNum Item.ItemName. END. |
This procedure uses the LAST option to display information on the last Order of each Customer:
r-fore3.p
If you specify the -v6q startup parameter, an index component is involved in an equality match if it is used in the Record phrase conditions in the following form:
Where the expression is independent of any fields in the table that the index is being selected from. A condition involving OF and USING are equivalent to this form. A field is involved in a range match if it is used in a condition of this form:
The BEGINS operator translates into two range matches for a field.
An equality or range match is considered active if the equality or range condition stands on its own or is related to other conditions solely through the AND operator (for example, not through OR or NOT).
A field is involved in a sort match if it is used in a BY option of this form:
For each index in the table, the ABL compiler looks at each index component in turn and counts the number of active equality, range, and sort matches. ABL ignores the counts for any components of an index that occur after a component that has no active equality match. ABL compares the results of this count and selects the best index. ABL uses the following order to determine the better of any two indexes:
The following table shows the index ABL selects and the bracketed part of the index for various record-phrases.
| Record phrase | Index selected | Bracketing on |
|---|---|---|
f WHERE a = 3 AND b = 2 AND c = 3 |
I1 | a + b + c |
f WHERE a = 3 |
I1 | a |
f WHERE c = 1 |
I2 | c |
f WHERE a = 3 AND b > 7 AND c = 3 |
I1 | a + b |
f WHERE a = 3 AND c = 4 |
I1 | a |
f WHERE b = 5 |
I1 | None of the fields1 |
f WHERE a = 1 OR b >5 |
I1 | None of the fields1 |
f WHERE (a >= a1 AND a <= a2) OR (a1=0) |
I1 | None of the fields2 |
f WHERE a >= (IF a1 NE 0 THEN a1 ELSE -99999999) AND a <= (IF a1 NE 0 THEN a2 ELSE +99999999) |
I1 | a2 |
Suppose you were interested in displaying the date when each Customer first placed an order. This procedure displays the Customer number and date of the first Order:
FOR EACH Customer NO-LOCK, FIRST Order OF Customer NO-LOCK: DISPLAY Order.CustNum Order.OrderDate. END. |
The following procedure displays the last Order Line of every Order, sorted by the Price of the Item and by the Promised Date of the Order:
DISPLAY "Show the last order-line of each order," SKIP "sorted by the item's price and the" SKIP "promised date of the order." WITH CENTERED. FOR EACH Order NO-LOCK, LAST OrderLine OF Order NO-LOCK, Item OF OrderLine NO-LOCK BY Item.Price BY Order.PromiseDate: DISPLAY Order.OrderNum OrderLine.LineNum Item.ItemNum OrderLine.Price Order.PromiseDate WITH TITLE "For FIRST/LAST" CENTERED. END. |