HP 3000 Manuals

HP ALLBASE/BRW Tutorial

Exercise 2:Defining the Data Access 

In the first report, you used only one table.  In this report you will
use a series of tables that build to one final access table.  This 
exercise takes about 50 minutes. 

The following illustration shows how the tables are combined.

	       Click here to view figure.
            

For more information about how BRW builds tables, read the BRW Reference 
Manual.

Clear the old table 

   1.  At the Define Report screen, choose  Define Table.

       The Define Table screen appears.

	       Click here to view figure.
            

       Because this report was copied from a previous report, the table
       name CUSTREP1-DATA appears in the Table field.

   2.  Choose  other keys until the function label for  Delete Table 
       appears.

   3.  Choose  Delete Table.

   4.  At the prompt, Confirm deletion of table CUSTREP1-DATA, press
       Enter or choose  Delete Table again.

       The Define Table screen is cleared.

Create the ORDER-TABLE 

   1.  Type ORDER-TABLE in the Table field.

   2.  Accept the default of J for the Type field.

   3.  Type ORDERS in the first line of the Source Table field.

   4.  Type TOYDB.PUB (or TOYDB.PUB.ITF3000) in the first line of the
       Location field.

   5.  Type ORDER-DETAILS in the second line of the Source Table field.

   6.  Type TOYDB.PUB (or TOYDB.PUB.ITF3000) in the second line of the
       Location field.

   7.  Press Enter or choose  Add Table.

	       Click here to view figure.
            

The prompt tells you that the table was added.

Defining the relation 

The next task, defining a relation, is a new procedure, one that is not
necessary when only one table is used, as in your first report.  The
relation definition specifies a common item that must exist between the
joined tables.  For example, each of the two tables joined in this
exercise has an item called ORDER-NO that contains the order number for
the sale.  When the order numbers match, BRW knows that a record from
each data set can be combined into a single record.

You can relate up to nine source tables to create the result table.
However, for this report, we cannot join all the tables at once because
they do not all contain the same common item.


CAUTION  If you do not specify a relation, BRW writes all possible
         combinations of records to the result table.  For example, if
         you joined a table with 1000 records to a table with 500
         records, there would be 500,000 possible combinations!


Define the relation 

   1.  Choose  Define Relation at the Define Table screen.  The Define
       Relation screen appears.

	       Click here to view figure.
            

   2.  Type ORDER-NO in the Common Item field.

   3.  Press Enter or choose  Add Relation.

Notice that BRW automatically inserts the source table name and the
second common item.  This happens when there is an item of the same name
and type in both joined tables.  BRW assumes that the items are related.

The message Relation Added appears.

Each record in the result table (ORDER-TABLE) will contain a value for
every item in both ORDERS and ORDER-DETAILS source tables.


NOTE  You can relate any item from one source table to any item in the
      other source table as long as they are of the same type.  For
      example, you can relate any string to a string (the string size
      does not matter) or any numeric to a numeric, but you cannot relate
      a numeric item to a string item, a date item to an integer, or a
      fixed-point numeric to an integer.

      The relationship needs to be logical for the report you are
      creating.  BRW would allow you to, for example, relate CUSTOMER-NO
      to PRODUCT-NO, but the relationship would not produce any
      reasonable records.  You would only get a record written to the
      table if, by chance, a customer number happened to be the same as a
      product number.

MPE/iX 5.0 Documentation