3 Creating Reports

Finding Entries

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

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

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

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

Item List

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

   FIND CUSTOMER FOR CUSTOMER.PRODUCT_NO = "92640" RETURN

   FIND CUSTOMER.ORDER_NO,NAME FOR 
         CUSTOMER.PRODUCT_NO = "92640" RETURN

   FIND ORDER_NO FOR PRODUCT_NO = "92640" RETURN

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

   Enter the number of the set you wish to use:

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

Some simple examples of search expressions are:

   SALESPERSON="SAM"

   NAME > "A" AND NAME < "B"
   NAME > "A" AND CUSTOMER.PART_NO < "999"

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   >   <   =   #   

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

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

Threading 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

   THREAD PRODUCT, CUSTOMER; CUSTOMER, ORDER

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

   THREAD CUSTOMER, ORDER; ORDER, OPTION RETURN

   FIND OPTION FOR CUSTOMER.PRODUCT_NO="100" RETURN

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.