HP 3000 Manuals

HP ALLBASE/BRW Reference Manual

Joins and Relations 

Joining Tables 

You join two or more source tables by:

   *   naming the table

   *   specifying the type of join

   *   listing the data sources

   *   defining a relation between the records in each table.  The
       relation is based on a common item.

You may specify an open join to further qualify the results you get from
the join.

A join is not complete until you have defined the relation between the
joined tables.

To join tables 

   1.  On the Define Table screen, type the name for a new table in the
       Table field.

   2.  Type J in the Type field (for join).

   3.  In the Source Table fields, type the names of the source tables
       from which the data for the new table is to be extracted.  Specify
       owner_name.table_name for each HP ALLBASE/SQL table.

   4.  In the Location fields, type the path to each source table.

   5.  Type any applicable passwords in the Password fields.

   6.  If this is an open join, type the name of the source table you
       want for the source that can contain NO_VALUE if no records match
       the relation.


            

   7.  Choose Add Table to add the joined table as an access table for
       this report.

Defining Relations 

To define a relation 

   1.  Go to the Define Relation screen by choosing Define Relation on
       the Define Tables screen.

       An example of the Define Relation screen is shown below.

   2.  Type the name of each item from the source tables that you want to
       define as a common item.


            

       If you have one or more items with the same name in different
       tables, just type one name in the Common Item column.

   3.  Press Enter.

The Define Relations screen is described in greater detail in Chapter 16
(*).

Common items have these characteristics: 

   *   They must be of the same type, (strings can only be related to
       strings, dates to dates, and so on).  Types are described in
       Chapter 15 .  String items do not have to be of the same
       length.

   *   They can have different names in the source tables.

   *   They need not be key items (that is, IMAGE search items or KSAM
       keys).


NOTE  HP ALLBASE/BRW does not require that the relation is logical.

      That is, you could relate, say, CUSTOMER-NO item of the INVOICES
      table with the SALES-AREA item of the CUSTOMERS table, since both
      items are the same data type (strings).  (If two strings of
      differing lengths are compared, the shorter is padded with blanks.)
      A record would then be written to the new table, CUST-INVOICES,
      when the value for CUSTOMER-NO was equal to the SALES-AREA. This
      example is, of course, not terribly sensible.  It is up to you to
      make the relation logical so that you get meaningful results in
      your report.



CAUTION  If you do not specify a relation, HP ALLBASE/BRW writes all
         possible combinations of records to the result table.  In other
         words, HP ALLBASE/BRW will relate all possible records in one
         table with all possible records in another, and you may get an
         enormous table full of unstructured data.


Example:  Join 

Suppose you want to create a report CUSTINV, using the data sources
CUSTOMERS and INVOICES.

You join the source tables CUSTOMERS and INVOICES to create the final
access table, CUST-INVOICES. If you define the relation using the common
item CUSTOMER-NO, HP ALLBASE/BRW reads each table and writes a record to
the result table each time the value of CUSTOMER-NO for a record in
CUSTOMERS is equal to the CUSTOMER-NO of a record in INVOICES.

If you did not specify a relation for table CUST-INVOICES, each record in
CUSTOMERS would be related to each record in INVOICES. If there were 5000
records in CUSTOMERS and 10000 recods in INVOICES, CUST-INVOICES would
contain 50,000,000 records!  Since you would only want to have this
happen in certain rare cases, make sure that you have defined all the
necessary relations before running a report.

Using CUSTOMER-NO as the common item, the record written to the result
table contains a value for every item in both source tables (provided
that the item names are unique).  Because the item CUSTOMER-NO exists in
both sources, the CUSTOMER-NO item from only one table is written to each
record in the result table.  (You can, however, supply an alias name for
CUSTOMER-NO in the other table so that both items are written to the
result table.  This is explained in "Projecting Items" ).

The join of CUSTOMERS and INVOICES on CUSTOMER-NO to produce the result
table CUST-INVOICES is illustrated in the following figure:


            

