HP 3000 Manuals HP 3000 Manuals
Using the Compile Listing HP BRW/XL's compiler generates a listing that shows how the relational data access definition was translated and the optimizations that have been performed. Example Below are two examples of how you can use the compile listing for a report. For these examples, the report uses the table PRINT-TABLE which joins the datasets PRODUCTS and ORDER-DETAILS. The Define Table screen for this join looks like this:The report contains these definitions: * a selection condition, as shown here: MONTH OF (ORDER-DETAILS.SHIPMENT-DATE) = ?MONTH AND PRODUCTS.PRODUCT-LINE SATISFIES ?PRODUCT-LINE * the table calculated item on-stock-amount with the following formula: PRODUCTS.PRICE * PRODUCTS.QUANTITY * the table calculated item ordered-amount with the following formula: PRODUCTS.PRICE * ORDER-DETAILS.QUANTITY * the table calculated item month-words with the following formula: ?MONTH OF ("JAN", "FEB", "MAR", "APR", "MAY", "JUN" "JUL", "AUG", "SEP", "OCT", "NOV", "DEC") Example 1: Using Keyed Access For This Report To use keyed access for this report, the following data access methods are specified on the Tune Access screen:The listing generated by the compiler looks like this: _________________________________________________________________________ | | | | | D A T A A C C E S S S T R U C T U R E | | ========================================= | | PRINT-TABLE (WORK001) join of: | | ------------------------------- | | ORDER-DETAILS, TOYDB.PUB serial | | PRODUCTS, TOYDB.PUB key: PRODUCT-NO | | | | T A B L E C A L C U L A T I O N S A N D S E L E C T I O N S | | ================================================================= | | PRINT-TABLE (WORK001) | | ORDER-DETAILS, TOYDB.PUB | | | | Selection: month_of (ORDER_DETAILS.SHIPMENT_DATE) = ?MONTH| | | | PRODUCTS, TOYDB.PUB | | | | Calculated items : ON-STOCK-AMOUNT ORDERED-AMOUNT | | | | Selection : PRODUCTS.PRODUCT-LINE satisfies ?PRODUCT-LINE | | | | C O N S T A N T C A L C U L A T E D I T E M S | | ================================================= | | MONTH-WORDS | | | _________________________________________________________________________ The DATA ACCESS STRUCTURE section on this listing shows that PRINT-TABLE creates the work file WORK001, which contains the result of the join. The join is performed by reading the dataset ORDER-DETAILS serially and the dataset PRODUCTS by the key product-no. The TABLE CALCULATIONS AND SELECTIONS section shows what is performed when each data set is read: When the dataset ORDER-DETAILS is read, the following is performed: MONTH_OF (ORDER-DETAILS.SHIPMENT-DATE) = ?MONTH When a record from the dataset PRODUCTS is read, the following is performed: * Calculation of the items on-stock-amount and ordered-amount * Selection of the following: PRODUCTS.PRODUCT-LINE SATISFIES ?PRODUCT-LINE The CONSTANT CALCULATED ITEMS section shows that the calculated item month-words has a constant value for the whole report (because it is calculated only from constants and parameters), so it is calculated only once. Example 2: Using Serial Access for This Report In this example, the datasets are accessed serially. The Tune Access screen below shows the access sequence:The compiler generates the following compile listing: _____________________________________________________________________________ | | | | | D A T A A C C E S S S T R U C T U R E | | ========================================= | | PRINT-TABLE (WORK003) join of: | | -------------------------------- | | WORK001 | | PRODUCTS, TOYDB.PUB serial | | | | WORK002 | | ORDER-DETIALS, TOYDB.PUB serial | | | | | | T A B L E C A L C U L A T I O N S A N D S E L E C T I O N S | | ================================================================= | | PRINT TABLE (WORK003) | | WORK001 | | | | WORK002 | | | | Calculated items: ORDERED-AMOUNT | | | | WORK001 | | PRODUCTS, TOYDB.PUB | | | | Calculated items : ON-STOCK-AMOUNT | | | | Selection : PRODUCTS.PRODUCT-LINE satisfies ?PRODUCT-LINE | | WORK002 | | ORDER-DETAILS, TOYDB.PUB | | | | Selection : month-of (ORDER-DETAILS.SHIPMENT-DATE) = ?MONTH| | | | C O N S T A N T C A L C U L A T E D I T E M S | | ================================================= | | MONTH-WORDS | | | | | _____________________________________________________________________________ The DATA ACCESS STRUCTURE section of the listing shows that the table PRINT- TABLE creates a work file WORK003, which contains the result of the join. The join is performed by reading the work files WORK001 and WORK002. WORK001 is created by reading the dataset PRODUCTS serially; WORK002 by reading ORDER-DETAILS serially. The TABLE CALCULATIONS AND SELECTIONS section shows that the item ordered-amount is calculated when the work files are joined. Selections and calculation of the item on-stock-amount are performed when the datasets are read, which is earlier during execution of the report. The CONSTANT CALCULATED ITEMS section is the same as in Example 1. Example 3: Accessing HP ALLBASE/SQL Below is the Tune Access screen from "Combining Data from Different Sources" of Chapter 6. In this example, the two HP ALLBASE/SQL tables default to the same access sequence number.The compile listing on the next page shows that when the access sequence number is the same, only one HP ALLBASE/SQL query is generated for both tables. Selection conditions for HP ALLBASE/SQL do not appear under TABLE CALCULATIONS AND SELECTIONS when HP ALLBASE/SQL makes the selection. Instead, selections that can be performed by HP ALLBASE/SQL show up in the SQL query included at the bottom of the compile listing. By assigning different access sequence numbers to HP SQL tables, separate queries are generated and HP ALLBASE/BRW performs the join. ___________________________________________________________________________________ | | | | | D A T A A C C E S S S T R U C T U R E | | | | ========================================= | | | | ROOT (WORK001) join of: | | | | ----------------------- | | | | CUSTOMERS,EXPLSQL.EXPLDB.TESTSQL SQL | | | | INVOICES,EXPLSQL.EXPLDB.TESTSQL SQL | | | | ORDERS,EXPLDB.EXPLDB.TESTSQL key: ORDER-NO | | | | | | | | | | | | T A B L E C A L C U L A T I O N S A N D S E L E C T I O N S | | | | ================================================================= | | | | ROOT (WORK001) | | | | CUSTOMERS,EXPLSQL.EXPLDB.TESTSQL | | | | INVOICES,EXPLSQL.EXPLDB.TESTSQL | | | | ORDERS,EXPLDB.EXPLDB.TESTSQL | | | | Selection: ORDERS.ORDER-DATE = ?ORDERDATE | | | | | | | | G E N E R A T E D S Q L Q U E R I E S | | | | ========================================= | | | | ROOT (WORK001) | | | | Query Number: 1 | | | | | | | | SELECT T1.CUSTOMER_NAME,T2.ORDER_NO | | | | FROM MGR@TESTSQL.CUSTOMERS T1,MGR@TESTSQL.INVOICES T2 | | | | WHERE (T1.CUSTOMER_NO=t2.CUSTOMER_NO) AND (T1.CUSTOMER_NAME=?CUSTOMER):| | | | | | | | | ___________________________________________________________________________________
![[]](../../pic3k/M011795/FFN2453c50.gif)
![[]](../../pic3k/M011795/FFN2463c50.gif)
![[]](../../pic3k/M011795/FFN2483c50.gif)
![[]](../../pic3k/M011795/FFN2503c50.gif)