Progress
Language Reference


FOR Statement

Interfaces
OS
SpeedScript
All
All
Yes

Starts an iterating block that reads a record from each of one or more tables at the start of each block iteration.

DATA MOVEMENT

BLOCK PROPERTIES

Iteration, record reading, record scoping, frame scoping, transactions by default.

SYNTAX

[ 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 ]
[ on-error-phrase ]
[ on-endkey-phrase ]
[ on-quit-phrase ]
[ on-stop-phrase ]
[ frame-phrase ] 

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. Progress finds the first record before any sorting.

FOR FIRST customer BY credit-limit:
  DISPLAY customer.
END. 

The procedure above displays customer 1 (cust-num is the primary index of the customer table), not the customer with the lowest credit-limit. A procedure that displays the customer with the lowest credit-limit looks like the following.

FOR EACH customer BY credit-limit:
  DISPLAY customer.
  LEAVE.
END. 

See the NOTES section for more information on using this option.

LAST

Uses the criteria in the record-phrase to find the last record in the table that meets that criteria. Progress finds the last record before sorting.

FOR LAST customer BY credit-limit:
  DISPLAY customer.
END. 

The procedure above displays the customer with the highest customer number (cust-num is the primary index of the customer table), not the customer with the highest credit-limit.

A procedure that displays the customer with the highest credit-limit looks like the following.

FOR EACH customer BY credit-limit DESCENDING:
  DISPLAY customer.
  LEAVE.
END. 

See the NOTES section for more information on using this option.

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.

SYNTAX
record
  [ constant ] [ OF table ]
  [ USE-INDEX index ]
  [ 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.

query-tuning-phrase

Allows programmatic control over the execution of a DataServer query.

SYNTAX
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, see the Progress DataServer Guides, Progress DataServer for ODBC Guide and Progress DataServer for ORACLE Guide.

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 EACH customer BREAK BY state:
  DISPLAY state name credit-limit (TOTAL BY state).
END. 

Here, Progress accumulates the total credit-limit for all the customers in the customer table. Each time the value of the state field changes, Progress displays a subtotal of the credit-limit 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.

BY expression [ DESCENDING ]

Sorts the selected records by the value of expression. If you do not use the BY option, Progress 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 default ascending order).

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

FOR EACH customer BY credit-limit BY name 

Here, the customers are sorted in order by credit-limit. Within each credit-limit 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.

string

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

strength

A CHARACTER expression that evaluates to one of the following:

collation

A CHARACTER expression that evaluates to the name of a collation table.

If collation does not appear, COLLATE uses the collation table of the client.

Progress reports an error and stops execution if one of the following occurs:

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. Progress 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 Progress 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. Progress starts a system transaction for each iteration of a transaction block if there is not already an active system transaction. See the Progress Programming Handbook for more information on transactions.

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.

SYNTAX
ON ERROR UNDO  [ label1 ]
  [   , LEAVE  [ label2 ]
    | , NEXT   [ label2 ]
    | , RETRY  [ label1 ]
    | , RETURN [ ERROR | NO-APPLY ] [ return-string ]
  ] 

For more information, see the ON ERROR Phrase reference entry.

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.

SYNTAX
ON ENDKEY UNDO [ label1 ]
  [   , LEAVE  [ label2 ]
    | , NEXT   [ label2 ]
    | , RETRY  [ label1 ]
    | , RETURN [ ERROR | NO-APPLY ] [ return-string ]
  ] 

For more information, see the ON ENDKEY Phrase reference entry.

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.

SYNTAX
ON QUIT [ UNDO [ label1 ] ]
  [   , LEAVE  [ label2 ]
    | , NEXT   [ label2 ]
    | , RETRY  [ label1 ]
    | , RETURN [ ERROR | NO-APPLY ] [ return-string ]
  ] 

For more information, see the ON QUIT Phrase reference entry.

on-stop-phrase

Describes the processing that takes place when the STOP conditions occurs during a block. This is the syntax for the ON STOP Phrase.

SYNTAX
ON STOP UNDO [ label1 ]
  [   , LEAVE  [ label2 ]
    | , NEXT   [ label2 ]
    | , RETRY  [ label1 ]
    | , RETURN [ ERROR | NO-APPLY ] [ return-string ]
  ] 

For more information, see the ON STOP Phrase reference entry.

frame-phrase

Specifies the overall layout and processing properties of a frame. For more information on frame-phrase, see the Frame Phrase reference entry.

EXAMPLES

This procedure reads customer records that have a cust-num less than 12, sorting the records in order by state before displaying them.

r-fore.p
FOR EACH customer WHERE cust-num < 12 BY state:
     DISPLAY cust-num name city state.
END. 

The next procedure gets information from four related tables (customer, order, order-line, and item) and displays some information from each. Before displaying the information, the FOR EACH statement sorts it in order by the promise-date field, then, within that field, in order by cust-num. Within the cust-num field, the data is sorted by the line-num field.

r-fore2.p
FOR EACH customer, EACH order OF customer,
    EACH order-line OF order, item OF order-line
    BY promise-date BY customer.cust-num BY line-num:
         DISPLAY promise-date customer.cust-num
                 order.order-num line-num item.item-num item-name.
END. 

This procedure uses the LAST option to display information on the last order of each customer.

r-fore3.p
FOR EACH customer, LAST order OF customer:
     DISPLAY customer.cust-num customer.name order.order-num
             order.order-date order.instructions.
     PAUSE 1 NO-MESSAGE.
     instructions = "Last order".
     DISPLAY instruction.
END. 

NOTES

SEE ALSO

FIND Statement, Frame Phrase, ON ENDKEY Phrase, ON ERROR Phrase, ON QUIT Phrase, ON STOP Phrase, Record Phrase


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095