HP 3000 Manuals HP 3000 Manuals
Some Simple Ways to Define View Tables
The following examples illustrate two ways to create a Using Clause (one
involves a PRODUCTION, one a JOIN) and one way to create a Where Clause.
For these examples, we'll use two tables, DEPARTMENT and EMPLOYEE. These
tables have been previously configured in the data dictionary (as IMAGE,
file, or view tables). Below, we'll be deriving three view tables from
them.
DEPARTMENT
---------------------------
| DEPTNUM | NAME |
---------------------------
| 474 | Marketing |
| 525 | Manufacturing |
| 820 | Personnel |
---------------------------
EMPLOYEE
-------------------------------
| EMPNUM | NAME | DEPTNUM |
-------------------------------
| 239 | Duncan | 474 |
| 374 | Williams | 525 |
-------------------------------
PRODUCTION Operation
First, we'll use the PRODUCTION operation to create the view table
TABLE1. The PRODUCTION of two tables results in a table that contains
every combination of records from both tables. The word PRODUCTION is
not required. Just leave a blank between table names in the Using
Clause.
What You Enter:
Table: TABLE1
Item Clause: DEPT=DEPARTMENT.NAME,EMP=EMPLOYEE.NAME
Using Clause: DEPARTMENT EMPLOYEE
The Resulting View Table:
TABLE1
-----------------------------
| DEPT | EMP |
-----------------------------
| Marketing | Duncan |
| Marketing | Williams |
| Manufacturing | Duncan |
| Manufacturing | Williams |
| Personnel | Duncan |
| Personnel | Williams |
-----------------------------
Because there were three entries under NAME in the DEPARTMENT table and
two entries under NAME in the EMPLOYEE table, TABLE1 combined them in the
six possible ways to produce six records.
NOTE Be careful with PRODUCTIONs. If the two tables combined above had
had 1000 entries each, the PRODUCTION would have produced a
1,000,000 record view table since there is no Where Clause.
Since NAME is not a unique item name in the tables specified in the Using
Clause, we must qualify it, at each use, with its table name in the Item
Clause. So we defined the item DEPT, for example, as being derived from
the item NAME in the table DEPARTMENT by typing DEPT=DEPARTMENT.NAME.
JOIN Operation
Next, we'll use the JOIN operation to create the view table TABLE2. The
JOIN of two tables results in a table that combines only those records
with matching values in a specified column or columns.
What You Enter:
Table: TABLE2
Item Clause: EMPNUM,EMP=EMPLOYEE.NAME,DEPT=DEPARTMENT.NAME
Using Clause: EMPLOYEE <DEPTNUM> JOIN DEPARTMENT <DEPTNUM>
The Resulting View Table:
TABLE2
--------------------------------------
| EMPNUM | EMP | DEPT |
--------------------------------------
| 239 | Duncan | Marketing |
| 374 | Williams | Manufacturing |
--------------------------------------
In this example, we did not qualify the item EMPNUM because EMPNUM is a
unique item (column heading) in the tables we're using.
The view table is created by looking for matching records, records that
show the same value in each of the columns designated in the two tables.
For example, the first record in each of the tables EMPLOYEE and
DEPARTMENT match on their DEPTNUM columns, so we pull out the values of
the three items specified and use those values as the first record in our
view table.
Where Clause
Finally, an illustration using the Where Clause to create the view table
TABLE3. With the Where Clause, you can pare down the number of records
in the view table by indicating the characteristics of the records you
want included.
The operators you can use in this clause are =, <, >, <=, >=, <>, and
MATCH. You can also combine operators using the AND, OR, and NOT
functions.
What You Enter:
Table: TABLE3
Item Clause: DEPTNUM,NAME
Using Clause: DEPARTMENT
Where Clause: (DEPTNUM > "500") AND (DEPTNUM < "900")
The Resulting View Table:
TABLE3
----------------------------
| DEPTNUM | NAME |
----------------------------
| 525 | Manufacturing |
| 820 | Personnel |
----------------------------
A view table need not be derived from several tables. It can be derived
from one previously configured table, as in this example, where we used
the Where Clause to reduce the number of records included in TABLE3.
In this example, the Where Clause brought in only those records with a
DEPTNUM between 500 and 900. The effect was to exclude the first record
in DEPARTMENT, since its DEPTNUM is 474.