Each record of the table CUST-INVOICES contains invoice information and
all the customer information for the customer to whom the invoice
applies.

Notice these things about the CUST-INVOICES table:

   *   Only those records in the source tables CUSTOMERS and INVOICES
       that have a matching value of the common item CUSTOMER-NO are
       written to the result table, CUST-INVOICES.

   *   The customer number C7 has no counterpart in the INVOICES table
       and, therefore, does not appear in the result table.

For cases of multiple records for one CUSTOMER-NO, multiple records are
written to the result table.  For instance, if customer number C2 had two
invoices listed in the INVOICES table, a record for each invoice would
appear in the result table.

Open Joins 

Open joins let you join source tables and report all records from one
source table, even if these have no corresponding records in the other
source table.  Items in the open join source table are set to NO_VALUE
when there are no corresponding records.


NOTE  NO_VALUE is an exception condition and is described in Chapter 15
      .  NO_VALUE indicates that an item has no value.  For example, a
      numeric item will be set to NO_VALUE if it is projected from an
      open join source table and has no value in that table.  Note that
      NO_VALUE is not the same as a numeric item being equal to zero.


This Define Table screen shows how the open join for these tables is
defined:


            

Example:  Open Join 

Suppose you want to join CUSTOMERS and INVOICES to produce a table
CUST-INVOICES, as in the previous example.  But now you also want to
report on all customers, whether or not they have invoices.  The previous
example will only write records to CUST-INVOICES when a customer has an
invoice, that is, when CUSTOMER-NO in CUSTOMERS equals CUSTOMER-NO in
INVOICES. If a customer has no invoices the CUSTOMER-NO in CUSTOMERS
cannot match a CUSTOMER-NO in INVOICES and no record will be written.
But you can include all customers by specifying an open join on the
INVOICES source table.

In this case, when CUSTOMER-NO in CUSTOMERS matches CUSTOMER-NO in
INVOICES, a record is written as before.  But if a CUSTOMER-NO in
CUSTOMERS has no corresponding entry in INVOICES, a record will also be
written because of the open join on INVOICES.

You can specify only one source table on each Define Table screen as an
open join.  If you want open joins on more than one source table, see
"Multiple Open Joins"  later in this chapter.

The next illustration shows what happens when there is an open join on
INVOICES. All items coming from INVOICES will be set to NO_VALUE in the
resulting row when no invoice is present.


            

NO_VALUE is signified in this table by a long dash (-------).  Note that
now a record for customer GAMBLER (CUSTOMER-NO C7) is written to
CUST-INVOICES, although there is no record with CUSTOMER-NO C7 in
INVOICES.

Multiple Open Joins 

Since you can only specify one open join per table, nested tables must be
used to specify multiple open joins.

To nest tables 

   1.  Decide how you want to group your source tables as access tables.

   2.  Define an access table as described in "Joining Tables" earlier in
       this chapter.

   3.  Define the relation for the first table.

   4.  Return to the Define Table screen.

   5.  Type the definition information for the next table.  You can type
       over the previous definition and use the space bar to remove extra
       characters if you need to.

   6.  Choose Add Table.  Do not press Enter (instead of Add Table).!
       Pressing Enter will redefine the first table instead of adding a 
       new table. 

   7.  Define the relation for the new table.

You can repeat the above steps for as many tables as you want to nest.
The maximum number of source tables you can use in a single access table
is 14.

Example:  Nested Tables 

Suppose you want to report customers, invoices, and installments and
include customers with no invoices and invoices with no installments.
The join must be split into two tables because you need to have two open
joins.

One table, called PAYMENTS, joins the source tables INVOICES and
INSTALLMENTS with an open join on INSTALLMENTS, as shown here:


            

The Define Table screen for the first open join is shown below:


            
[REV BEG]

The other table, shown next, joins CUSTOMERS and PAYMENTS with an open
join on PAYMENTS (the access table created by the previous join).[REV
END]


            

