HP 3000 Manuals HP 3000 Manuals
C : View Table: Joining IMAGE Tables In Lesson 2:G, you configured a simple view table that merely changed the names of dataset items to a format required by a PC application. View tables can be used to structure information in many other ways. In this and the next two exercises, you'll explore some of them. First, define a view table called ORDERS, to join items from the two IMAGE tables you just defined. This view table will list all orders generated by each employee, sorted by employee number.Figure 5-3. View Table ORDERS Joins Two IMAGE Tables You'll learn how to: * Join data from more than one table. * Define part of an item as a new item. * Sort records according to the contents of one item. __________________________________________________________ | | | Reference in this Learning manual: | | Lesson 1:D, How to Configure the Data Dictionary| | Lesson 2:G, Configuring a View Table | | Appendix A, Schema for SAMPL1 Database | | | | 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 | __________________________________________________________ Once the view table ORDERS is defined, you'll use it in the next exercise to define a second view table, DOLLARS. That table will calculate the sales dollars generated by each order. Some of the items included in the view table ORDERS are for use in this second view table. Here are the items you'll use to define the view table ORDERS, with the tables and the database from which the items are derived: ---------------------------------------------------------------------------------------------- | | | | | Database | IMAGE Table | Items | | | | | ---------------------------------------------------------------------------------------------- | | | | | SAMPL1 | SALES-STAFF | | | | | EMP-NBR | | | | OFFICE * | | | | | ---------------------------------------------------------------------------------------------- | | | | | SAMPL1 | ORDER-DETAIL | | | | | EMP-NBR | | | | PRODUCT-NBR | | | | ORDER-QTY | | | | ORDER-DATE * | | | | | ---------------------------------------------------------------------------------------------- * These items will be used later in this lesson. Which Items?--------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | C-1. At the Add Table Menu, choose: | The Add View Table screen appears. | | | | | View f1 | Each field on these screens has its | | Table | required syntax. (You'll find the syntax | | | in Chapter 5 and Appendix C of the | | | Information Access Server: Database | | | Administration manual.) | | | | --------------------------------------------------------------------------------------------- | | | | C-2. Type the text shown on the next page | | | in the fields indicated. (The items are | | | listed on the previous page.) | | | | | | When the items for a view table come from | | | more than one table, item names that are | | | not unique, such as EMP-NBR, can be | | | qualified with a table name (table.item) in | | | the Item Clause. | | | | | | PRODUCT-NBR, ORDER-QTY, and ORDER-DATE are | | | being renamed to fewer than eight | | | characters for use with dBASE II on PCs. | | | | | | Be sure you use commas to separate items. | | | | | | Remain in the Item Clause field. | | | | | --------------------------------------------------------------------------------------------- ________________________________________________________________________________________ | | | | | Table ORDERS Sample Values Security | | | | Table Description ORDERS LISTED BY EMPLOYEE NUMBER | | | | Item Clause (separated by commas) | | SALES-STAFF.EMP-NBR, | | OFFICE, | | NUMBER = PRODUCT-NBR, | | QUANTITY = ORDER-QTY, | | DATE = ORDER-DATE | | | | | | | | | | | | | | | | | | | | | | | ________________________________________________________________________________________ Extracting a Substring --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | The table DOLLARS you're going to define | | | next requires an item for product type. | | | | | | Product type is not a separate item in any | | | of the IMAGE tables you have defined. | | | Rather, it is entered as the first three | | | characters of each product number. So, you | | | need to define an item that includes these | | | characters only. | | | | | | The SUB function lets you define a new item | | | by extracting a substring from an item. | | | | | | C-3. Add a comma after ORDER-DATE and type | The SUB function you specified will extract | | the following expression as the last item | bytes 1 through 3 of the item PRODUCT-NBR | | in the Item Clause: | to define the new item, TYPE. | | | | | TYPE = SUB (PRODUCT-NBR,1,3) | (SUB function syntax is discussed fully in | | | Appendixes B and C in the Information | | | Access Server: Database Administration | | | manual.) | | | | --------------------------------------------------------------------------------------------- Table 5-0. (cont.) --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | C-4. Proofread carefully, making sure you | Additional fields appear. | | have included commas between all items. | | | Make any corrections and choose: | | | | | | Next f3 | | | Fields | | | | | --------------------------------------------------------------------------------------------- Using Which Joined Tables? --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | The items just listed in the Item Clause | | | come from the two IMAGE tables you defined | | | earlier in this lesson--one with staffing | | | information, the other with order | | | information. | | | | | | When items come from more than one table, | | | you must indicate how to combine the | | | records that will be retrieved. In this | | | case, you want a table that combines only | | | those records with matching values in the | | | EMP-NBR column (an item common to both | | | tables). | | | | | | The JOIN operation lets you match records | | | by the value of a common item. | | | | | | C-5. In the Using Clause, type the text | | | shown below. | | | | | --------------------------------------------------------------------------------------------- ________________________________________________________________________________________ | | | | | Using Clause (separated by spaces) | | SALES-STAFF <EMP-NBR> JOIN ORDER-DETAIL <EMP-NBR> | | | | | | | ________________________________________________________________________________________ -------------------------------------------------- | | | | | This tells the Administrator Utility to | | | look in the IMAGE tables SALES-STAFF and | | | ORDER-DETAIL to find the items. | | | | | | Each time it finds a matching value on | | | EMP-NBR, it will pull out the OFFICE value | | | from the table SALES-STAFF and the | | | PRODUCT-NBR, ORDER-QTY, and ORDER-DATE | | | values from the table ORDER-DETAIL. | | | | | | It will join these values into a single | | | record, and calculate a new value based on | | | the SUB function. | | | | --------------------------------------------------
NOTE If you had simply listed both IMAGE tables in the Using Clause without specifying a JOIN operation based on a particular item, the resulting view table would have included every possible combination of all specified items from both tables. This is called a PRODUCTION. It produces a table with many more records than you want in most situations.
Which Records? --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | The JOIN operation helps define which | | | records will be selected for the view | | | table. The Where Clause can be used to | | | further specify which records to include, | | | if desired. The Sort Clause lets you | | | control the order in which the selected | | | records will appear. | | | | | | C-6. Tab over the Where Clause and type | Because no Where Clause is being specified | | EMP-NBR in the Sort Clause. | to define additional record selection | | | criteria, all records that have a match on | | | EMP-NBR will be included in the table. | | | | | | The records will be sorted in ascending | | | order (the default) by EMP-NBR. That is, | | | all of one employee's orders, then the next | | | employee's orders, then the next. | | | | --------------------------------------------------------------------------------------------- Table 5-0. (cont.) --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | C-7. To configure the view table, press | The screen displays the first group of Add | | Enter. | View Table fields. Your entry has been | | | accepted. | | (If you made a syntax error when typing, an | | | error message appears and the cursor goes | You have told the Administrator Utility to | | to the field with the error. Correct your | draw the items you specified from records | | mistake and press Enter.) | in the tables SALES-STAFF and ORDER-DETAIL, | | | matching on EMP-NBR, and to group the new | | | records by employee number. | | | | --------------------------------------------------------------------------------------------- What This Table Will Look Like An Access PC query that chooses columns EMP-NBR, NUMBER, QUANTITY, and TYPE in view table ORDERS will produce this display:Figure 5-4. PC Display of View Table ORDERS Summary In this exercise, you learned how to: * Use a JOIN operation to combine data from two tables into a limited number of records by matching the values in a specified field. * Use the SUB function to define a substring of an item as a new item. * Use the Sort Clause to order the records according to the values in a specified item.
![[]](../../pic3k/M012040/FFN613c50.gif)
![[]](../../pic3k/M012040/FFN633c50.gif)
![[]](../../pic3k/M012040/FFN663c50.gif)