HP 3000 Manuals HP 3000 Manuals
Example Showing View Table Flexibility
In the preceding pages, the examples illustrate only the feature being
described. Below, we show you how JOINs, PRODUCTIONs, the SUB function,
and item concatenation might play together in a more extended example.
We begin by looking at the contents of three IMAGE tables, each of which
contains only part of the information we want to draw together into one
table. Then we walk you through three possible scenarios of view table
creation that lead to the same final view of the data.
The IMAGE Tables
The first IMAGE table is called ACCT-NUMBERS. It contains two items of
type character: EXPENSE identifies the kind of expense, and ACCT-NO
gives the account numbers within that expense type (for example, under
Travel Expense, 1200 might refer to the cost of meals). The table
contains six records and looks like this:
ACCT-NUMBERS
----------------------------------
| EXPENSE | ACCT-NO |
----------------------------------
| Office Expense | 1000 |
| Office Expense | 1010 |
| Depreciation Expense | 1100 |
| Depreciation Expense | 1110 |
| Travel Expense | 1200 |
| Travel Expense | 1210 |
----------------------------------
The second IMAGE table is called DEPT-NUMBERS. It contains two items of
type character: FUNCTION-AREA identifies the functional area, and
DEPT-NO gives the numbers of the departments within the functional area.
The table contains five records and looks like this:
DEPT-NUMBERS
---------------------------
| FUNCTION-AREA | DEPT-NO |
---------------------------
| Manufacturing | 5000 |
| Manufacturing | 5010 |
| Marketing | 6000 |
| Marketing | 6010 |
| Manufacturing | 5020 |
---------------------------
The third IMAGE table, GENERAL-LEDGER, contains two items. The first,
ACCT-DEPT-NO, is an item of type character in which the first four
characters give the account number and the second four characters give
the department number. The second item, ACCT-BALANCE, is of type real
and shows the account balance for the given combination of account and
department. The table contains seven records and looks like this:
GENERAL-LEDGER
-------------------------------
| ACCT-DEPT-NO | ACCT-BALANCE |
-------------------------------
| 10005000 | 50.00 |
| 10105000 | 140.00 |
| 11005000 | 44.00 |
| 11005010 | 470.00 |
| 10006000 | 50.00 |
| 10006010 | 10.00 |
| 12006000 | 25.00 |
-------------------------------
Our objective: We want to make this third table more intelligible to our
users by replacing account numbers and department numbers with the names
of the functional areas and expense types to which they refer.
Three Ways to Create the Desired View of the Data
Below, we explore three ways to use view table definitions to reach our
objective.
Using the SUB Function in the Item Clause.
The first way of producing the desired view of the data involves:
* Defining the view table PRE1 from the IMAGE table GENERAL-LEDGER.
* Defining the view table RESULT1 by JOINing the three tables PRE1,
ACCT-NUMBERS, and DEPT-NUMBERS.
Here's how the SUB function can be used in the definition of PRE1 to
extract the two substrings from the ACCT-DEPT-NO item and make them each
an item of type character (AN and DN) in PRE1.
What You Enter:
Table: PRE1
Item Clause: AN=SUB(ACCT-DEPT-NO,1,4),
DN=SUB(ACCT-DEPT-NO,5,4),
ACCT-BALANCE
Using Clause: GENERAL-LEDGER
The Resulting View Table:
PRE1
------------------------------
| AN | DN | ACCT-BALANCE |
------------------------------
| 1000 | 5000 | 50.00 |
| 1010 | 5000 | 140.00 |
| 1100 | 5000 | 44.00 |
| 1100 | 5010 | 470.00 |
| 1000 | 6000 | 50.00 |
| 1000 | 6010 | 10.00 |
| 1200 | 6000 | 25.00 |
------------------------------
Now that the items AN and DN have been defined in PRE1, we can use them
to find matches on the items ACCT-NO and DEPT-NO. We do that using the
JOIN operation in our definition of the RESULT1 view table below.
What You Enter:
Table: RESULT1
Item Clause: FUNCTION-AREA,
EXPENSE,
ACCT-BALANCE
Using Clause: (PRE1<AN> JOIN ACCT-NUMBERS<ACCT-NO>)<DN>
JOIN DEPT-NUMBERS<DEPT-NO>
The Resulting View Table:
RESULT1
-------------------------------------------------------
| FUNCTION-AREA | EXPENSE | ACCT-BALANCE |
-------------------------------------------------------
| Manufacturing | Office Expense | 50.00 |
| Manufacturing | Office Expense | 140.00 |
| Manufacturing | Depreciation Expense | 44.00 |
| Manufacturing | Depreciation Expense | 470.00 |
| Marketing | Office Expense | 50.00 |
| Marketing | Office Expense | 10.00 |
| Marketing | Travel Expense | 25.00 |
-------------------------------------------------------
As the Using Clause indicates, the PRE1 and ACCT- NUMBERS tables are
first JOINed on the items AN and ACCT-NO. Then the result is JOINed with
the DEPT-NUMBERS table on the items DN and DEPT-NO.
From the final result, the Item Clause identifies the items included in
the view table, which appears as shown.
Using Concatenation in the Item Clause.
The second way of producing the desired view of the data involves:
* Defining the view table PRE2 using a concatenate operation in the
Item Clause and a PRODUCTION operation in the Using Clause.
* Defining the view table RESULT2 using a JOIN operation between the
PRE2 and GENERAL-LEDGER tables.
Here's how you might define PRE2:
What You Enter:
Table: PRE2
Item Clause: FUNCTION-AREA,
EXPENSE,
AD = ACCT-NO + DEPT-NO
Using Clause: ACCT-NUMBERS DEPT-NUMBERS
The Resulting View Table:
PRE2
---------------------------------------------------
| FUNCTION-AREA | EXPENSE | AD |
---------------------------------------------------
| Manufacturing | Office Expense | 10005000 |
| Manufacturing | Office Expense | 10005010 |
| Marketing | Office Expense | 10006000 |
| Marketing | Office Expense | 10006010 |
| Manufacturing | Office Expense | 10005020 |
| Manufacturing | Office Expense | 10105000 |
| Manufacturing | Office Expense | 10105010 |
| Marketing | Office Expense | 10106000 |
| Marketing | Office Expense | 10106010 |
| Manufacturing | Office Expense | 10105020 |
| Manufacturing | Depreciation Expense | 11005000 |
| Manufacturing | Depreciation Expense | 11005010 |
| Marketing | Depreciation Expense | 11006000 |
| Marketing | Depreciation Expense | 11006010 |
| Manufacturing | Depreciation Expense | 11005020 |
| Manufacturing | Depreciation Expense | 11105000 |
| Manufacturing | Depreciation Expense | 11105010 |
| Marketing | Depreciation Expense | 11106000 |
| Marketing | Depreciation Expense | 11106010 |
| Manufacturing | Depreciation Expense | 11105020 |
| Manufacturing | Travel Expense | 12005000 |
| Manufacturing | Travel Expense | 12005010 |
| Marketing | Travel Expense | 12006000 |
| Marketing | Travel Expense | 12006010 |
| Manufacturing | Travel Expense | 12005020 |
| Manufacturing | Travel Expense | 12105000 |
| Manufacturing | Travel Expense | 12105010 |
| Marketing | Travel Expense | 12106000 |
| Marketing | Travel Expense | 12106010 |
| Manufacturing | Travel Expense | 12105020 |
---------------------------------------------------
The concatenate operation drew together two 4-character items into one
8-character item, so that in the next step the new item can be JOINed
against the existing item ACCT-DEPT-NO.
Note that the PRODUCTION operation produces a 30-record table because the
number of records is the product of a 5-record table and a 6-record
table. Because of this multiplicative effect, PRODUCTIONs should be used
with care.
Now that the item AD has been defined in PRE2, we can use it to find
matches on the item ACCT-DEPT-NO. We do that using a JOIN operation in
our definition of the RESULT2 view table below.
What You Enter:
Table: RESULT2
Item Clause: FUNCTION-AREA,
EXPENSE,
ACCT-BALANCE
Using Clause: PRE2<AD> JOIN GENERAL-LEDGER<ACCT-DEPT-NO>
The Resulting View Table:
RESULT2
-------------------------------------------------------
| FUNCTION-AREA | EXPENSE | ACCT-BALANCE |
-------------------------------------------------------
| Manufacturing | Office Expense | 50.00 |
| Marketing | Office Expense | 50.00 |
| Marketing | Office Expense | 10.00 |
| Manufacturing | Office Expense | 140.00 |
| Manufacturing | Depreciation Expense | 44.00 |
| Manufacturing | Depreciation Expense | 470.00 |
| Marketing | Travel Expense | 25.00 |
-------------------------------------------------------
RESULT2 includes only those records that match on AD and ACCT-DEPT-NO.
The same records appear in RESULT2 as in RESULT1, though their order
differs. If ordering of the records were crucial, we could have used the
Sort Clause in RESULT2's view table definition to specify it.
Using the SUB Function in the Where Clause.
The third way of producing the desired view of the data involves defining
just one view table RESULT3. Here we illustrate a three-way PRODUCTION
in the Using Clause and the use of the SUB function in the Where Clause.
Here's how RESULT3 might be defined:
What You Enter:
Table: RESULT3
Item Clause: FUNCTION-AREA,
EXPENSE,
ACCT-BALANCE
Using Clause: ACCT-NUMBERS GENERAL-LEDGER DEPT-NUMBERS
Where Clause: DEPT-NO=SUB(ACCT-DEPT-NO,5,4) AND
ACCT-NO=SUB(ACCT-DEPT-NO,1,4)
The Resulting View Table:
RESULT3
-------------------------------------------------------
| FUNCTION-AREA | EXPENSE | ACCT-BALANCE |
-------------------------------------------------------
| Manufacturing | Office Expense | 50.00 |
| Marketing | Office Expense | 50.00 |
| Marketing | Office Expense | 10.00 |
| Manufacturing | Office Expense | 140.00 |
| Manufacturing | Depreciation Expense | 44.00 |
| Manufacturing | Depreciation Expense | 470.00 |
| Marketing | Travel Expense | 25.00 |
-------------------------------------------------------
The Where Clause selects only those records for which DEPT-NO and ACCT-NO
match the respective parts of ACCT-DEPT-NO. Then the Item Clause
identifies the three columns to be included in RESULT3.