Select the Order table from the list of tables, then click the Indexes button:

3. Click the Index Properties button. The Index Properties dialog box appears and shows the properties of the first index, CustOrder:

This is the index Progress uses to retrieve the Orders, because its first component is the CustNum field, and that is the field it has to match against the CustNum from the Customer table. Since the other component in the index is the OrderNum field, this index sorts records by OrderNum within CustNum so your request for the FIRST Order returns the record with the lowest Order number.

4. Exit the Data Dictionary before you continue. Otherwise, Progress won’t let you run any procedures because it has a database transaction open and ready to save any changes you might make in the Data Dictionary.

Figure 6–10 shows the beginning of the display from the block FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer.

Figure 6–10: Lowest Order number for each Customer

As expected, you see the Order with the lowest Order number for each Customer. If what you want is the earliest Order date, this output might not give you the information you are looking for.

Adding a BY phrase to the statement doesn’t help because Progress retrieves the records before applying the sort. So if you want the Order with the earliest Order date, it won’t work to do this:

FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer BY OrderDate:

DISPLAY Customer.CustNum NAME OrderNum OrderDate.

END.

Language statements that define blocks

This code retrieves the same Orders as before, but then sorts the whole result set by the OrderDate field, as shown in Figure 6–11.

Figure 6–11: Orders sorted by OrderDate

Using the USE-INDEX phrase to force a retrieval order

If you look at all the indexes for the Order table in the Data Dictionary, you can see that there is also an index called OrderDate that uses the Order field. You can select the index to use when the default choice is not the one you want. Progress does this by adding a USE-INDEX

phrase to the record phrase. This form of the FOR EACH statement is guaranteed to return the earliest OrderDate, even if it’s not the lowest OrderNum:

The result in Figure 6–12 shows that there is indeed an earlier Order for the first of your Customers that doesn’t have the lowest OrderNum.

Figure 6–12: Earliest Customer Order

FOR EACH Customer WHERE State = "NH",

FIRST Order OF Customer USE-INDEX OrderDate:

DISPLAY Customer.CustNum NAME OrderNum OrderDate.

END.

Using the LEAVE statement to leave a block

Use the USE-INDEX phrase only when necessary. Progress is extremely effective at choosing the right index, or combination of multiple indexes, to optimize your data retrieval. In fact, there’s an alternative even in the present example that yields the same result without requiring you to know the names and fields in the Order table’s indexes. Take a look at this procedure:

This code uses nested blocks to retrieve the Customers and Orders separately. These nested blocks allow you to sort the Orders for a single Customer BY OrderDate. You have to define the set of all the Customer’s Orders using the FOR EACH phrase so that the BY phrase has the effect of sorting them by OrderDate. But you really only want to see the first one. To do this, you use another one-word 4GL statement: LEAVE. The LEAVE statement does exactly what you would expect it to: It leaves the block (specifically the innermost iterating block to the LEAVE

statement) after displaying fields from the first of the Customer’s Orders. It does not execute any more statements that might be in the block nor does it loop through any more records that are in its result set. Instead, it moves back to the outer block to retrieve the next Customer.

Because the LEAVE statement looks for an iterating block to leave, it always leaves a FOR block.

It leaves a DO block only if the DO statement has a qualifier, such as WHILE, that causes it to iterate. If there is no iterating block, Progress leaves the entire procedure.

FOR EACH Customer WHERE State = "NH" WITH FRAME f:

DISPLAY Customer.CustNum Name.

FOR EACH Order OF Customer BY OrderDate:

DISPLAY OrderNum OrderDate WITH FRAME f.

LEAVE.

END. /* END FOR EACH Order */

END. /* END FOR EACH Customer */

Language statements that define blocks

Using block headers to identify blocks

If it isn’t clear what block the LEAVE statement applies to, or if you want it to apply to some other enclosing block, you can give a block a name followed by a colon and then specifically leave that block. This variant of the procedure has the same effect as the first one:

Just to see the effect of specifying a different block, you can try this variant:

If you run this code, Progress leaves the outer FOR EACHCustomer block after retrieving the first Order for the first Customer because of the change to the LEAVE statement, as shown in Figure 6–13.

Figure 6–13: Specifying a different block

FOR EACH Customer WHERE State = "NH" WITH FRAME f:

DISPLAY Customer.CustNum NAME.

OrderBlock:

FOR EACH Order OF Customer BY OrderDate:

DISPLAY OrderNum OrderDate WITH FRAME f.

LEAVE OrderBlock.

END. /* END FOR EACH Order */

END. /* END FOR EACH Customer */

CustBlock:

FOR EACH Customer WHERE State = "NH" WITH FRAME f:

DISPLAY Customer.CustNum NAME.

OrderBlock:

FOR EACH Order OF Customer BY OrderDate:

DISPLAY OrderNum OrderDate WITH FRAME f.

LEAVE CustBlock.

END. /* END FOR EACH Order */

END. /* END FOR EACH Customer */

Using NEXT, STOP, and QUIT to change block behavior

There’s another one-word statement that works much like LEAVE and that is NEXT. As you might expect, this statement skips any remaining statements in the block and proceeds to the next iteration of the block. You can qualify it with a block name the same way you do with LEAVE. There are two more such statements that have increasingly more drastic consequences: STOP and

QUIT.

STOP terminates the current procedure, backs out any active transactions, and returns to the Progress session’s startup procedure or to the Editor. You can intercept a STOP action by including the ON STOP phrase on a block header, which defines an action to take other than the default when the STOP condition occurs.

QUIT exits from Progress altogether in a run-time environment and returns to the operating system. If you’re running in a development environment, it has a similar effect to STOP and returns to the Editor or to the Desktop. There is also an ON QUIT phrase to intercept the QUIT

condition in a block header and define an action to take other than quitting the session.

Qualifying a FOR statement with a frame reference This most recent example also has an explicit frame reference in it:

Why is this necessary? A FOR EACH block scopes a frame to the block. By default, this is an unnamed frame. Without the specific frame reference, you get two nested frames, one for the Customer and one for its Orders. You saw this already in the sample procedure in Chapter 2,

“Using Basic 4GL Constructs.”

In this case, that isn’t what you want. Because there’s only one Order of interest for each

FOR EACH Customer WHERE State = "NH" WITH FRAME f:

DISPLAY Customer.CustNum Name.

FOR EACH Order OF Customer BY OrderDate:

DISPLAY OrderNum OrderDate WITH FRAME f.

LEAVE.

END. /* END FOR EACH Order */

END. /* END FOR EACH Customer */

Language statements that define blocks