You are already familiar with starting a block definition with the FOR keyword. You saw the common FOR EACH table-name form, but there are a number of variations on the FOR statement. In contrast to the DO block, every FOR block provides all of the following services for you automatically:

  • Loops automatically through all the records that satisfy the record set definition in the block
  • Reads the next record from the result set for you as it iterates
  • Scopes those records to the block
  • Scopes a frame to the block, and you can use the WITH FRAME phrase to specify that frame
  • Provides database update services within a transaction

The FOR statement defines the set of records you want the block to iterate through. Typically you use the EACH keyword to specify this set:

FOR EACH Customer NO-LOCK WHERE Customer.State = "NH":
  DISPLAY Customer.CustNum Customer.Name.
END.

When the block begins, the AVM evaluates the expression and retrieves the first record that satisfies it. This record is scoped to the entire block. Each time the block iterates, the AVM retrieves the next matching record and makes it available to the rest of the block. When the set of matching records is exhausted, the AVM automatically terminates the block. You do not have to add any checks or special syntax to exit the block at this point.

Sorting records by using the BY phrase

As you saw, you can sort the records by using the BY phrase. The default is ascending order, but you cannot use the keyword ASCENDING to indicate this. You get a syntax error, so just leave it out to get ascending order.

To sort in descending order, add the keyword DESCENDING to the BY phrase: 

BY field [ DESCENDING ] ...

To sort on multiple fields, you can repeat the BY phrase.