for

Type

Processing Command

Purpose

The for command is used in a script to specify the table from which records are selected for processing and the criteria used to select those records.

Usage

The for command is the most frequently used word in the DataEase Query Language. Most Processing procedures begin with this command.

A script can have multiple for commands when records must be selected from different tables (see Examples 1 and 2). for commands can be nested to alter the sequence of program actions (see Example 3).

The for command is always terminated by an end command. Actions specified between a for command and its corresponding end command are executed once for each record selected by the for command.

Syntax

for TABLENAME|RELATIONSHIP

 [named "UNIQUE RELATIONSHIP NAME" ]

 [with ( selection criteria) ] ;

The for command usually requires a semicolon after the TABLENAME (and selection criteria, if any). However, when a for command is nested within another for command, only the outermost for command requires a semicolon (see Example 3).

Example 1

Example 1 shows how to use a for command to access a single table.

for MEMBERS with TOTAL DUE > 175 ;

list records

LAST NAME in order ;

TOTAL DUE .

end

This script tells DataEase: (1) Select the MEMBERS records that have a value greater than $175 in the TOTAL DUE field, and (2) for each selected record, list the member's LAST NAME and TOTAL DUE. The output from this script, arranged in alphabetical order by LAST NAME, might look like this:

Example 2

Example 2 shows how to use sequential for commands without nesting.

for MEMBERS with TOTAL DUE > 200 ;

list records

LASTNAME in order .

end

for RESERVATIONS ;

list records

LASTNAME in order ;

RESERVATION I D ;

TOTAL DUE .

end

This script tells DataEase: (1) Select the MEMBERS records that have a value greater than $200 in the TOTAL DUE field, (2) for each selected record, list the member's LAST NAME, (3) select all the RESERVATIONS records, and (4) for each RESERVATIONS record, list the LAST NAME, RESERVATION ID and TOTAL DUE.

When a script contains two or more for statements that list records, DataEase prints or displays all records requested by the first for statement first, then displays all records requested by the second for statement, and so on. The output for the script in Example 2 might look like this:

Last Name

Christino

Perrault

Stafford

Strachan

Example 3

Example 3 shows how to use nested for commands.

Although you can display data from multiple tables using sequenced for statements, as shown in Example 2, a script may run faster and generate more useful output when it contains nested for statements as shown in Example 3:

for MEMBERS with TOTAL DUE > 200 ;

for RESERVATIONS

list records

MEMBERS LAST NAME in groups ;

RESERVATIONID in order ;

TOTAL DUE .

end

end

In this script, the second for command, which is nested within the first, selects all RESERVATIONS records related to the selected MEMBERS records. For each of those RESERVATIONS records, DataEase performs all the actions between the second for and its corresponding end.

This query tells DataEase: (1) Select the MEMBERS records that have a value greater than $200 in the TOTAL DUE field, (2) find all the related RESERVATIONS records for each MEMBERS record selected in Step 1, (3) for each selected MEMBERS record, list the member's LAST NAME, and (4) for each selected RESERVATIONS record, list the RESERVATION ID and the TOTAL DUE.

Notice that in Example3, there is no semicolon after the second for because it is nested within another for command. In contrast, both for statements in Example 2 require a semicolon because neither statement is nested inside another for statement. The output from this script, arranged in alphabetical order by LAST NAME, might look as shown on the next page.

The in groups operator tells DataEase to processes all the records for each member together as a group and to list the members in alphabetical order. The in order operator tells DataEase to arrange the data for each member in ascending order by RESERVATION ID.

Note: The in groups operator always precedes the in order operator in a script.