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.

Data movement



Syntax

This is the syntax for the 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-phrase ] 
  [ on-error-phrase ] 
  [ on-endkey-phrase ] 
  [ on-quit-phrase ] 
  [ on-stop-phrase ] 
  [ frame-phrase ] :

  <block of statements>

END.
EACH
Starts an iterating block, finding a single record on each iteration. If you do not use the EACH keyword, the record-phrase you use must identify exactly one record in the table.
FIRST

Uses the criteria in the record-phrase to find the first record in the table that meets that criteria. Note that the AVM finds the first record before any sorting. Therefore a BY option, if specified, would have no effect on the record returned.

Note: The state of the buffer outside of the FOR FIRST loop is undefined and records may or may not be available. Any code that depends on the buffer state should be moved into the FOR FIRST loop.
LAST

Uses the criteria in the record-phrase to find the last record in the table that meets that criteria. Note that the AVM finds the last record before any sorting. Therefore a BY option, if specified, would have no effect on the record returned.

record-phrase

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 [ field-list ]
  [ constant ] [ OF table]
  [ USE-INDEX index | TABLE-SCAN ]
  [ USING [ FRAME frame ] field
       [ AND [ FRAME frame ] field ] ...]
  [ WHERE expression ]
  [ TENANT-WHERE expression [ SKIP-GROUP-DUPLICATES ]]
  [ 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 Record phrase.

query-tuning-phrase

Allows programmatic control over the execution of a DataServer query. This 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 (Use the Oracle Data Server or Use the Microsoft SQL Data Server).

BREAK

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.

The BREAK option is intended for forward-only and read-only processing. Updating, deleting, and inserting records or repositioning using the FIND statement can cause unpredictable results.

BY expression[ DESCENDING ]

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).

Note: You cannot reference a BLOB or CLOB field in the BY option.

You can use multiple BY options to do multi-level sorting. For example:

FOR EACH Customer BY Customer.CreditLimit BY Customer.Name

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.

Note that in a FOR FIRST/LAST statement, the AVM finds the first/last record before any sorting, therefore a BY option, if specified, would have no effect on the record returned.

COLLATE ( string , strength[ , collation] ) [ DESCENDING ]

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).

string
A CHARACTER expression that evaluates to the string whose collation value you want to generate.
strength

A CHARACTER expression that evaluates to an ABL comparison strength or an International Components for Unicode (ICU) comparison strength.

The ABL comparison strengths include:

  • RAW — Generates a collation value for the string based on its binary value.
  • CASE-SENSITIVE — Generates a case-sensitive collation value for the string based on a particular collation. If you specify this strength with an ICU collation, the AVM applies the ICU TERTIARY strength.
  • CASE-INSENSITIVE — Generates a case-insensitive collation value for the string based on a particular collation. If you specify this strength with an ICU collation, the AVM applies the ICU SECONDARY strength.
  • CAPS — Generates a collation value for the string based on its binary value after converting any lowercase letters in the string to uppercase letters, based on the settings of the Internal Code Page (-cpinternal) and Case Table (-cpcase) startup parameters.

The ICU comparison strengths include:

  • PRIMARY — Generates a collation value for the base characters in the string.
  • SECONDARY — Generates a collation value for the base characters and any diacritical marks in the string.
  • TERTIARY — Generates a case-sensitive collation value for the base characters and any diacritical marks in the string.
  • QUATERNARY — Generates a case-sensitive collation value for the base characters and any diacritical marks in the string, and distinguishes words with and without punctuation. ICU uses this strength to distinguish between Hiragana and Katakana when applied with the ICU-JA (Japanese) collation. Otherwise, it is the same as TERTIARY.
Note: Use ICU comparison strengths only with ICU collations.
collation

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:

  • strength does not evaluate to a valid value.
  • collation does not evaluate to a collation table residing in the convmap.cp file.
  • collation evaluates to a collation table that is not defined for the code page corresponding to the -cpinternal startup parameter.
variable = expression1 TO expression2[ BY k]

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.

