3 Creating Reports

Finding Entries

When telling Query how to find data items and data entries, you must first specify a location (called a workfile) where Query can store the information it finds; this is done with the WORKFILE command.

If the data you want to report is contained in one data set, you can then use Query's FIND command to find the data and copy it into the workfile. If the data is stored in more than one data set, you must first use the THREAD command to tell Query how the data sets are connected and in which order they are to be searched; then use the FIND command to find the data items and copy them into the workfile.

Specifying a Workfile

When finding data items and data entries for you, Query needs a place to temporarily store the information it finds; this location is called a workfile. Query automatically creates a workfile when told to find the first data item and then deletes this workfile when you exit the Query program.

You can tell Query to use a specific workfile by executing the following command:

WORKFILE ["file name [ volume spec ]"]

Notice that the file name is optional. If you do not supply a file name, Query creates its own workfile and then removes that file when you exit the Query program.

For example, to tell Query to use the file TEMP, located on the volume labeled FILES, as the workfile you would enter the following:

   WORKFILE "TEMP, FILES" RETURN

Finding Entries in One Data Set

Once you have specified the workfile (or decided to let Query create its own), you are ready to find entries and copy them into the workfile. To find entries, you must know the names of the data sets and data items. If you do not know them, use the INFO command as described in page 31 .

To find entries, execute the FIND command:

FIND item list FOR search expression

The parameter item list specifies which data items are to be found; the parameter search expression specifies what conditions the data items must satisfy. A maximum of 60 data items may be specified in the item list and search expression combined.

For example, using the example database, assume you wanted to find the name of all customers who ordered product number 92640. You would enter the following command:

   FIND NAME FOR CUSTOMER.PRODUCT_NO = "92640" RETURN
Query would find all the values of the NAME data item that have a corresponding value of 92640 for the PRODUCT_NO data item in the CUSTOMER data set. Then Query copies all these values into the workfile.

The syntax of the item list and search expression parameters and a description of their use is provided in the following paragraphs.

Item List

The item list parameter is a list of data items and data sets that you want Query to find. Query finds each data entry that satisfies the search expression. It then copies the values of all data items specified in the item list for that entry into the workfile. If only a data set is specified in the item list, the entire data entry is copied in the workfile.

There are two ways to specify a data item. If a data item's name is in only one data set, you need enter only the data item name. If the data item name is in two or more data sets, precede the item name with the set name and a period: set name.item name

For example, suppose that you wanted to know the customer order number for every customer that ordered product number 92640. In other words, you want to copy into the workfile the ORDER_NO and PRODUCT_NO data items from the CUSTOMER data set each time that the PRODUCT_NO data item in the CUSTOMER data set equals "92640". To do this you would type the following:

   FIND CUSTOMER.ORDER_NO FOR CUSTOMER.PRODUCT_NO = "92640" RETURN
Alternately, if you want to copy the entire data entry into the workfile each time the CUSTOMER data set's PRODUCT_NO data item equals "92640", you would type the following:

   FIND CUSTOMER FOR CUSTOMER.PRODUCT_NO = "92640" RETURN
As a final example, if you want to copy the values for the data items ORDER_NO, NAME, and PRODUCT_NO into the workfile each time that the CUSTOMER data set's PRODUCT_NO data item equals "92640", you would type the following:

   FIND CUSTOMER.ORDER_NO,NAME FOR 
         CUSTOMER.PRODUCT_NO = "92640" RETURN
If you fail to specify the data set and the requested data item exists in more than one data set, Query asks you from which data set you want the data item value. For example, using the example database, suppose that you entered the following:

   FIND ORDER_NO FOR PRODUCT_NO = "92640" RETURN
Query would then display the following message:

   ORDER_NO is a member of these sets:
          1) ORDER
          2) OPTION
          3) CUSTOMER

   Enter the number of the set you wish to use:
You would then type the number identifying the data set to be used and press RETURN.

If a data set and a data item have the same name and you want Query to copy the entire data set into the workfile, add a period to the data set name:

FIND set name. FOR search expression

Otherwise, in this situation Query interprets a name without a period as a data item name.

