HP 3000 Manuals

HP ALLBASE/BRW Tutorial

Exercise 4:Relating Another Table 

Relate the ORDER-TABLE to the PRODUCTS data 
set. 

   1.  Overwrite the existing entries in the following fields with the
       new information:

       Field name            New Entry                                                                                               

       Table                 ORDER-PRODUCTS

       Source Tables         PRODUCTS

        '                    ORDER-TABLE

       Location              TOYDB.PUB (or TOYDB.PUB.ITF3000)

       Be sure to blank out any extra characters.

       You don't need a Location for ORDER-TABLE since it only exists as
       part of this report, not as a physical file.

       Your screen should look like this:


            

   2.  Choose  Add Table.  DON'T press Enter.

       If you pressed Enter at this point, you would be renaming the
       original table, not adding a table.

Define the relation 

   1.  Choose Define Relation.

   2.  Type PRODUCT-NO in the first Common Item field.

   3.  Choose  Add Relation.

       Your screen should look like the next screen.


            

Each record written to the new table, ORDER-PRODUCTS, will contain all
the items in the table ORDER-TABLE plus all the items from the PRODUCTS
data set for each product number that occurs in the ORDER-TABLE.

In this case, you do not need a relation condition to restrict the
records because you want all the records in ORDER-TABLE (which is already
restricted).  The only records that will be selected from PRODUCTS are
those where the product number matches a product number in the
ORDER-TABLE, that is, a product ordered in June.

To make sure you have the items you require, choose  Project Items.


            

Items with Identical Names 

Look at the Project Items screen.  Notice that there are several items
with the same name, PRODUCT-NO and PRODUCT-NO, QUANTITY and QUANTITY,
TABLE-REC-NUMBER and TABLE-REC-NUMBER. To avoid confusion, if more than
one data item has the same name, BRW projects only the item from the
first source table mentioned on the Define Table screen.

In the case of PRODUCT-NO and PRODUCT-NO, it doesn't matter which one is
projected since both of them are combined as the common item for the
join.  The TABLE-REC-NUMBER for the ORDER-TABLE and for the PRODUCTS
table is the logical record number generated by BRW and it doesn't matter
which of these items is projected.  But in the case of QUANTITY and
QUANTITY, they are two different items entirely.  The QUANTITY in the
PRODUCTS table is the quantity in stock.  The QUANTITY in the
ORDER-DETAILS table is the quantity ordered.  For this report, you want
the quantity ordered.

Modify the Project Items screen 

   1.  Type X in the Proj field for the item QUANTITY from the
       ORDER-TABLE.

   2.  Type a space over the X in the Proj field beside QUANTITY from the
       PRODUCTS table.

   3.  Press Enter.

       A message tells you Item projection modified.

   4.  Choose  Exit to return to the Define Table screen.


NOTE  Alias Names 

      If you had wanted both items in the report, you could project them
      both by putting and X in the Proj field for both items and giving
      one of the items an alias name.  Thereafter, when you wanted to
      refer to that item, you would use the alias name instead of the
      actual name from the data source.


Final Table (CUSTREP2-DATA) 

The last table you add to the report contains the customer details for
each order.

Create the CUSTREP2-DATA table 

Try this one on your own.  You have successfully created and joined the
two other tables.  This table consists of the CUSTOMERS data set joined
to the ORDER-PRODUCTS table related by the CUSTOMER-NO item in each one.
Begin at the Define Tables screen and then use the Define Relation
screen.  Remember to use  Add Table and  Add Relation so that you don't
overwrite the previous table definition.

When you are finished, the Define Relation screen should look like this:


            

Use the  Project Items screen to see if all the items you need are
projected.


            

Using Quick Browse 

Now the Project Items screen has more items than will fit on one screen.
There are two ways to look at the rest of the items.

   a.  Choose  Next Items to see the next screen of items and then choose
        Prev Items to return to the previous screen.

       Or...

   b.  In the first line of the Item field, type the name of one of the
       items you want to see, overwriting the item name already in that
       field.  Be sure to space over extra characters from the previous
       item.  Then press  Next Item.

       The next screen of items begins with the item you just typed.

       For instance, if you typed CUSTOMER-NAME in the first Item field,
       this is the screen that appears:


            

You can see from this screen that you have all the items that you need
for the report:

   *   CUSTOMER-NAME

   *   CUSTOMER-NO

   *   ORDER-NO

   *   PRICE

   *   PRODUCT-NO

   *   QUANTITY (quantity ordered, not quantity in stock)

   *   SALES-AREA

   *   TURNOVER MTD

At this point, your report will contain all the customers that have
placed an order in June.  It will not have any customers who did not 
place an order in June.

Defining an open join 

For this report, you do want to see the names of all the customers, even
if they did not place an order in June.  To do that, you must define an
open join.

In an open join, all the items from the first source table listed are
written to the report, even if there is no qualifying value (such as an
order in the month of June) in the joined table.

Define an open join 

   1.  At the Define Table screen, type ORDER-PRODUCTS in the Open Join
       on Source Table field.

   2.  Press Enter.

       The message Table modified appears.


            

   3.  Choose  Exit to return to the Define Report screen.

       Notice that the Final Access Table field shows CUSTREP2-DATA as
       the Final Access Table for this report.

For this report, if a customer has made an order there will be an entry
in the ORDER-PRODUCTS table and the data from that entry will be included
in the report.  If there has been no order, a NO-VALUE entry is written
in the report.  The result looks like this:
____________________________________________________________________________
|                                                                          |
|     CUSTOMER-NO  CITY     STATE    ORDER-NO  ORDER-DATE  PRICE PRODUCT-NO|
|                                                                          |
|     00002        HOUSTON  TEXAS    ORD-002   0603        1745  A00005    |
|     00006        CHICAGO  ILLINOIS -------   ----        ----  ------    |
____________________________________________________________________________

            

In this illustration, NO-VALUE is represented by the character "-".  You
use the Item Edits screen to set the character to be printed for a
NO-VALUE item.

For a more detailed explanation of open joins, see the HP ALLBASE/BRW 
Reference Manual.

MPE/iX 5.0 Documentation