How to Organize the Commands in a Simple Script

See DQL 8 for information on the for, list records, and end commands.

Question

How should I order the commands in a script when I'm using sorting, grouping, and statistical operators?

Solution

1. Begin with a for command to specify the Primary table for the report.

2. Use the list records command to list the fields you want to include.

3. List the grouped field(s) in groups with group-totals first under the list records command.

4. List the ordered field(s) in order (or in reverse) after the grouped field(s).

5. List the field(s) on which you wish to generate statistics using the conditional statistical operators or the relational statistical operators.

Example 1

 for MEMBERS ;

  list records

  STATE in order ;

  LAST NAME in order ;

  FIRST NAME in order .

 end

This script tells DataEase to: (1) sort the STATE field in order first, (2) then, within each state group, sort the LAST NAME field in order, and finally, (3) for each duplicate LAST NAME, sort the FIRST NAME in order.

Sample Output 1

The output for the above example can be formatted to look like this:

State  Name

AL Gould, Matthew

AL Jones, Anita

AL Jones, Thomas

AR Notarnicola, Rosanne

Example 2

 for MEMBERS ;

 list records

  LAST NAME in order ;

  FIRST NAME in order ;

  STATE in order .

 end

In Example 2, since the LAST NAME field is listed first and the STATE field last, DataEase performs the sorts in a different sequence, creating a very different result.

SampleOutput 2

The output for Example 2 can be formatted to look like this:

Name  State

Adams, John   CT

Adams,Will  OK

Albert, Roland  OH

Anders, Jenna  MT

Example 3

 for MEMBERS ;

  list records

  STATE in groups ;

  LAST NAME in order ;

  FIRST NAME in order .

 end

Example 3 is identical to Example 1 except that the STATE field is now listed in groups rather than in order. The in groups command orders the values just like the in order command but each value is listed just once instead of repeatedly. Notice the difference in the appearance of the output shown on the next page.

SampleOutput 3

The output from Example 3 can be formatted to look like this:

State  Name 

AL

 Gould, Matthew

 Jones, Anita

 Jones, Thomas

AR

 Notarnicola, Rosanne

 Rubin, Benjamin

 Shoen, Cecilia

 Turner, Anne

 Turner, Patrick

Example 4

 for CLUBS ;

  list records

  COUNTRY in groups with group-totals ;

  CLUB NAME in order ;

  mean of RESERVATIONS TOTAL DUE ;

  ROOMS : item sum .

 end

This example tells DataEase to: (1) open the CLUBS table and process all the records, (2) group the records by country, (3) within each country group, sort the club names in ascending order (a-z), (4) calculate the average reservation total for each club, and (5) list the number of rooms at each club, the number of rooms in each country, and the total number of club rooms in Club ParaDEASE.

Discussion

DataEase processes the commands in a script in the order it encounters them. Therefore, as a general rule you must:

• Place any grouping commands before any ordering commands.

• Place any statistical operators after any grouping and/or ordering commands.

Tip

When you use multiple or nested for loops, you must terminate each for loop with an end command. Although DataEase doesn't require an end command in a simple script like the one shown on the previous page, we recommend that you get in the habit of using an end command to signal the end of each for loop.