HP 3000 Manuals

Data from Different Sources [ HP ALLBASE/BRW Reference Manual ] MPE/iX 5.0 Documentation

HP ALLBASE/BRW Reference Manual

Data from Different Sources

With HP ALLBASE/BRW it is possible to show, on one report, data coming
from an IMAGE database, an HP ALLBASE/SQL database, MPE files, and KSAM
files.

To combine data from different sources

1. Define the table, relations, and relation conditions just
as you would any other table. Remember to specify the
owner_name.table_name for any HP ALLBASE/SQL tables.

2. Use the Tune Access screen to tune the access for the tables in
the most efficient way for the data sources you are using. See
"Tuning Data Access" in this chapter for more information about
data tuning.

Example: Combine data

The following example combines data from an IMAGE data set and two HP
ALLBASE/SQL tables. The sample databases used are shown in Chapter 1
.

The IMAGE database (TOYDB) has a master data set PRODUCTS, which contains
product information (PRODUCT-NO, PRODUCT-NAME, PRICE). The HP ALLBASE/SQL
DBEnvironment (EXDBE) has a table SUPPLYDB.PRODUCTPARTS, which lists the
parts used to manufacture the product (PRODUCTNUMBER, PARTNUMBER), and a
table, SUPPLYDB.PARTS, which contains information on the parts
(PARTNUMBER, PARTNAME, PARTPRICE).

The information on the report PARTPROD has to show the part information
(PARTNUMBER, PARTNAME, PARTPRICE) for parts that have a part price
greater than or equal to 1000.00. For the parts satisfying this
condition, the report also has to show the product information
(PRODUCT-NO, PRODUCT-NAME, PRICE) for all products that contain this part
as long as the product price is less than 5000.00.

* Create the table PART-PRODUCT. This will be the final access
table, which contains all the data items required for the report.
Note that for HP ALLBASE/SQL tables, owner_name.table_name is
specified in the Source Table field.

* Join the two HP ALLBASE/SQL tables PARTS and PRODUCTPARTS using
PARTNUMBER. This provides the product numbers for each part.

* Join the HP ALLBASE/SQL table PRODUCTPARTS and the IMAGE data set
PRODUCTS using the product number. This provides the product
information for each product the part is used in.

* Create the formula that selects only parts with a part price
greater than or equal to 1000.00 and products with a product price
less than 5000.00.

* The Tune Access screen shows the table types and the order in
which tables and data sets are accessed for this example. The
following table show the access sequence number for the tables
used in this example, with an explanation about what happens.

------------------------------------------------------------------------------------------
| |
| Access Table Explanation |
| Sequence No. |
| |
------------------------------------------------------------------------------------------
| |
| 1 PRODUCTPARTS and HP ALLBASE/BRW generates an HP ALLBASE/SQL query to |
| PARTS retrieve parts with a PARTPRICE greater than or |
| equal to 1000.00 where there is a corresponding |
| PRODUCTNUMBER (according to the relation defined |
| for these two SQL tables). |
| |
| For the HP ALLBASE/SQL tables, the default access |
| sequence number is the same if the tables belong to |
| the same DBEnvironment and are listed consecutively |
| on the Define Table screen. When the access |
| sequence number is the same, one HP ALLBASE/SQL |
| query is generated for both HP ALLBASE/SQL tables. |
| Separate queries are passed if the tables do not |
| belong to the same DBEnvironment. |
| |
------------------------------------------------------------------------------------------
| |
| 2 PRODUCTS HP ALLBASE/BRW retrieves the product information |
| from the IMAGE data set PRODUCTS where the |
| corresponding PRODUCT-NO matches the PARTNUMBER |
| taken from the two HP ALLBASE/SQL tables. |
| |
| Note that a keyed access is made to the data set |
| PRODUCTS on PRODUCT-NO. That is, for every |
| PRODUCT-NO retrieved by HP ALLBASE/SQL, HP |
| ALLBASE/BRW uses keyed access into PRODUCTS to find |
| the PRODUCT information. |
| |
------------------------------------------------------------------------------------------

HP ALLBASE/SQL can be given access sequence numbers that are lower than
IMAGE tables because HP ALLBASE/BRW supports keyed reads from HP
ALLBASE/SQL into IMAGE, but not keyed reads from IMAGE into HP
ALLBASE/SQL.

MPE/iX 5.0 Documentation