The Define Table for this open join looks like this:


            

CUSTINV is the final access table.

Open Join on HP ALLBASE/SQL Tables 

With HP ALLBASE/BRW, a user can define an open join using HP ALLBASE/SQL
tables.

Example:  Open join on HP ALLBASE/SQL table 

The following example is for a report of all vendors and the parts they
sell, including those vendors that do not sell parts.  The two data
sources are VENDORS and SUPPLYRPICE in the SUPPLYDB DBEnvironment, as
shown on this Define Table screen.


            

The name of the table specified in the Open Join on Source Table field is
the table (in this case SUPPLYPRICE) for which NO_VALUE is used if there
is no corresponding record.  In other words, the new table will show a
record for a vendor that does not sell parts, since the vendor name is in
the PAYMENTS table, but NO_VALUE will appear in the record where the
parts sold would appear.

The relation using the common item VENDORNUMBER is shown on this Define
Relation screen:


            

In a join, you can use the same access sequence for an HP ALLBASE/SQL
query, but with an open join you cannot.  The access sequence is
specified on the Tune Access screen.  For this open join, the Tune Access
screen will look like this:


            

HP ALLBASE/SQL will create a separate workfile for each HP ALLBASE/SQL
query.  For more information about tuning the data access, see "Data
Access Methods" later in this chapter.

Joining a Source Table To Itself 

You can join records from one source table to other records in the same
source table.  Since the source table names in a table must be unique,
nested tables can be used to join a source table to itself, as in the
following example.

Example:  Self-join 

The data set EMPLOYEES contains three items:  EMPLOYEE-NUMBER,
EMPLOYEE-NAME and SUPERVISOR-NUMBER. Suppose you want to report each
supervisor and the employees she manages.  These steps are necessary:

   *   Create the table SUPERVISORS, which only has one source table,
       EMPLOYEES, as shown on this section of the Define Table screen:


            

   *   Create the table EMPL-SUPERS which joins EMPLOYEES and
       SUPERVISORS, as shown here:


            

       This is what the Define Table screen would look like for this
       join:


            

   *   Relate the two tables by the EMPLOYEE-NUMBER from the SUPERVISORS
       table and the SUPERVISOR-NUMBER from the EMPLOYEES table as shown
       below:


            

   *   In your new table, you want the item EMPLNAME from the SUPERVISORS
       table to be called SUPERVISOR-NAME. To rename it, give the item an
       alias name as shown here:


            

   *   The final joined table looks like this:


            

Joining HP ALLBASE/SQL Tables With Identical Table Names 

Within HP ALLBASE/BRW, tables are identified by their table names only.
Therefore, HP ALLBASE/SQL table names belonging to one BRW table must be
unique.  Winthin HP ALLBASE/SQL, however, you may have identical table
names with different owners.  To use these tables in HP ALLBASE/BRW, use
one of the HP ALLBASE/SQL tables to define an HP ALLBASE/BRW table and
give it a unique name.  You can then join the HP ALLBASE/BRW table and
the other HP ALLBASE/SQL table.


NOTE  The naming of HP ALLBASE/SQL tables with identical names should be
      avoided whenever possible.


Example:  Join with identical table names 

In this example, the HP ALLBASE/SQL tables STOREHOUSE.PARTS and
CUSTOMERORDERS.PARTS have different owner names (STOREHOUSE and
CUSTOMERORDERS), but the same table names (PARTS).

   *   Create the HP ALLBASE/BRW table STORED-PARTS from the HP
       ALLBASE/SQL table STOREHOUSE.PARTS.


            

   *   Create the HP ALLBASE/BRW table COMBINED-PARTS. This holds the
       results of joining the HP ALLBASE/BRW table (STORED-PARTS) and the
       HP ALLBASE/SQL table (CUSTOMERORDERS.PARTS).


            

   *   Join the two HP ALLBASE/BRW tables by using the PARTNUMBER in each
       table.


            

MPE/iX 5.0 Documentation