HP 3000 Manuals HP 3000 Manuals
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:
Click here to view figure.
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.
Click here to view figure.
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.
Click here to view figure.
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:
Click here to view figure.
Use the Project Items screen to see if all the items you need are
projected.
Click here to view figure.
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:
Click here to view figure.
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.
Click here to view figure.
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.