HP 3000 Manuals

HP ALLBASE/BRW Tutorial

Exercise 2:Defining the Data Access 

This exercise describes how to use more than two source tables in a
report and how to use more than one relation in a table.  This exercise
also introduces keyed access and access sequence and shows you how to use
the Tune Access screen to specify keyed access and access sequence.  This 
exercise takes 60 minutes.

You defined the data access in the previous report.  In this report you
must use keyed access to the HP TurboIMAGE/iX data sets to enhance the
report performance.

Using More Than Two Source Tables 

You built a series of two tables in the previous report.  You require
three tables in this lesson:

ORDER-TABLE         which joins ORDERS and ORDER-DETAILS on ORDER-NO

ORDER-PRODUCTS      which joins PRODUCTS with ORDER-TABLE on PRODUCT-NO

CUSTREP3-DATA       the final access table, which joins ORDER-PRODUCTS
                    and CUSTOMERS on CUSTOMER-NO

There is an open join on ORDER-PRODUCTS, so that customers without orders
will be included in the final access table.


NOTE  You can define the operations performed in the first two tables
      (ORDER-PRODUCTS and ORDER-TABLE) in one table, without loss of
      performance.


Add the first table 

   1.  Choose  Define Table to go to the Define Table screen.


            

   2.  Type CUSTREP3-DATA in the Table field, overwriting CUSTREP2-DATA.

   3.  Press Enter.

   4.  When BRW prompts "Press ENTER to confirm renaming the table",
       press Enter again to rename the table CUSTREP3-DATA.

   5.  Choose  Next Table until the table ORDER-TABLE is displayed, as
       shown in the next table.


            

   6.  Choose  Delete Table.

   7.  When BRW prompts "Confirm deletion of table ORDER-TABLE" press
       Enter or the  Delete Table again to delete it.

       BRW deletes the table and displays the previous table,
       ORDER-PRODUCTS.

Add the next table 

   1.  Use the Tab key to move the cursor to the Source Table field below
       PRODUCTS.

   2.  Type ORDER-DETAILS in the Source Table field.

   3.  Type TOYDB.PUB in the Location field.

   4.  Type ORDERS in the next Source Table field.

   5.  Type TOYDB.PUB in the adjacent Location field.

   6.  Press Enter.

BRW modifies the table.

Your screen should look like this:


            

Define the relation between the tables 

   1.  Choose  Define Relation.  The relation from the old table is shown
       in the next table.


            

       Note that the source table ORDER-TABLE is highlighted, and that
       BRW prompts "Source table doesn't exist".  This is because you
       have deleted it.  You need to replace it with the correct source
       table for PRODUCT-NO.

   2.  Type ORDER-DETAILS (overwriting ORDER-TABLE).

   3.  Press Enter.

BRW modifies the relation.

The screen now looks like this:


            

Using More Than Two Relations 

Now you must add a new relation between the data sets ORDERS and
ORDER-DETAILS.

Add the relation between ORDERS and ORDER-DETAILS 

   1.  Type ORDER-NO in the Common Item field, (overwriting PRODUCT-NO).

   2.  Press Clear Display or space over all the remaining text for the
       common items and source tables.

   3.  Choose  Add Relation.  (DO NOT press Enter because that would
       overwrite the previous relation.)

       BRW adds the two source tables and the relation automatically.
       The relation is now Relation 2 of 2, as shown in the next table.


            

       _________________________________________________________________ 

       NOTE  If the item ORDER-NO had been in all three source tables,
             BRW would have added a further Common Item, ORDER-NO of
             PRODUCTS, to this relation.

       _________________________________________________________________ 

   4.  Choose  Relation Condit'n.

       BRW displays the Define Relation Condition screen.

   5.  Type the following text:

            MONTH_OF (ORDER-DATE) = 6
            << Selects only those orders placed in June. >>

   6.  Press Enter.

       BRW adds the relation formula.


            

   7.  Choose  Exit to return to the Define Table screen for table
       ORDER-PRODUCTS.

You have incorporated the old table ORDER-TABLE in ORDER-PRODUCTS.


NOTE  There are no performance gains or losses in this operation.  The
      object was to demonstrate that more than two source tables can be
      used in a table definition, with more than one relation in each
      table.  BRW will work out the most efficient way of relating the
      tables when the report is run.

MPE/iX 5.0 Documentation