HP 3000 Manuals HP 3000 Manuals
D : View Table: Joining View Tables Now define the view table DOLLARS, which will calculate sales dollars for each order and group them by office. This table requires information stored in two view tables--ORDERS, which you just defined, and PRODUCTS from Lesson 2:G. These view tables draw on information that resides in two different databases, SAMPL1 and SAMPL2.Figure 5-5. View Table DOLLARS Joins Two View Tables You'll learn how to: * Join data from two view tables. * Create a new item by applying a mathematical expression to data in existing items. * Substitute full words for "coded" entries. * Specify a limited group of records. _________________________________________________________ | | | Reference in this Learning manual: | | Lesson 2:G, Configuring a View Table | | | | References in Information Access Server: Database | | Administration: | | Chapter 5, Configuring Tables: | | Adding a View Table Definition | | Appendix B, Constructing View Tables | | Appendix C, View Table Syntax | _________________________________________________________ Here are the items you'll use to define the view table DOLLARS. You can trace each item back through the view table, IMAGE table, and database from which it derives: ---------------------------------------------------------------------------------------------------- | | | | | | Database | IMAGE Tables | View Table | Items | | | | | | ---------------------------------------------------------------------------------------------------- | | | | | | SAMPL1 | SALES-STAFF and ORDER-DETAIL | ORDERS | NUMBER | | | | | OFFICE | | | | | QUANTITY | | | | | DATE | | | | | | ---------------------------------------------------------------------------------------------------- | | | | | | SAMPL2 | PRODUCT-MASTER | PRODUCTS | NUMBER | | | | | PRODUCT | | | | | PRICE | | | | | | ---------------------------------------------------------------------------------------------------- Which Items? --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | Since you have just finished defining one | | | view table, you are exactly where you need | | | to be to define the view table DOLLARS. | | | | | | D-1. Type the text shown below in the | | | fields indicated. (The items are listed on | | | the previous page.) | | | | | | Qualify NUMBER, the item common to both | | | source view tables. The view tables have | | | already taken care of renaming items for | | | use with dBASE II, so no further renaming | | | is needed here. | | | | | | The last item is defined by a mathematical | | | expression. The EXTPRICE column will | | | contain the result of multiplying the | | | quantity ordered by the price per unit. | | | | | | Be sure to separate items with commas. | | | | | | Remain in the Item Clause field. | | | | | --------------------------------------------------------------------------------------------- ________________________________________________________________________________________ | | | | | Table DOLLARS Sample Values Security | | | | Table Description SALES DOLLARS LISTED BY OFFICE | | | | Item Clause (separated by commas) | | ORDERS.NUMBER, | | OFFICE, | | PRODUCT, | | EXTPRICE = QUANTITY * PRICE | | | | | | | | | | | | | | | | | | | | | | | ________________________________________________________________________________________ Substituting Full Words --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | In the view table ORDERS, you used the SUB | | | function to extract a 3-character item, | | | TYPE. | | | | | | Now, use a CASE statement to substitute | | | full words for each 3-character product | | | type designation. | | | | | | D-2. Add a comma after PRICE and type the | | | following CASE statement as the last entry | | | in the Item Clause (lined up for | | | legibility). | | | | | --------------------------------------------------------------------------------------------- ________________________________________________________________________________________ | | | | | PRODTYPE = CASE TYPE = 'FIL': 'FILING PRODUCTS', | | TYPE = 'PAP': 'PAPER PRODUCTS', | | TYPE = 'DES': 'DESK PRODUCTS', | | TYPE = 'PEN': 'WRITING PRODUCTS', | | ELSE TYPE | | END | | | | | | | | | | | ________________________________________________________________________________________ --------------------------------------------------------------------------------------------- | | | | | When this view table is requested, the | | | Administrator Utility will draw out the | | | product number prefix as the item TYPE and | | | then substitute a full product type | | | description for that prefix. | | | | | | For example, each occurrence of product | | | number "FIL104" will show "FILING PRODUCTS" | | | in the PRODTYPE column of the table. | | | | --------------------------------------------------------------------------------------------- | | | | D-3. Proofread carefully, making sure you | Additional fields appear. | | have included commas between each item in | | | the Item Clause. Make corrections and | | | choose: | | | | | | Next f3 | | | Fields | | | | | --------------------------------------------------------------------------------------------- Using Which Joined Tables? --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | The items in the Item Clause come from two | | | view tables--ORDERS which you just defined, | | | and PRODUCTS (with price and product | | | descriptions) defined in Lesson 2:G. | | | | | | You want a table that combines only those | | | records with matching values in the NUMBER | | | column (the item common to both view | | | tables). | | | | | | Again, use a JOIN operation to match | | | records by the value in a common item. | | | | | | D-4. Type the following in the Using | | | Clause field. | | | | | --------------------------------------------------------------------------------------------- ________________________________________________________________________________________ | | | | | Using Clause (separated by spaces) | | ORDERS <NUMBER> JOIN PRODUCTS <NUMBER> | | | | | | | | | ________________________________________________________________________________________ -------------------------------------------------- | | | | | This tells the Administrator Utility to | | | look in the view tables ORDERS and PRODUCTS | | | to find the items. | | | | | | Each time it finds a matching value on | | | NUMBER, it will pull out the OFFICE, | | | ORDER-QTY, and TYPE values from the table | | | ORDERS, and the PRODUCT and PRICE values | | | from the table PRODUCTS. | | | | | | It will join these values into a single | | | record and will calculate new values based | | | on the equation and CASE statement in the | | | Item Clause. | | | | -------------------------------------------------- Where What Conditions Apply? --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | The Where Clause lets you specify | | | conditions each record must meet to be | | | included in the view table. | | | | | | You don't need outdated sales information, | | | so limit records by order date. | | | | | | D-5. In the Where Clause field, type: | Only records representing orders from | | | January 1, 1990 to the present will be | | DATE >= "900101" | included in the table. | | | | --------------------------------------------------------------------------------------------- Records Sorted in What Order? --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | You can sort records based on the values of | | | up to eight different items. Use a | | | two-level sort to group records according | | | to OFFICE and EXTPRICE entries. | | | | | | D-6. In the Sort Clause type: | The records selected according to the Using | | | and Where Clause fields will first be | | OFFICE, EXTPRICE D | sorted in alphabetical order (default is | | | ascending) by office. | | | | | | The group of records for each office will | | | be listed in descending order (D) by | | | extended price. | | | | --------------------------------------------------------------------------------------------- Table 5-0. (cont.) --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | D-7. To configure the view table, press | The screen clears and displays the first | | Enter. | group of Add View Table fields. Your entry | | | has been accepted. | | (If you made a syntax error when typing any | | | of the clauses, an error message appears | You have told the Administrator Utility to | | and the cursor goes to the field with the | draw the items you specified from records | | error. Correct your mistake and press | in the view tables ORDERS and PRODUCTS, | | Enter.) | based on matching values in the item | | | NUMBER, for orders placed from 1990 on. | | | You asked to display them grouped | | | alphabetically by office with the highest | | | extended price first in each office's group | | | of records. | | | | | | This set of instructions will produce a | | | view table that calculates the sales | | | dollars generated by each order and lists | | | them by office. | | | | | | This table is derived from view tables. In | | | order to provide current values for the new | | | view table, the utility will first | | | construct all the "source" tables. This | | | includes the IMAGE tables that draw the | | | values from the datasets in the two | | | databases and the view tables that | | | manipulate those values within Access | | | Server. | | | | | (If you wanted to calculate the total sales | | | dollars per office, you could do so from | | | this table with the Information Access | | | Report Writer on a PC.) | | | | | --------------------------------------------------------------------------------------------- What This Table Will Look Like An Access PC query that chooses columns NUMBER, PRODUCT, EXTPRICE, and PRODTYPE in view table DOLLARS will produce this display:Figure 5-6. PC Display of View Table DOLLARS Summary In this exercise, you learned how to: * Use the JOIN operation to combine records from two view tables by matching values in a specified item. (This process is the same as joining two IMAGE tables.) * Multiply the value of one item by the value of another to calculate an item with an entirely new value. (Note that the two items in the expression are derived from different datasets residing in different databases.) * Use a CASE statement to substitutefull words for "coded" entries. (This was the second step in a two-step process of drawing out the codes, then defining the full word equivalents.) * Use the Where Clause to specify a limited group of records. * Use a two-level sort to specify record order within each primary record grouping.
![[]](../../pic3k/M012040/FFN673c50.gif)
![[]](../../pic3k/M012040/FFN723c50.gif)