Search Expression

The search expression is a mathematical expression that is evaluated true or false. Data item names are used as variables. These data items cannot be arrays (such as the ADDRESS data item in the CUSTOMER data set).

Some simple examples of search expressions are:

   SALESPERSON="SAM"

   NAME > "A" AND NAME < "B"
   NAME > "A" AND CUSTOMER.PART_NO < "999"
Notice that the values (such as SAM, A, B, and 999) to which data items are compared must be enclosed in quotes.

Query looks at a data entry and inserts the value of the data items specified into the search expression. If the expression evaluates true, Query copies all the data items specified in the item list and in the search expression from that data entry into the workfile. If the expression evaluates false, Query looks at the next entry. All entries are tested. A maximum of 1024 data items may be contained in the item list and search expression combined.

The search expression can be any expression using the following operators:


+   -   /   *   AND   OR   POS   >   <   =   #   


These operators are explained in Appendix C page 97 .

If you want to find all the entries for the item list, use the word ALL as the search expression. For example, to copy the name of every customer into the workfile you would type the following:

   FIND NAME FOR ALL

Using the FIND Softkey

If you press the FIND softkey to execute the FIND command, the following is displayed:

Enter list item values in the columns labeled "set name" and "item name"; enter the search expression in the line labeled "Enter the criteria you wish to find the items for:".

For example, your display would resemble the following if you used the softkeys to enter this sample FIND command: FIND OPTION.OPTION_DESC FOR OPTION_PRICE>"100"

Once the item list and search expression fields are filled in, the command is shown above the softkey definitions. If it is not correct, use the editing key to move the cursor to the incorrect item in the inverse video boxes and retype the item. When the command is displayed correctly, press the EXECUTE softkey.

Finding Entries in Multiple Data Sets

In the previous paragraphs all data items specified were in the same data set. Before you can use multiple data sets, you must tell Query how and in what order the data sets are connected.

Threading Data Sets

Use the THREAD command to specify paths between data sets.

The data sets must be connected by a key item. For example, the PRODUCT data set is connected to the CUSTOMER data set with the key item PRODUCT_NO.

   THREAD PRODUCT, CUSTOMER
The data set CUSTOMER is also connected to the ORDER data set by the key item ORDER_NO.

   THREAD PRODUCT, CUSTOMER; CUSTOMER, ORDER
If you do not know how the data sets are connected, use the INFO command. The data paths are listed.

Note that you can only thread data sets from master to detail and detail to master.

You can extend the thread through 16 data sets as long as there are no breaks. For example, assume you had sets A, B, C, D, and E, with A connected to B, B connected to C, and D connected to E. You can thread A, B, and C together, but D and E cannot be on the thread. If you give a second THREAD command it cancels the first.

The syntax is as follows:

THREAD set1, set2 [ ;set2, set3 [;set3, set4 ...] ]

If one master data set has paths to more than one item in a detail data set, the data item to be threaded must be specified.

THREAD set1.item1, set2.item2; ...

If you do not give the item name, Query issues an error message.

The THREAD command stays in effect until the next THREAD command is given or until a single set FIND or ADD command is executed.

The FIND Command with Threaded Data Sets

After the THREAD command is given, you can find entries in more than one data set. For example, assume you want to know what options were ordered with a specific product. You would type the following:

   THREAD CUSTOMER, ORDER; ORDER, OPTION RETURN

   FIND OPTION FOR CUSTOMER.PRODUCT_NO="100" RETURN
Query looks at the first data entry in the data set CUSTOMER and tests the search expression. If the expression is true, Query chains to the OPTION data set through the ORDER data set and copies all the values into the workfile. If the expression is false, or after the chain operation, Query tests the next entry, in CUSTOMER. If the chain length is zero (there are no entries in the OPTION data set), then no values are copied into the workfile even if the search expression (CUSTOMER<PRODUCT_NO = "100") is satisfied.

In any multiple data set find, Query searches the first data set sequentially and all other data sets on the thread via the data paths and data chains.


Eloquence Query Manual - 19 DEC 2002