Creating the Script

See DQL I for information on creating a new procedure document.

Before you can write a script, you must create a DQL Procedure document. Choose File>>New>>Procedure. When DataEase displays the New Document dialog, choose a Style Sheet if desired, then click OK to close the dialog and display the DQL Script Editor.

The for Command

The for command is frequently the first word in a query. for is used in conjunction with a table name to tell DataEase from which table to gather information.

The for command tells DataEase to select records in a particular table and perform a group of actions on each of those records. After all the actions are performed for the first record, the next record is read, and all the actions are performed again. DataEase repeats this "loop" until all selected records in the table have been processed.

The Primary table is the first table specified in a query. Usually, it is the table that holds the key data you want to view or manipulate. MEMBERS is the Primary table in this query because this table contains the members' names and annual fee data. You can also access data in a Secondary table (a table related to the Primary table) in a query, as you'll see later in this example.

Specifying Selection Criteria in a Query

DataEase needs to know if you want it to use all the records in the Primary table, or to select only some of them based on certain selection criteria. We only want to see some of the records-the records for members who have an annual membership fee greater than $90.00.

You tell DataEase that you want to include selection criteria by inserting the keyword with in the query. Press the Enter key to begin a new line, then enter with into the query either by double-clicking the command in the Commands pick list or by typing it manually. The query appears as shown below, indicating that you want to select only some records from the MEMBERS table.

 for MEMBERS

 with

Specifically, we want DataEase to process records for only those members whose annual fees (stored in the TOTAL DUE field) are greater than $90. The following DQL statement specifies this criterion:

 TOTALDUE > 90

To insert this selection criterion into the query, you can type it manually, or double-click TOTAL DUE in the Columns pick list, select the Comparison Operator, >, from the Operators picklist, and the value, 90, via the keypad

See DQL 8 for more information on using the and and or operators.

The query reads:

 for MEMBERS

 with TOTALDUE > 90

Although this query contains only one selection criterion, DataEase also lets you select records based on multiple selection criteria. For example, if you want to process only the records of members from certain states with a TOTAL DUE of more than $90, you can use the and and or operators to join more than one selection criterion, as shown below:

However, for this query we want to select records only on the basis of the value in the TOTAL DUE field. To tell DataEase that there are no additional selection criteria, end the statement with a semicolon. The semicolon is required to mark the end of the selection criteria used to select records from the primary table.

The query reads:

 for MEMBERS

 with TOTALDUE > 90 ;

Note: When you enter a currency value into a script, do not include a dollar sign or commas. It is not necessary to enter a decimal point unless you are specifying a decimal value.

Using the list records Command

See DQL 8 for more information on comparison operators.

Now that you've specified which records to process, DataEase needs to know what to do with those records. Because we want to list data from the selected records, enter the list records command into the query. Press Enter to begin a new line, then double-click list records in the Commands pick list. The query reads as follows:

 for MEMBERS

 with TOTALDUE > 90 ;

  list records

For each selected record, the list records command tells DataEase to list the items you're about to specify in the next part of the query. The most common type of list item is the name of a field (data column).

Note: Although DataEase does not require it, you may want to make your scripts easy to read by indenting the list records command and the list items that follow the command. It is not required to start each new statement on a separate line other than for the sake of readability.

Sorting and Grouping Data in a Query

Because we want to list members alphabetically by name, the first item we want in the procedure output is the data from the LASTNAME column. Press Enter to start a new line, then double-click LASTNAME in the Columns pick list. DataEase inserts the column name into the script, as shown:

 for MEMBERS

 with TOTALDUE > 90;

  list records

   LASTNAME

Sorting and Grouping Operators

If you run the procedure using the script as it appears above, DataEase lists the members' last names in the order in which they were entered into the database. DQL offers four operators that let you specify a more useful order for your list of records. Each option appears in the Operators pick list and is briefly explained below:

• in order sorts the records in ascending alphabetical order (putting Adams ahead of Beecher).

• in reverse sorts in descending alphabetical order (putting Zimmerman before Young).

• in groups sorts the records in ascending order into groups that have the same value in the specified column (e.g., all members who live in Alabama could be listed together as a group with their names arranged in alphabetical order, followed by all members from Alaska, and so on). You can generate statistical totals for each group, such as the total number of members living in each state.

• in groups with group-totals is included for compatibility with previous versions of DataEase. Both this option and "in groups" can be used to generate group statistics.

For our alphabetized report, select in order by double-clicking it in the Operators pick list or by typing it into the query after LAST NAME. End the line with a semicolon, as shown:

  LASTNAME in order ;

Note: The semicolon, which is used to end a statement, is a DQL syntax requirement. A semicolon must appear in every for statement (after the table name, or if selection criteria are used, after the last selection criterion) unless the for statement is nested inside another for statement. You must also insert a semicolon after each data item specified in the list records section of the query, except the last item.