WHILE expression
Indicates the condition in which you want the FOR EACH block to continue processing the statements within it. Using the WHILE expression option causes the block to iterate as long as the condition specified by the expression is TRUE or the AVM reaches the end of the index it is scanning, whichever comes first. The expression is any combination of constants, operators, field names, and variable names that yield a logical value.
TRANSACTION
Identifies the FOR EACH block as a system transaction block. The AVM starts a system transaction for each iteration of a transaction block if there is not already an active system transaction. See Develop ABL Applications for more information on transactions.
stop-after-phrase

Specifies a time-out value (in seconds) for the block. This is the syntax for the STOP-AFTER phrase:

STOP-AFTER time-limit

For more information see STOP-AFTER phrase.

on-error-phrase

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.

on-endkey-phrase

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.

on-quit-phrase

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.

on-stop-phrase

Describes the processing that takes place when the STOP condition 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.

frame-phrase

Specifies the overall layout and processing properties of a frame. This is the syntax for the frame phrase:

WITH [ ACCUM [ max-length ]]
     [ at-phrase ]
     [ CANCEL-BUTTON button-name][ CENTERED ]
     [ color-specification]
     [ COLUMN expression][n COLUMNS ]
     [ CONTEXT-HELP ][ CONTEXT-HELP-FILE help-file-name]
     [ DEFAULT-BUTTON button-name]
     [ DROP-TARGET ]
     [[expression] DOWN ][ EXPORT ] 
     [ WIDGET-ID id-number][ FONT expression]
     [ FRAME frame] 
     [ INHERIT-BGCOLOR | NO-INHERIT-BGCOLOR ]
     [ INHERIT-FGCOLOR | NO-INHERIT-FGCOLOR ]
     [ KEEP-TAB-ORDER ][ NO-BOX ]
     [ NO-HIDE ][ NO-LABELS ][ USE-DICT-EXPS ]
     [ NO-VALIDATE ][ NO-AUTO-VALIDATE ]
     [ NO-HELP ][ NO-UNDERLINE ]
     [ OVERLAY ][ PAGE-BOTTOM | PAGE-TOP ][ RETAIN n]
     [ ROW expression][ SCREEN-IO | STREAM-IO ]
     [ SCROLL n][ SCROLLABLE ][ SIDE-LABELS ]
     [ size-phrase][ STREAM stream| STREAM-HANDLE handle][ THREE-D ]
     [ title-phrase][ TOP-ONLY ][ USE-TEXT ]
     [ V6FRAME [ USE-REVVIDEO | USE-UNDERLINE ]]
     [ VIEW-AS DIALOG-BOX ][ WIDTH n][ IN WINDOW window]

For more information on frame-phrase, see the Frame phrase.

Examples

The following example code displays the Customer with the lowest CreditLimit. Note that you cannot use a FOR FIRST statement to do this because the AVM finds the first record before any sorting, Therefore a BY option, if specified, would have no effect on the record returned. Instead you can use a FOR EACH statement and exit the loop after displaying the first record.

FOR EACH Customer NO-LOCK BY Customer.CreditLimit:
  DISPLAY Customer.
  LEAVE.
END.

The following code reads Customer records that have a CustNum less than 12, sorting the records in order by State before displaying them:

FOR EACH Customer NO-LOCK WHERE Customer.CustNum < 12 BY Customer.State:
  DISPLAY Customer.CustNum Customer.Name Customer.City Customer.State.
END.

The following example code 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.

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.

The following code uses the LAST option to display information on the last Order of each Customer: 

FOR EACH Customer NO-LOCK, LAST Order OF Customer:
  DISPLAY Customer.CustNum Customer.Name Order.OrderNum Order.OrderDate
    Order.Instructions.
  PAUSE 1 NO-MESSAGE.
  Order.Instructions = "Last order".
  DISPLAY Order.Instruction.
END.

Notes

  • At compile time, ABL determines which index or indexes to use for retrieving records from a table, based on the conditions in the record-phrase. For compatibility with Progress Version 6 or earlier, you can force ABL to use only one index by specifying the USE-INDEX option or by using the Version 6 Query (-v6q) parameter.

  • 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: 

    field = expression

    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:

    field [ < | <= | > | >= | BEGINS ]expression

    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:

    BY field[ DESCENDING ]
  • If you specify the -v6q startup parameter, the following list describes the rules the OpenEdge database manager uses to choose an index for an OpenEdge database:
    • If you specify the record by ROWID, the AVM accesses the record directly without using an index.
    • If you use the USE-INDEX option, in the record-phrase, the AVM uses the index you name in that option.

    • 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:

      1. If one index is unique and all of its components are involved in active equality matches and the other index is not unique, or if not all of its components are involved in active equality matches, ABL chooses the former of the two.
      2. Select the index with more active equality matches.
      3. Select the index with more active range matches.
      4. Select the index with more active sort matches.
      5. Select the index that is the primary index.
      6. Select the first index alphabetically by index name.
  • If you specify the -v6q startup parameter, the AVM might have to scan all the records in the index to find those meeting the conditions, or it might have to examine only a subset of the records. This latter case is called bracketing the index and results in more efficient access. Having selected an index as previously described, the ABL compiler examines each component as follows to see if the index can be bracketed:
    • If the component has an active equality match, ABL can bracket it, and it examines the next component for possible bracketing.
    • If the component has an active range match, ABL can bracket it, but it does not examine the remaining components for possible bracketing.
    • If the component does not have an active equality match or an active range match, ABL does not examine the remaining components for bracketing.
  • If you specify the v6q parameter, any conditions you specify in the record-phrase that are not involved in bracketing the selected index are applied to the fields in the record itself to determine if the record meets the overall record-phrase criteria. For example, assume that the f table has fields a, b, and c involved in two indexes:
    • Primary, unique index (I1) on a, b, and c
    • Secondary non-unique index (I2) on c

    The following table shows the index ABL selects and the bracketed part of the index for various record-phrases.

    Table 1. Progress Version 6 index selection examples
    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
  • The FIRST and LAST keywords are especially useful when you are sorting records in a table in which you want to display information. Often, several related records exist in a related table, but you only want to display the first or last related record from that table in the sort. You can use FIRST or LAST in these cases.

    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 example code 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.
  • If you want the AVM to use a specific index, you must specify the first component of that index in the record phrase of the FOR statement.
  • You cannot reference a BLOB or CLOB field in a WHERE clause.
  • For SpeedScript, the on-endkey-phrase and the on-quit-phrase do not apply.
  • If server-side join is enabled and the join query contains a variable in the WHERE clause, that variable should not be updated inside the FOR block, as changes to the variable's value are not communicated to the server after the join is initially sent by the client. If you do update a variable, then the results returned may not be as expected.

    The following example show such a case:

    FOR EACH Customer NO-LOCK, 
  EACH Order OF Customer NO-LOCK
  WHERE order-date > myDateVar:
  ...
  myDateVar = new-value.
END.
    To avoid this behavior, separate the inner join into its own inner FOR block to have the client perform the join. For example:
    FOR EACH Customer NO-LOCK:
  FOR EACH Order OF Customer NO-LOCK
  ...
  • When a join query executes, there is no guarantee that any child table buffers of a join are released unless the record of its immediate parent table is fetched. With server-side join enabled, because the server only sends records that satisfies all levels of the join, you may see that a record that used to be released on a child table is no longer released. Therefore, if no records satisfy the query, then the child buffers are left in scope. For example: 
    FIND FIRST Order.

FOR EACH Customer WHERE Cust-num < 10 NO-LOCK, EACH Order of Customer NO-LOCK.
    If there are no Customer records that satisfy the WHERE clause, then the Order record remains in scope, whether server-side join is involved or not. If there are Customer and Order records that satisfy the query, then the Order record in scope is released. When server-side join is not enabled, the ABL client drives the resolution of the join so the client needs to retrieve a record from the parent level before trying to find records in its child table, in which case it does release the Order record. For this reason, it is not recommended that you depend on the availability of a record of a child table in a join after the join is executed.

See also

CATCH statement, FINALLY statement, FIND statement, Frame phrase, ON ENDKEY phrase, ON ERROR phrase, ON QUIT phrase, ON STOP phrase, Record phrase, STOP-AFTER phrase

1 In this case, ABL must look at all of the records to determine which meet the specified criteria.
2 The two record phrases in these examples are almost identical in effect, but the one using the OR operator to connect conditions is much less efficient in its use of the selected index.