HP NonStop SQL/MX Report Writer Guide Abstract This manual explains how to use the HP NonStop™ SQL/MX report writer commands, clauses, and functions, and the MXCI options that relate to reports. The manual tells how to produce formatted reports using data from a NonStop SQL/MX database. Product Version NonStop SQL/MX Release 2.0 Supported Release Version Updates (RVUs) This publication supports G06.23 and all subsequent G-series RVUs until otherwise indicated by its replacement publication.
Document History Part Number Product Version Published 527194-001 NonStop SQL/MX Release 2.0 April 2004 527194-002 NonStop SQL/MX Release 2.
HP NonStop SQL/MX Report Writer Guide Index Figures What’s New in This Manual vii Manual Information vii New and Changed Information Tables vii About This Manual ix Audience ix Further Reading ix How to Use This Manual ix Related Documentation x Notation Conventions xii 1.
2. Using MXCI and the Report Writer Contents 2.
4. Customizing a Report Contents Preparing a SELECT Command 3-25 Using Views 3-26 Using Subqueries 3-28 Developing Multistep Queries 3-32 Multilevel Group Aggregates 3-33 Conditional Aggregates 3-34 Row Value as Percent of All Row Values 3-35 4.
Contents A.
Tables Contents Figure 3-5. Figure 3-6. Figure 3-7. Figure 3-8. Figure 4-1. Figure 4-2. Figure 4-3. Figure 4-4. Figure 4-5. Figure 4-6. Figure 4-7. Figure 4-8. Figure 4-9. Figure 4-10. Figure 4-11. Figure 4-12. Figure 4-13. Figure 4-14. Figure 4-15. Figure 4-16. Figure 4-17. Figure 4-18. Figure 4-19. Figure 4-20. Figure 4-21. Figure 4-22.
Contents HP NonStop SQL/MX Report Writer Guide—527194-002 vi
What’s New in This Manual Manual Information HP NonStop SQL/MX Report Writer Guide Abstract This manual explains how to use the HP NonStop™ SQL/MX report writer commands, clauses, and functions, and the MXCI options that relate to reports. The manual tells how to produce formatted reports using data from a NonStop SQL/MX database. Product Version NonStop SQL/MX Release 2.0 Supported Release Version Updates (RVUs) This publication supports G06.
What’s New in This Manual New and Changed Information HP NonStop SQL/MX Report Writer Guide—527194-002 viii
About This Manual This manual provides task-oriented instructions on how to design and produce reports using the report writer and information from a NonStop SQL/MX database. The manual also provides an overview of the report writer commands and MXCI commands that are directly related to formatting reports. Audience Readers should have some experience in using command interpreters and be familiar with the HP NonStop Kernel operating system.
Related Documentation About This Manual Related Documentation This manual is part of the SQL/MX library of manuals, which includes: Introductory Guides SQL/MX Comparison Guide for SQL/MP Users Describes SQL differences between SQL/MP and SQL/MX. SQL/MX Quick Start Describes basic techniques for using SQL in the SQL/MX conversational interface (MXCI). Includes information about installing the sample database.
Related Documentation About This Manual SQL/MX Report Writer Guide Describes how to produce formatted reports using data from a NonStop SQL/MX database. SQL/MX Connectivity Service Manual Describes how to install and manage the SQL/MX Connectivity Service (MXCS), which enables applications developed for the Microsoft Open Database Connectivity (ODBC) application programming interface (API) and other connectivity APIs to use SQL/MX.
Notation Conventions About This Manual Figure i.
General Syntax Notation About This Manual This requirement is described under Backup DAM Volumes and Physical Disk Drives on page 3-2. General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS. Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: MAXATTACH lowercase italic letters.
General Syntax Notation About This Manual braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. For example: LISTOPENS PROCESS { $appl-mgr-name } { $process-name } ALLOWSU { ON | OFF } | Vertical Line. A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example: INSPECT { OFF | ON | SAVEABEND } … Ellipsis.
Change Bar Notation About This Manual !i and !o. In procedure calls, the !i notation follows an input parameter (one that passes data to the called procedure); the !o notation follows an output parameter (one that returns data to the calling program). For example: CALL CHECKRESIZESEGMENT ( segment-id , error ) ; !i !o !i,o. In procedure calls, the !i,o notation follows an input/output parameter (one that both passes data to the called procedure and returns data to the calling program).
Change Bar Notation About This Manual HP NonStop SQL/MX Report Writer Guide—527194-002 xvi
1 Introduction to the NonStop SQL/MX Report Writer Accessing Data From SQL/MP Tables 1-1 Database Organization 1-2 Report Development Steps 1-4 Default Report Format 1-6 Customized Reports 1-8 Report Formatting Commands 1-14 Report Formatting Command Clauses 1-15 Style Options 1-15 Layout Options 1-16 Report Functions 1-17 MXCI Commands 1-17 NonStop SQL/MX is a relational database management system that uses the industry standard SQL language to define and manipulate data.
Database Organization Introduction to the NonStop SQL/MX Report Writer For information on the SQL/MP language, see the SQL/MP Reference Manual . Database Organization In an SQL/MX database, data is organized and maintained in tables. A table consists of vertical columns and horizontal rows: • • A row contains pieces of related data, such as an employee's identification number, name, and address. A column contains data of the same type, such as all employee ID numbers. Columns have names; rows do not.
Introduction to the NonStop SQL/MX Report Writer SQL Database Reports SQL Database Reports MXCI generates a default report definition for each SELECT command you enter during your Report Writer session. You can override the default report by using layout options, style options, and report formatting commands. Layout and style options are global and not related to a specific SELECT command.
Introduction to the NonStop SQL/MX Report Writer Report Development Steps Report Development Steps The steps for using the MXCI report writer to develop a report are described in detail in the remainder of this guide. This summary provides an overview: 1. Setting report writer mode Before developing a report, you must set report writer mode . Note. All the examples in this guide assume that you have set report writer mode before executing any report writer commands. 2.
Introduction to the NonStop SQL/MX Report Writer Report Layout and Style To change the appearance of a report, use the layout options to determine: • • • • Margins The number of lines per page Line spacing Whether default headings are to print above columns of data 4. Customizing the style Use the style options to customize: • • • 5.
Default Report Format Introduction to the NonStop SQL/MX Report Writer Default Report Format You can display or print a report without specifying any report formatting commands, layout options, or style options. The report is produced in the default format and is called the default report. The tables referred to are in the same catalog and schema, so your default catalog and schema should have been set at the beginning of this session.
Introduction to the NonStop SQL/MX Report Writer Default Layout and Style Options The WHERE clause also specifies that rows of the PARTS table are joined with rows of the ODETAIL table when the rows have the same part number. The components of the default report are: • • Detail lines ° Each detail line contains all elements of the select list in the order you specify in the SELECT command. (In Figure 1-2, the columns of data correspond to the data items as they were listed in the SELECT command.
Introduction to the NonStop SQL/MX Report Writer Customized Reports For a summary of layout and style options, which includes the default setting of each option, see Report Writer Components on page 1-12. For detailed information about layout and style options, see the SQL/MX Reference Manual. Customized Reports By entering report formatting commands, you can define these additional report elements: • • • Page title: A title appears at the top of each page.
Introduction to the NonStop SQL/MX Report Writer The report contains: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.
Introduction to the NonStop SQL/MX Report Writer Customized Reports Figure 1-3. Commands to Produce A Formatted Report >>MODE REPORT; >> SET LAYOUT RIGHT_MARGIN 60; >> SELECT P.PARTNUM, PARTDESC, PRICE, UNIT_PRICE, +> QTY_ORDERED, ORDERNUM +> FROM PARTS P, ODETAIL O +> WHERE P.PARTNUM = O.PARTNUM +> AND ( P.PARTNUM < 300 OR P.PARTNUM >= 6500 ) +> ORDER BY P.PARTNUM, ORDERNUM ; S> DETAIL P.PARTNUM HEADING "Part No.", +> UNIT_PRICE HEADING "Unit Price", +> UNIT_PRICE*QTY_ORDERED AS F11.
Introduction to the NonStop SQL/MX Report Writer Customized Reports Figure 1-4. Formatted Report, page 1 Summary of Orders 09/25/03 Report Author and Version: Tom Jones, v. 2 Part No. Unit Price PC SILVER, 20 MB 212 2450.00 2500.00 Total Price (2) Order No. (3) (4) 29400.00 20000.00 * (5) 400410 500450 49400.00 Suggested Price: (1) (6) 2500.00 (7) PC GOLD, 30 MB 244 3500.00 3500.00 3500.00 2800.00 3000.00 3000.00 * 10500.00 14000.00 28000.00 56000.00 18000.00 18000.
Introduction to the NonStop SQL/MX Report Writer Report Writer Components Figure 1-5. Formatted Report, page 2 Summary of Orders 09/25/03 Unit Price Part No. Total Price Order No. 950.00 760.00 2090.00 100250 700510 800660 (1) (3) DISK CONTROLLER 95.00 95.00 95.00 6500 * 3800.00 Suggested Price: 95.00 SMART MODEM, 1200 1925.00 1375.00 1650.00 275.00 275.00 275.00 7102 * 101220 700510 800660 4950.00 275.00 Suggested Price: SMART MODEM, 2400 7301 425.00 415.00 425.00 425.00 3400.
Introduction to the NonStop SQL/MX Report Writer • • • • Print Items and Logical Lines Clauses Style options Layout options Report functions This subsection provides a summary of the report writer components. For a detailed description of these components, see the SQL/MX Reference Manual. Print Items and Logical Lines Print items identify the items to print in the titles, footings, and detail lines of your report and are optionally accompanied by formatting instructions.
Introduction to the NonStop SQL/MX Report Writer Column number Report Formatting Commands Specifies the column in a numeric position of the select list The first item in the select list is COL 1. Use this form to refer to literals and expressions. Alias name A name defined in a NAME command Use an alias name to refer to a name you assign to a literal or expression.
Introduction to the NonStop SQL/MX Report Writer Report Formatting Command Clauses Table 1-1. Report Formatting Commands (page 2 of 2) Command What the Command Defines REPORT FOOTING Text to print at the end of the report REPORT TITLE Text to print at the start of the report SUBTOTAL Which items to subtotal and when to calculate the subtotal TOTAL Which items to total Report Formatting Command Clauses Clauses in report formatting commands specify display formats for print items.
Introduction to the NonStop SQL/MX Report Writer Layout Options Table 1-3.
Introduction to the NonStop SQL/MX Report Writer Report Functions Report Functions Report functions provide timestamps (both current and for a specified date and time), the current line number, and the current page number. The report functions are summarized in Table 1-5. Table 1-5.
Introduction to the NonStop SQL/MX Report Writer HP NonStop SQL/MX Report Writer Guide—527194-002 1-18 MXCI Commands
2 Using MXCI and the Report Writer Setting the Default Directory 2-1 Starting and Ending an MXCI Session 2-2 Setting Up Your Session Environment 2-2 The Elements of Your Session Environment 2-3 Invoking Report Writer Mode 2-4 Setting and Displaying Options 2-5 Entering Report Formatting Commands 2-10 Executing a Command Repeatedly 2-13 Defining Reports in Command Files 2-15 You can control the environment of a report writing session by using general MXCI commands.
Using MXCI and the Report Writer Starting and Ending an MXCI Session Starting and Ending an MXCI Session To start an MXCI session, at the OSS shell prompt, enter: /home/sql:mxci Hewlett-Packard NonStop(TM) SQL/MX Conversational Interface 2.0 (c) Copyright 2003 Hewlett-Packard Development Company, LP. >> When waiting for a command, MXCI displays the standard MXCI prompt (>>).
Using MXCI and the Report Writer The Elements of Your Session Environment The catalog and schema you have set are in effect until the end of your session or until you execute another SET CATALOG or SET SCHEMA command. Note. All examples in this guide assume that you have already set the correct names for the catalog and schema, as appropriate, at the beginning of your session. The Elements of Your Session Environment CURRENT DIRECTORY Path name of the current server directory.
Using MXCI and the Report Writer Invoking Report Writer Mode Use the ENV command to display the current default settings, as shown: >> env; ---------------------------------Current Environment ---------------------------------CURRENT DIRECTORY /usr/yourdir/bin HOME DIRECTORY /usr/yourdir LIST_COUNT 4294967295 LOG FILE MESSAGEFILE /usr/yourdir/bin/mxcierrors.cat TERMINAL CHARSET ISO88591 MESSAGEFILE LANG US English MESSAGEFILE VRSN {2003-09-19 09:43 NSK:FIGARO/SUPER.
Using MXCI and the Report Writer Setting and Displaying Options Setting and Displaying Options There are three basic commands for working with options: SET, RESET, and SHOW. There are two types of options you can use to customize your reports: • • Layout options, such as line spacing and margin settings, control the appearance of a report on the screen or printed page. Style options, such as the decimal character and underline character, control the appearance of items in a report.
Using MXCI and the Report Writer Listing Rows of a Report You can use the RESET, RESET LAYOUT, and RESET STYLE commands to reset specific options or all options to their default values. For example, to reset the line spacing to 1 (single spacing) and reset all style options to their default values, enter: >> RESET LAYOUT LINE_SPACING; >> RESET STYLE * ; You can enter SET, RESET, and SHOW commands at the standard MXCI prompt (>>) or the select-in-progress prompt (S>).
Using MXCI and the Report Writer Canceling a SELECT Command Figure 2-2.
Using MXCI and the Report Writer Defining a Window for Report Output Defining a Window for Report Output If your report is wider than the maximum width of the device on which you are displaying or printing it, the device determines at what point an output item will wrap to the next line. Use the SET LAYOUT WINDOW command to specify which vertical portion of the report you want to see. For example, suppose that a report is designed for a printer that prints 120 single-byte characters per line.
Using MXCI and the Report Writer Defining a Window for Report Output At the select-in-progress prompt only, you can define a window by the column name or column number of the left most select item you want to display. For example, to display the fourth column of the select list, enter: S> SET LAYOUT WINDOW COL 4; Figure 2-4 illustrates the relation of the left edge of a window to the output line of a report. At each numbered step, the headings and first row are displayed.
Using MXCI and the Report Writer Entering Report Formatting Commands Figure 2-4. Relation of Window to Output Line 1 Default left edge of window is TAB 1 (First position of output line is at left edge of window.) (Screen Position 64) (Left margin of 5 print positions is part of output line.) DEPTNUM DEPTNAME 1000 MANAGER 23 FINANCE LOCATION EMPNUM CHICAGO 23 2 SET LAYOUT WINDOW TAB 15 (15th position of output line moves to left edge of window.
Using MXCI and the Report Writer Entering Report Formatting Commands Figure 2-5. Report Formatting Commands >> SELECT * FROM DEPT, EMPLOYEE +> WHERE DEPT.DEPTNUM = EMPLOYEE.DEPTNUM +> ORDER BY DEPT.DEPTNUM, EMPNUM; S> DETAIL DEPT.DEPTNUM, EMPNUM, LAST_NAME; S> BREAK ON DEPT.DEPTNUM; S> BREAK TITLE DEPT.
Using MXCI and the Report Writer Entering Report Formatting Commands You can enter only one version of each of these commands at one time: • • • • • • • BREAK ON DETAIL PAGE FOOTING PAGE TITLE REPORT FOOTING REPORT TITLE TOTAL If you enter a second version of any of these commands, that version replaces the previous one. A report formatting command is canceled if you do any of the following: • • • Cancel the current SELECT command. Enter LIST ALL. Reset the command using RESET REPORT.
Using MXCI and the Report Writer Specifying Log Files However, if you enter the SUBTOTAL COL 2 command, only column 2 will be subtotaled: S> BREAK ON DEPTNUM, JOBCODE; S> SUBTOTAL COL 1; . . S> SUBTOTAL COL 2; Suppose you enter these commands: S> BREAK ON DEPTNUM; S> BREAK TITLE DEPTNUM ("Department Number:", DEPTNUM); If you decide you want to change the break title, reenter the command: S> BREAK TITLE DEPTNUM ("Dept. No. ", DEPTNUM); The second BREAK TITLE command replaces the first.
Using MXCI and the Report Writer • • Repeating Stored Commands Use the PREPARE and EXECUTE commands. (For an example, see Preparing a SELECT Command on page 3-25.) Use the OBEY command. To execute a set of commands repeatedly, use a text editor to put the commands in a command file. For more information on examples of creating command files, see Defining Reports in Command Files on page 2-15.
Using MXCI and the Report Writer Defining Reports in Command Files Figure 2-6. Repeating Stored Commands Example 1 >> FC SELECT >> SELECT PARTNUM, QTY_AVAILABLE .. I, PRICE >> SELECT PARTNUM, QTY_AVAILABLE, PRICE .. Insert change and press RETURN. Press RETURN. +> FROM PARTS; Press RETURN. .. S> Example 2 Request History. >> HISTORY 7 MXCI displays 3 > SELECT PARTNUM, QTY_ AVAILABLE FROM PARTS; most recent commands.
Using MXCI and the Report Writer Creating a Command File With a Text Editor Creating a Command File With a Text Editor You can use a text editor to create a command file that contains all the commands needed to define a report. Note these considerations: • • The first command must set mode to REPORT. The SELECT command must precede the report formatting commands.
3 Selecting Data for a Report Data Selection 3-1 Developing a Query 3-2 Joining Tables 3-4 Comparing a Set of Columns to a Set of Values 3-10 Comparing Character Values 3-11 Grouping Data for Calculations 3-16 Using Expressions to Calculate Report Values 3-22 Preparing a SELECT Command 3-25 Using Views 3-26 Using Subqueries 3-28 Developing Multistep Queries 3-32 Before you can define a report, you must retrieve the data by entering a SELECT command.
Selecting Data for a Report • • Developing a Query Preparing SELECT commands to be executed more than once Creating views to simplify the specification of queries, save time, and make data access and reporting easier for nontechnical users of the database Developing a Query Before you develop a query to select data, consider these questions: • • • • What columns of information do you need and how should the columns be arranged? In what order should the rows appear? What subtotals and totals are to be
Locating the Data Selecting Data for a Report Figure 3-1. Plan for Report Content Supplier Parts Summary Date: date Part Number 1 Time: time Available Units Unit Cost (dollars) 3 2 Total Cost (dollars) Estimated Profit 4 5 suppnum, suppname, city, state nnnn nnnn . . nnnn nnnn nnnn . . nnnn nnnn.nn nnnn.nn . . nnnn.nn nnnnnnn.nn nnnnnnn.nn nnnnnnn.nn nnnnnnn.nn . . . . nnnnnnn.nn nnnnnnn.nn nnnnnnnnn.nn nnnnnnnn.nn nnnnnnn.nn nnnnnnn.nn nnnnnnn.nn nnnnnnn.
Selecting Data for a Report Joining Tables The FROM clause of the SELECT command specifies the names of all tables from which the SELECT command retrieves data. For example, to retrieve values from the PARTS table, enter: >> SELECT * FROM PARTS; An asterisk in the select list retrieves a value for each column of the table. The table you specify must be on the current default catalog and schema.
Selecting Columns of Source Data Selecting Data for a Report Figure 3-2. Sample Rows from Joined Tables PARTSUPP Table PARTS Table PARTNUM 212 212 4102 4102 4102 QTY_AVAILABLE PC S . . PC S . . DISK . . DISK . . DISK . . 2500.00 2500.00 28.00 28.00 28.00 PARTCOST PARTNUM PARTDESC PRICE 3525 3525 6540 6540 6540 QTY_RECEIVED SUPPNUM 212 212 4102 4102 4102 1 3 6 8 15 2000.00 1900.00 20.00 19.00 21.00 20 35 115 140 30 VST0302.
Selecting Data for a Report Setting Criteria for Selecting Data The select list must specify each column of data used in the report. For example, a column value you print in a title or footing does not have to appear in the detail line, but it must be retrieved in the select list. You select specific columns by specifying column names. The select list for the supplier parts summary consists of the column names in boldface type: >> SELECT S.SUPPNUM, +> SUPPNAME, +> CITY, +> STATE, +> P.
Selecting Data for a Report Setting Criteria for Selecting Data To select only rows describing parts with at least 50 units available, enter: >> SELECT * +> FROM PARTS +> WHERE QTY_AVAILABLE >= 50; In the next example, the WHERE clause specifies conditions for selecting rows and specifies the method for joining three tables. Only information about local suppliers is selected; that is, suppliers in areas with postal codes between 95400 and 95500. >> +> +> +> +> +> +> +> SELECT S.SUPPNUM, SUPPNAME, P.
Selecting Data for a Report Setting Criteria for Selecting Data Table 3-1. Search Condition Predicates Predicate Purpose Comparison Compares values of two expressions, two sets of expressions, or the value of an expression and a single value resulting from a subquery. For example: PARTNUM = 244 (Part number must equal 244.) Quantified Compares the value of an expression to all or any of the values of a single column result from a subquery.
Selecting Data for a Report Setting Criteria for Selecting Data These examples illustrate the effect of each predicate when included in the SELECT command: >> +> +> +> +> +> +> • SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE FROM SAMDBCAT.SALES.PARTS P, SAMDBCAT.INVENT.PARTSUPP PS, SAMDBCAT.INVENT.SUPPLIER S WHERE P.PARTNUM = PS.PARTNUM AND PS.SUPPNUM = S.SUPPNUM ( Substitute predicate example from following text.
Selecting Data for a Report Comparing a Set of Columns to a Set of Values In the next example, the subquery (shown in boldface type) selects from the PARTLOC table part numbers for parts with greater than 500 units on hand in a single location. The main query selects the part description and price for these parts from the PARTS table. >> +> +> +> +> S> SELECT PARTNUM, PARTDESC, PRICE FROM SAMDBCAT.SALES.PARTS WHERE PARTNUM IN (SELECT PARTNUM FROM SAMDBCAT.INVENT.
Selecting Data for a Report Comparing Character Values This query selects employee numbers and job codes for all employees whose names are between CLARK, LARRY and FOLEY, MARK: >> +> +> +> SELECT EMPNUM, JOBCODE FROM EMPLOYEE WHERE LAST_NAME, FIRST_NAME BETWEEN 'CLARK', 'LARRY' AND 'FOLEY', 'MARK'; The names CLARK, JUNE and FOLEY, MAVA would not be selected.
Selecting Data for a Report Comparing Character Values Use the LIKE predicate to search for similar values by specifying only a few characters and using these wild-card characters: % (percent sign) Indicates zero or more characters of any type are acceptable. _ (underscore) Indicates any single character is acceptable. Character data can be stored in columns of data type CHAR, PIC X, VARCHAR, NATIONAL CHAR, and NCHAR.
Selecting Data for a Report Comparing Character Values Table 3-2.
Selecting Data for a Report Specifying More Than One Condition Specifying More Than One Condition You can use the Boolean operators NOT, AND, and OR to select data that satisfies more than one condition: >> +> +> +> +> SELECT PARTNUM, PARTDESC FROM PARTS WHERE QTY_AVAILABLE < 2500 AND PARTNUM BETWEEN 2000 AND 3000 OR PARTNUM > 6000 ; The order of evaluation is: 1. Expressions within parentheses 2. NOT 3. AND 4.
Selecting Data for a Report Sorting the Data Sorting the Data The ORDER BY clause of the SELECT command determines the order in which rows appear in the report. For example, the rows selected by the query in Figure 3-3 are displayed in descending order by quantity available. Figure 3-3. Rows Sorted by One Column >> SELECT * FROM SALES .
Selecting Data for a Report Grouping Data for Calculations Figure 3-4. Rows Sorted by Two Columns >> SELECT * FROM SALES.ODETAIL +> WHERE QTY_ORDERED > 30 +> ORDER BY PARTNUM, QTY_ORDERED DESC; S> LIST ALL; ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED 1000.00 1000.00 1900.00 1900.00 240.00 500.00 700.00 425.00 415.00 60 36 65 40 48 70 36 40 36 600480 2001 400410 2001 700410 2003 600480 2003 400410 6301 400410 6400 800660 6401 600480 7301 400410 7301 --- 15 row(s) selected. VST0304.
Selecting Data for a Report Grouping Data for Calculations The GROUP BY clause determines the rows to which the function is applied. Each group of rows with the same part number is processed to determine a sum.
Selecting Data for a Report Counting Rows the select list and to the GROUP BY clause. The same rows will be grouped. You must qualify the PARTNUM column or the reference will be ambiguous. >> SELECT O.PARTNUM, PARTDESC, SUM (QTY_ORDERED) +> FROM ODETAIL O, PARTS P +> WHERE O.PARTNUM = P.PARTNUM +> GROUP BY O.
Selecting Data for a Report Determining Minimum and Maximum Values CUSTNUM CUSTNAME (EXPR) ------- ------------------ -------------------21 CENTRAL UNIVERSITY 1 123 BROWN MEDICAL CO 2 143 STEVENS SUPPLY 1 You can also select and display only the grouped rows that have more than two orders by moving the COUNT function to the WHERE clause as shown: >> MODE REPORT; >> SELECT C.CUSTNUM, CUSTNAME +> FROM CUSTOMER C, ORDERS O +> WHERE C.CUSTNUM = O.CUSTNUM +> AND COUNT (DISTINCT ORDERNUM) >= 2 +> GROUP BY C.
Selecting Data for a Report Determining Which Columns to Group Determining Which Columns to Group By specifying different sets of columns in the GROUP BY clause, you change the results of functions you apply to the group. For example: • This example counts employees in departments numbered less than 2000. Department 1000 has five employees.
Selecting Data for a Report Selecting Distinct Rows The group to which the function is applied is determined by all the grouping columns you specify. The last column you specify determines how precisely the groups are divided. Note. You do not have to specify an ORDER BY clause unless you want the groups arranged in a particular order or you want to specify break groups in the report. For more information, see Organizing Rows Into Break Groups on page 4-15.
Selecting Data for a Report Using Expressions to Calculate Report Values a part, more than one row of the PARTSUPP might describe the same part number and supplier.
Selecting Data for a Report Using Expressions to Calculate Report Values Figure 3-5. Expressions in the Select List >> SELECT P.PARTNUM, +> PARTCOST, +> PARTCOST * QTY_AVAILABLE, +> +> +> +> QTY_AVAILABLE * (PRICE - PARTCOST) FROM SAMDBCAT.SALES.PARTS P, SAMDBCAT.INVENT.PARTSUPP PS, SAMDBCAT.INVENT.SUPPLIER S +> WHERE P.PARTNUM = PS.PARTNUM AND PS.SUPPNUM = S.SUPPNUM; S> LIST FIRST 1; PARTNUM 212 PARTCOST 2000.00 (EXPR) 7050000.00 (EXPR) 1762500.00 VST0305.
Selecting Data for a Report Using Parameters With SELECT Commands Figure 3-6. Expressions in the Detail Line >> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE +> FROM SAMDBCAT.SALES.PARTS P, +> SAMDBCAT.INVENT.PARTSUPP PS, +> SAMDBCAT.INVENT.SUPPLIER S +> WHERE P.PARTNUM = PS.PARTNUM AND PS.SUPPNUM = S.SUPPNUM; S> DETAIL P.PARTNUM HEADING "Part No.
Selecting Data for a Report Preparing a SELECT Command Named parameters can be useful in a report definition that you specify in an EDIT file and execute by using the OBEY command. To set the value, an operator enters a command before executing the command file. For example: >> SET PARAM ?CUSTNO 324 ; You can specify the value for a parameter as a numeric or string literal, or you can use the CURRENT_TIMESTAMP or COMPUTE_TIMESTAMP functions to calculate the value.
Selecting Data for a Report Using Views This SELPREP file contains this PREPARE command: PREPARE SELCUSTINFO FROM ' SELECT * FROM CUSTOMER C, ' ' ORDERS R, ' ' ODETAIL OD, ' ' PARTS P ' ' WHERE C.CUSTNUM = R.CUSTNUM ' AND R.ORDERNUM = OD.ORDERNUM ' AND OD.PARTNUM = P.PARTNUM ' AND C.CUSTNUM = ? ' ' AND SALESREP = ?'; ' ' ' The SELECT command contains two unnamed parameters to be given values through an EXECUTE command.
Selecting Data for a Report Using Views information. The view columns are specified in parentheses following the view name. The rest of the command specifies the selection of columns and rows for the view. >> CREATE VIEW CUSTORD +> ( CUSTNUM, CUSTNAME, +> STREET, CITY, STATE, POSTCODE, +> CREDIT, +> ORDERNUM, ORDER_DATE, DELIV_DATE, +> SALESREP, +> PARTNUM, UNIT_PRICE, QTY_ORDERED ) +> AS SELECT +> C.CUSTNUM, CUSTNAME, +> STREET, CITY, STATE, POSTCODE, +> CREDIT, +> O.
Selecting Data for a Report Using Subqueries To print an invoice, enter: >> >> +> S> SET LIST_COUNT 0; SELECT * FROM RWVIEWS.CUSTORD WHERE CUSTNUM = 1234 AND ORDERNUM = 100210; OBEY INVOICE; Figure 3-7 shows a sample invoice (line numbers do not appear on the printed report). Figure 3-7. An Invoice INVOICE Customer : DATASPEED 300 SAN GABRIEL WAY NEW YORK, NEW YORK 10014 Part No. 244 2001 2403 5100 Unit Price Quantity 3500.00 1100.00 620.00 150.00 3 3 6 10 Order Date : 870410 Deliv.
Selecting Data for a Report Using Subqueries A correlated subquery is evaluated for each row selected by the main query. A subquery that does not contain a correlated reference is evaluated once. The result is used for evaluating the WHERE clause against each row selected by the main query.
Selecting Data for a Report Using Subqueries The subquery is evaluated for each row selected by the main query. The FROM clause of the main query defines the correlation name XP for the PARTSUPP table. The subquery defines the correlation name P for the PARTSUPP table. The WHERE clause defines the correlation. The subquery averages rows from the PARTSUPP table with a PARTNUM value equal to the PARTNUM value of the current row from the outer query.
Selecting Data for a Report Using Subqueries ORDERNUM, QTY_AVAILABLE, QTY_ORDERED FROM PARTS, ODETAIL X WHERE PARTS.PARTNUM = X.PARTNUM AND (?PERCENT/100) * QTY_AVAILABLE < ( SELECT SUM(QTY_ORDERED) FROM ODETAIL WHERE X.PARTNUM = ODETAIL.PARTNUM) ORDER BY X.PARTNUM; DETAIL PARTNUM AS I4 HEADING 'PART', PARTDESC, QTY_AVAILABLE AS I6 HEADING 'AVAILABLE', ORDERNUM AS I6 HEADING 'ORDER NO.
Selecting Data for a Report Developing Multistep Queries Figure 3-8. A Report With a Subquery PART 2001 PARTDESC AVAILABLE ORDER NO. 2100 100210 200300 400410 500450 600480 800660 GRAPHIC PRINTER,M1 STREAMING TAPE,M20 3 10 36 16 60 30 155 * 6400 ORDERED 2100 200320 300350 400410 800660 * 7 5 70 30 112 VST0308.vsd When using subqueries, consider the following points : • • • • • A subquery must be enclosed in parentheses.
Selecting Data for a Report Multilevel Group Aggregates Multilevel Group Aggregates Grouping Data for Calculations on page 3-16 describes ways to apply aggregate functions to groups of rows. To apply aggregate functions to multiple levels of groups, you must specify more than one query and use temporary tables. For example, to report the average salary for each department and within each department for each job classification: 1.
Selecting Data for a Report Conditional Aggregates DEPTNUM DEPT_AVGSAL JOBCODE JOB_AVGSAL ------- ----------- ------- ---------1000 52000 100 137000 500 34666 900 19000 1500 41250 100 90000 600 29000 900 17000 2000 50000 100 13800 200 24000 S> The BREAK ON command suppresses printing of the same department number and salary average in multiple lines. You can drop the tables or purge the data and reuse the tables in future reports.
Selecting Data for a Report Row Value as Percent of All Row Values 4. Select the report information from the DEPTTEMP table: >> +> +> S> +> +> +> S> SELECT DEPTNUM, SUM(RANGE1), FROM DEPTTEMP GROUP BY DEPTNUM; DETAIL DEPTNUM, COL 2 AS I12 HEADING 'SAL. < COL 3 AS I12 HEADING 'SAL. < COL 4 AS I12 HEADING 'SAL. < LIST ALL; SUM(RANGE2), SUM(RANGE3) 20000', 50000', 200000'; DEPTNUM SAL. < 20000 SAL. < 50000 SAL. < 200000 ------- ------------ ------------ ------------1000 1 3 5 1500 1 3 4 2000 0 4 5 . . .
Selecting Data for a Report +> +> S> +> +> S> Row Value as Percent of All Row Values FROM EMPLOYEE E, AVGTEMP A WHERE E.DEPTNUM = A.DEPTNUM; DETAIL DEPTNUM, EMPNUM, LAST_NAME, SALARY AS F10.2, COL 5 AS F10.2 HEADING 'PCT OF AVG'; LIST ALL; DEPTNUM EMPNUM LAST_NAME SALARY PCT OF AVG ------- ------ ---------------- ---------- ---------1000 23 HOWARD 137000.10 263.46 1000 202 CLARK 25000.75 48.08 1000 208 CRAMER 19000.00 36.54 . . . . . . . . . . . . . . . 9000 1 GREEN 175500.00 165.
4 Customizing a Report Setting Margins 4-2 Paginating 4-6 Spacing Items and Lines 4-10 Specifying the Items in a Detail Line 4-12 Naming Select List and Detail Line Items 4-14 Organizing Rows Into Break Groups 4-15 Specifying Column Headings 4-17 Specifying Titles 4-20 Specifying Footings 4-24 Formatting Data Values 4-30 Conditional Printing of Items or Line Entries 4-46 Redefining Special Characters 4-48 Calculating Totals 4-49 Printing Double-Byte Characters 4-58 You can use
Customizing a Report Defining the Layout Defining the Layout You control the layout of a report by specifying margins, pagination, and spacing. Setting Margins The current settings of the layout options LEFT_MARGIN and RIGHT_MARGIN determine the margins of a report. If you want a margin to precede the leftmost printed item in your report, set the left margin to the number of blanks needed.
Customizing a Report Setting Margins If you are specifying a detail line, insert a SKIP clause before a print item to force the item to be printed on the next line. Figure 4-1 and Figure 4-2 illustrate the default margin settings for displayed and printed output. Figure 4-1. Default Margin Settings in a Displayed Report Displayed Report Left Margin 0 Print Position 1 Right Margin At Output Device Width VST0401.
Customizing a Report Setting Margins Figure 4-2. Default Margin Settings in a Printer Report Printed Report Left Margin 0 Print Position 1 Right Margin Output Device Width VST0402.vsd The default top and bottom margins are one line each. You can increase the apparent size of these margins with a PAGE TITLE or PAGE FOOTING command. If you have no title, specify PAGE TITLE '' for a top margin of 3 lines or specify PAGE TITLE SKIP desired-margin-size - 3 for more than 3 lines.
Customizing a Report Setting Margins If you specify text in the title line, specify PAGE TITLE SKIP desired-marginsize - 1, ' text'. For example, to produce a desired margin size of 4 lines (SKIP 4 - 1 = SKIP 3), enter: S> PAGE TITLE SKIP 3, 'Summary of Employees'; ( 4 blank lines appear here. ) Summary of Employees CUSTNUM CUSTNAME ------- -------In this case, the four blank lines are created as follows: • • • The first blank line is the default top margin.
Customizing a Report Paginating Paginating The report writer features that control where page breaks occur in a report are: PAGE_LENGTH Specifies the number of lines from the top to the bottom of a page. For example, to set a page length of 55 lines, enter: >> SET LAYOUT PAGE_LENGTH 55; Default page length for a printed report = 60 lines. Default page length on a terminal is ALL. (The entire report is a single page unless you specify the PAGE clause.
Customizing a Report Paginating You can specify the maximum number of pages to be printed in a report by setting the PAGE_COUNT layout option. For example, to set the limit to 100 pages, enter: >> SET LAYOUT PAGE_COUNT 100; The default PAGE_COUNT value is ALL. The entire report is printed or displayed. To number the pages in a report, use the PAGE_NUMBER function to retrieve the number of the current page. The first page of the report is numbered 1.
Customizing a Report Paginating Figure 4-3. Pagination Features Department : FINANCE JOBCODE EMPNUM 900 500 208 202 210 214 23 100 SALARY CRAMER, SUE CLARK, LARRY BARTON, RICHARD KELLY, JULIA HOWARD, JERRY 19000.00 25000.75 29000.00 50000.00 137000.10 Location CHICAGO Page - 1 Department : INVENTORY JOBCODE EMPNUM 900 250 321 219 233 230 32 200 100 SALARY WINN, BILL TERRY, DAVID MCDONALD, TED LEWIS, ROCKY RUDOLF, THOMAS 32000.00 27000.12 29000.00 24000.00 138000.
Paginating Customizing a Report Figure 4-4. Result of Using the NEED Clause 1 Supplier No. 2 8 ATTRACTIVE CORP 3 7777 FOUNTAIN WAY 4 CHICAGO, ILLINOIS 60610 5 6 Supplier No. 2 7 DATA TERMINAL INC 8 2000 BAKER STREET 9 LAS VEGAS, NEVADA 66134 10 11 12 13 14 1 Supplier No. 2 15 DATADRIVE CORP 3 100 MAC ARTHUR 4 DALLAS, TEXAS 75244 5 6 Supplier No. 3 7 HIGH DENSITY INC 8 7600 EMERSON 9 NEW YORK, NEW YORK 10230 10 S> VST0404.
Spacing Items and Lines Customizing a Report Spacing Items and Lines The report writer features that control spacing of print items and lines are: LINE_SPACING Sets the number of lines to advance between report lines. Default setting = 1 (a single-spaced report) For double spacing, enter: >> SET LAYOUT LINE_SPACING 2; SKIP clause Specifies the number of additional times to invoke LINE_SPACING to create extra space before displaying or printing the next item in a detail line, title, or footing.
Spacing Items and Lines Customizing a Report SPACE clause Specifies a number of spaces to insert before displaying or printing the next item in a detail line, title, or footing. NOTE: This clause temporarily overrides the SPACE option; the clause determines the amount of space between the print items that precede and follow the clause.
Specifying the Items in a Detail Line Customizing a Report Figure 4-5. Tabbing to a Display or Print Position Left Margin 8, First Output Position 9 Tab Position 35 Display Positions 1 2 3 4 5 12345678901234567890123456789012345678901234567890... Location : CHICAGO Dept. No. 1000 Print Positions 1 2 3 4 5 12345678901234567890123456789012345678901234567890... Dept. No. 1000 Location : CHICAGO VST0405.
Customizing a Report Specifying the Items in a Detail Line You can refer to columns LAST_NAME and FIRST_NAME as COL 2 and COL 3: S> DETAIL col2, col3; When you use a DETAIL command, consider these points: • • You can specify only one DETAIL command at a time. You can edit the command by using FC, replace the command by reentering it, or delete the command by entering RESET REPORT DETAIL. In report formatting commands, you can refer to the items of the select list by column name or by column number Note.
Customizing a Report Naming Select List and Detail Line Items +> FIRST_NAME NOHEAD, +> JOBCODE HEADING 'Job Code'; S> LIST FIRST 2; Employee No. Job Code ------------ -------1 GREEN ROGER 100 32 RUDLOFF THOMAS 100 S> For more information about defining headings, see Specifying Column Headings on page 4-17. Naming Select List and Detail Line Items To assign an alias name to items in the select list, use the NAME command.
Customizing a Report +> +> +> +> +> Organizing Rows Into Break Groups PARTCOST HEADING 'Unit Cost', PARTCOST * QTY_AVAILABLE HEADING 'Total Cost', QTY_AVAILABLE * (PRICE - PARTCOST) HEADING 'Profit' NAME PROFIT; To refer to the PROFIT column, enter: S> TOTAL PROFIT; You cannot refer to a detail alias name within the DETAIL command itself. You can only define the name there and use it in succeeding commands.
Customizing a Report Organizing Rows Into Break Groups +> QTY_ORDERED; S> BREAK ON SALESREP, C.CUSTNUM, R.ORDERNUM; S> LIST N 18; The break groups for this report are: • • • Sales representatives form the major break group. The customers of each sales representative form the next level break group. The orders from each customer form the last break group. A title and footing can be printed at each level. Figure 4-6 shows the resulting report. Figure 4-6.
Customizing a Report • • Labeling Information You can specify a break column that is not in the detail line. The break occurs, but the item is not printed. For example, you might want to print the break column value in the break title but not in the detail line. If the break column is an item in the detail line: ° SUPPRESS ensures that the column value appears only in the first row of the group. If a page break occurs in the middle of the group, the value appears again in the first row of the new page.
Customizing a Report Headings for Column Identifier Print Items 2. HEADING or NOHEAD clauses of the DETAIL command control headings for print items. You can specify a heading with the HEADING option for a print item in the detail list. You can also specify NOHEAD to suppress a heading for a print item. 3. Defined alias or detail names for the report provide headings of affected print items. 4.
Customizing a Report Multiple-Line Headings S> LIST N 3; Employee No. Name Job Title ------------ ------------------------- -----------------65 87 104 RACHEL MCKAY ERIC BROWN DAVID STRAND MANAGER SYSTEM ANALYST SYSTEM ANALYST S> The previous detail line illustrates these important points: • • If the width of a heading is greater than the width of the print item value, the heading determines the field size. A numeric value is right-justified; a character value is left-justified.
Customizing a Report Specifying Titles +> PARTCOST * QTY_AVAILABLE +> HEADING 'Total Cost/(dollars)' CENTER; S> LIST N 1; Part Available Unit Cost Total Cost Number Units (dollars) (dollars) ------ ----------- ------------ -------------------212 3525 2000.00 7050000.00 S> To specify a different new-line character, enter: >> SET STYLE NEWLINE_CHAR '!'; The first line of the DETAIL line must then be changed: S> DETAIL P.
Customizing a Report Page and Report Titles Figure 4-7. Report and Page Titles Supplier Parts Summary Date : 04/25/87 Author : Sarah Verdi ( The body of the report begins here.) VST0407.vsd A blank line follows the page title and the report title. The report title appears only on the first page of the report.
Customizing a Report Break Titles Figure 4-8. A Page Title With a Row Value Customer Delivery Summary CUSTNUM 21 123 Cust. No. CUSTNAME CENTRAL UNIVERSITY BROWN MEDICAL CO STATE 21 DELIV_DATE PENNSYLVANIA CALIFORNIA 870720 871101 870820 (Page Break) Customer Delivery Summary CUSTNUM 143 324 543 CUSTNAME STEVENS SUPPLY PREMIER INSURANCE FRESNO STATE BANK Cust. No.
Customizing a Report Break Titles You can delete the break title associated with each specified break column by using the RESET REPORT BREAK TITLE ( column-list) command. To include an unnamed item from the detail line in a break title, you must define a detail alias name for the item. In this example, a report generates break titles for two break groups—one for each customer and one for each order. The CUSTNUM value in the first title is taken from the first row of each customer break group.
Customizing a Report Specifying Footings Figure 4-9. Break Titles Part No. Quantity CUSTNUM Break Title Customer No. 21 CENTRAL UNIVERSITY ORDERNUM Break Title Order No. 200320 5504 6201 6301 6400 5 16 6 7 Customer No. 123 BROWN MEDICAL CO Order No. 200490 Order No. 300380 3210 5505 1 1 244 2402 6 12 VST0409.vsd Specifying Footings A report can contain three types of footings: • • • A page footing appears at the bottom of each page of a report.
Customizing a Report Page and Report Footings Figure 4-10. Location of Report and Page Footings . . (The body of the report ends here.) End of Summary Page 3 VST0410.vsd A blank line separates the body of the report from the page footing. On the last page, the report footing is separated from the body of the report by a blank line. The other pages of the report do not contain the report footing.
Customizing a Report Break Footings Figure 4-11. A Page Footing 21 CENTRAL UNIVERSITY UNIVERSITY WAY PHILADELPHIA, PENNSYLVANIA 123 BROWN MEDICAL CO . . . . . . 7777 SLEEPWELL HOTELS 9000 PETERS AVENUE DALLAS, TEXAS Customer 7777 Page 4 VST0411.vsd Break Footings You can specify a break footing for each break group. You can have more than one BREAK FOOTING command in effect at a time, but each command must relate to a different break column.
Customizing a Report +> +> S> S> +> +> +> S> +> +> +> +> +> S> Line Numbers PARTNUM HEADING 'Part No.', QTY_ORDERED HEADING 'Quantity'; BREAK ON C.CUSTNUM, CUSTNAME, R.ORDERNUM; BREAK FOOTING C.CUSTNUM ('Earliest Delivery Date: ', DELIV_DATE AS I6, SKIP 1) ; BREAK FOOTING R.ORDERNUM ('Order', R.ORDERNUM, SPACE 3, 'Salesperson's number: ', SALESREP, SKIP 1) ; LIST NEXT 10; Figure 4-12 shows the report. The DELIV_DATE value in the first footing is taken from the last row of each customer break group.
Customizing a Report Line Numbers The line number is incremented at each detail line. If the detail line is printed as multiple output lines, the number is incremented only once for each set of output lines. The default display format for the LINE_NUMBER value is I11. To number all detail lines in one sequence, include the print item: LINE_NUMBER OVER REPORT or LINE_NUMBER Suppose that you want to number the entries in a listing of parts and the suppliers who supply them.
Customizing a Report Line Numbers Figure 4-13. Numbering Lines Page 1 Parts Supplier List Part 212 1 Part Cost Supplier 2 Part Cost Supplier Part 244 3 Part Cost Supplier 4 Part Cost Supplier Part 5 2000.00 1 NEW COMPUTERS INC 1900.00 3 HIGH DENSITY INC 2400.00 1 NEW COMPUTERS INC 2200.00 1 DATA TERMINAL INC 255 Part Cost Supplier 3300.00 1 NEW COMPUTERS INC VST0413.
Customizing a Report Text Inserted Into a Line Figure 4-14. Numbering Break Group Lines Page 1 Parts Supplier List Part 212 1 Part Cost Supplier 2 Part Cost Supplier Part 244 1 Part Cost Supplier 2 Part Cost Supplier Part 1 2000.00 1 NEW COMPUTERS INC 1900.00 3 HIGH DENSITY INC 2400.00 1 NEW COMPUTERS INC 2200.00 1 DATA TERMINAL INC 255 Part Cost Supplier 3300.00 1 NEW COMPUTERS INC VST0414.
Customizing a Report Formatting Data Values Table 4-1. Default Display Formats (page 1 of 2) SQL Data Type Default Display Format Fixed-length Characters: An (or A255 if n > 255) CHAR(n) PIC X(n) DISPLAY NATIONAL CHAR(n) NCHAR(n) Variable-length Characters: VARCHAR(n) NATIONAL CHARACTER VARYING (n) NCHAR VARYING(n) NUMERIC(1,s) — NUMERIC (4,s) I6 (or F7.s if s > 0) NUMERIC(1,s) UNSIGNED — I5 (or F6.s if s > 0) NUMERIC(4,s) UNSIGNED NUMERIC(5,s) — NUMERIC(9,s) I11 (or F12.
Customizing a Report Using the AS Clause to Modify the Default Report Format Table 4-1. Default Display Formats (page 2 of 2) SQL Data Type Default Display Format n Length vw Current VARCHAR_WIDTH value s Scale of the data type i Integer A, I, F For more information, see Display Format Specifications on page 4-32. E Edit descriptor, explained in the Guardian Programmer's Guide DATE yyyy-mm-dd DATETIME yyyy-mm-dd:hh:mm:ss.msssss TIME hh:mm:ss TIMESTAMP yyyy-mm-dd:hh:mm:ss.
Customizing a Report • Display Format Specifications The essential part of a display format is the display-descriptor. A display descriptor controls the width of a field and, for numeric values, the scale of the value. The display descriptors for character values are: ° A[ w] specifies alphanumeric display of width w. For example, A20 displays characters in a field that is 20 single-byte print positions wide. Values are left justified in this format. Note.
Customizing a Report Numeric Values Numeric Values Use the I and F display descriptors for numeric values. In this example, QTY_RECEIVED appears in the detail line. The data type defined for QTY_RECEIVED is NUMERIC (7); the value is always an integer. By default, the display format is I11. The DETAIL command specifies a narrower field.
Customizing a Report Monetary Values You cannot specify a scale-sign descriptor for an I display descriptor. >> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST +> FROM SAMDBCAT.SALES.PARTS P, +> SAMDBCAT.INVENT.PARTSUPP PS, +> WHERE P.PARTNUM = PS.PARTNUM +> ORDER BY P.PARTNUM; S> DETAIL PARTNUM HEADING 'Part No.', +> PARTCOST AS F10.2 HEADING 'Part Cost/(dollars)', +> PARTCOST * QTY_AVAILABLE AS '-3P F10.2' +> HEADING 'Total Cost/(thousands/of dollars)', +> PARTCOST * QTY_AVAILABLE AS F15.
Customizing a Report Monetary Values code within each department and to produce subtotals and totals, the mask must be modified to accommodate larger numbers in the detail line and large numbers in the subtotal and total lines. >> +> +> +> S> +> +> +> S> S> S> S> SELECT DEPTNUM, JOBCODE, SUM(SALARY) FROM EMPLOYEE ORDER BY DEPTNUM GROUP BY DEPTNUM, JOBCODE; DETAIL DEPTNUM AS I6 HEADING 'Dept.', JOBCODE AS I4 HEADING 'Job', COL 3 AS M<$ZZZ,ZZZ,ZZZ.
Customizing a Report +> S> +> +> S> Monetary Values Job GROUP BY JOBCODE; DETAIL JOBCODE AS I4 HEADING 'Job', COL 2 AS '[PF''$''] M' HEADING 'Avg. Sal.'; LIST NEXT 5; Avg. Sal. ---- ----------100 200 250 300 400 S> $105,954.59 $24,000.00 $28,000.06 $31,123.05 $77,400.00 Figure 4-16 interprets the display format specified for COL 2 in the previous query. The form of decoration is: condition location char-string The entire display format must be enclosed in quotation marks.
Customizing a Report • • • Suppressing Leading or Trailing Zeros In the first decoration, condition M specifies that this decoration applies if the value is negative. location A1 specifies print position 1 for printing charstring CR. In the second decoration, condition MP specifies that this decoration applies if the value is negative or positive. location and char-string are the same as those shown in Figure 4-16.
Customizing a Report Concatenating Text Use the uppercase letter Z to suppress leading and trailing zeros in a mask: Mask Value Result M 2.453 2.45 9023.00 9023.00 5432 5,432 300 0,300 0 0,000 M 300 300 M<$ZZZ9.99> 5432 $5432.00 300.153 $ 300.15 0 $ 0.00 M<9,999> You can use the BZ modifier to suppress all zeros when the value is zero; for example, AS ’[BZ] M’.
Customizing a Report Truncated Values To specify the width of the field, include an AS clause in the print item: CONCAT (FIRST_NAME STRIP, SPACE 1, LAST_NAME) AS A25 Truncated Values The VARCHAR_WIDTH style option specifies the maximum number of characters that can appear in a print item with a value of a variable-length character data type. The excess characters are truncated. The default is 80 single-byte characters. You can set a value up to 255 single-byte (or 177 double-byte) characters.
Customizing a Report • Filler Characters ° A value in an A display descriptor field does not fill the field. For example, ’[FL''*''] A12’ produces FINANCE*****. ° ° Leading zeros are to be replaced. For example, ’[FL''*'']I6’ produces ***355. Embedded text in a mask descriptor is not printed because the digits that surround the text are not printed. For example, ’[FL''*'']M<$ZZZZ9.99>’ produces $**355.67.
Customizing a Report Formatting Dates and Times Formatting Dates and Times To format a date and time in your report, you must consider the data type of the date and time value to be formatted. The data type is determined by the way the value is generated or stored. Date and Time Values A report can contain dates and times selected from columns of the DATETIME, DATE, TIME, or TIMESTAMP data types. You can also use date-time literals or expressions to generate dates and times for a report.
Customizing a Report Julian Timestamp Formats You might also store a date as a numeric value but not as a timestamp. See Formats for Dates Stored as Binary Values on page 4-46. Julian Timestamp Formats By using the AS DATE/TIME clause, you can specify the date format and time format of a Julian timestamp.
Customizing a Report SQL/MX Date and Time Formats Table 4-2 summarizes the characters you use to specify date and time formats in an AS DATE/TIME clause, a DATE_FORMAT style option, or a TIME_FORMAT style option. Table 4-2.
Customizing a Report Converting Timestamps If you are formatting only a part of the date-time value, the applicable parts of the format are used. For example, if you specify YEAR TO DAY in the DATEFORMAT function, the EUROPEAN format will be dd.mm.yyyy. These print items produce identical formats: CURRENT DATEFORMAT (CURRENT, DEFAULT) A current date and time generated by either of these functions would appear as: 2000-04-15:18:22:05.
Customizing a Report Formats for Dates Stored as Binary Values Note that you do not need to include YEAR TO DAY with CURRENT because the AS DATE clause selects only the month, day, and year from the timestamp.
Customizing a Report Conditional Printing of Items or Line Entries +> ELSE ('Postpone')) +> HEADING 'REVIEW PLAN'; S> LIST NEXT 10; NAME DEPTNAME REVIEW PLAN -------------------- ------------ -----------HERB ALBERT ENGLND SALES Wait 1 month RICHARD BARTON FINANCE Evaluate MARLENE BONNY RESEARCH Evaluate ERIC BROWN RESEARCH Postpone SUSAN CHAPMAN PERSONNEL Evaluate JOHN CHOU ASIA SALES Evaluate DINAH CLARK CORPORATE Wait 1 month LARRY CLARK FINANCE Evaluate MANFRED CONRAD RESEARCH Wait 1 month STEVE COOK R
Customizing a Report Redefining Special Characters RUDLOFF THOMAS Shipping SAFFERT KLAUS Sales S> You can use decorations to specify conditions for printing information within a print item. For a description of decorations, see Monetary Values on page 4-35. For another type of conditional printing, see Filler Characters on page 4-40. Note.
Customizing a Report Calculating Totals To set all style options to their default values, enter: >> RESET STYLE *; Calculating Totals You can calculate totals of numeric print-item values. A total appears at the end of the report. For example, in the following report, you want the total of all parts ordered: >> +> +> S> S> SELECT PARTNUM, SUM (QTY_ORDERED) FROM ODETAIL GROUP BY PARTNUM; TOTAL COL 2; LIST ALL; PARTNUM (EXPR) ------- ------------------212 20 244 47 255 38 . . . . . .
Customizing a Report +> +> +> S> +> +> +> +> S> Calculating Totals AND PS.SUPPNUM = S.SUPPNUM AND P.PARTNUM IN (5100, 5101, 5103) ORDER BY S.SUPPNUM, P.PARTNUM; DETAIL PARTNUM, PARTCOST AS F8.2, PARTCOST * QTY_AVAILABLE NAME TOTAL_COST, QTY_AVAILABLE * (PRICE - PARTCOST) NAME PROFIT; TOTAL TOTAL_COST, PROFIT; LIST ALL; Figure 4-17 shows the resulting report. Figure 4-17. A Report With Totals PARTNUM 5100 5100 5101 5103 5100 5101 5103 PARTCOST TOTAL_COST 100.00 105.00 135.00 265.00 95.00 125.00 250.
Customizing a Report Calculating Subtotals Figure 4-18. Formatted Total Values PARTNUM 5100 5100 5101 5103 5100 5101 5103 PARTCOST TOTAL_COST 100.00 105.00 135.00 265.00 95.00 125.00 250.00 PROFIT 323700.00 339885.00 324000.00 881920.00 307515.00 300000.00 832000.00 161850.00 145665.00 156000.00 449280.00 178035.00 180000.00 499200.00 3309020.00 1770030.00 --- 7 row(s) selected. >> VST0419.vsd The display format you specify must be large enough to contain the result of the TOTAL command.
Customizing a Report +> +> +> +> S> +> +> +> +> +> +> +> +> S> S> +> +> +> +> S> +> S> S> S> +> +> S> S> S> Calculating Subtotals SAMDBCAT.INVENT.SUPPLIER S, WHERE P.PARTNUM = PS.PARTNUM AND PS.SUPPNUM = S.SUPPNUM ORDER BY S.SUPPNUM, P.PARTNUM; DETAIL PARTNUM AS I6 HEADING 'Part/Number' CENTER, QTY_AVAILABLE AS I9 HEADING 'Available/Units' CENTER, PARTCOST AS F11.2 HEADING 'Unit Cost/(dollars)' CENTER, PARTCOST * QTY_AVAILABLE AS F14.
Customizing a Report Calculating Subtotals Figure 4-19. A Report With Subtotals Supplier Parts Summary Date: April 20, 1987 Available Part Number Units Supplier : 1 212 244 255 2001 2002 . 3525 4426 3321 2100 3220 . . 6301 6400 7301 . 2331 1268 2332 Supplier : 244 2001 2003 5110 5504 6401 6500 6603 2 4426 2100 2200 3236 2630 1308 2532 430 Supplier : 3 212 255 6401 6500 3525 3321 1308 2532 Time: 02:06:56 PM Total Cost (dollars) SAN NEW COMPUTERS INC, FRANCISCO, 2000.00 7050000.00 2400.
Customizing a Report Calculating Subtotals Figure 4-20. A Report With Subtotals (Page 2 of 2) Supplier Parts Summary Part Number Supplier : 2002 2405 3210 4102 5100 5504 Supplier : 4102 5100 5101 5103 Available Units 6 3220 2712 3314 6540 3237 2630 8 6540 3237 2400 3328 Unit Cost (dollars) MAGNETICS INC, 1100.00 450.00 470.00 20.00 100.00 75.00 Total Cost (dollars) Estimated Profit LEXINGTON, MASS 3542000.00 1220400.00 1557580.00 130800.00 323700.00 197250.00 1288000.00 935640.00 811930.00 52320.
Customizing a Report • • Defining a Subtotal Label The OVER clause indicates when a subtotal is to be calculated. If you omit the OVER clause, the subtotals appear whenever any data value in any currently defined break column changes. Only one SUBTOTAL command without an OVER clause is in effect at one time. To delete all subtotal commands, enter: S> RESET REPORT SUBTOTAL; RESET REPORT BREAK also resets subtotals by deleting the specified break columns.
Customizing a Report +> +> +> S> S> S> S> Calculating Subtotals on Conditional Values QTY_ORDERED AS I8 HEADING 'QUANTITY', UNIT_PRICE * QTY_ORDERED AS F14.2 HEADING 'TOTAL PRICE' NAME TOTALP ; BREAK ON CUSTNUM, OD.ORDERNUM; SUBTOTAL QTY_ORDERED, TOTALP OVER OD.ORDERNUM; SUBTOTAL TOTALP OVER CUSTNUM; LIST ALL; Figure 4-21. Customer Orders Summary CUSTNUM ORDERNUM PARTNUM PRICE QUANTITY TOTAL PRICE 21 200320 5504 165.00 5 825.00 6201 195.00 16 3120.00 6301 245.00 6 1470.00 6400 540.
Customizing a Report Calculating Subtotals on Conditional Values In this example, a union of three SELECT statements (shown in boldface) retrieves the data for a report. Column 2 of the detail line contains the number of bonus points the sales representative has earned based on the quantity of parts ordered by customers: 10 points for up to 5 units of a specific part, 25 points for 6 to 15 units, and 50 points for more than 15 units.
Customizing a Report Printing Double-Byte Characters Figure 4-22. Subtotals on Conditional Values Sales Representative Bonus Points 220 10 10 10 25 25 25 25 25 25 25 50 50 50 355 * 10 221 25 25 25 85 * 25 222 25 25 * 75 * * * * * * VST0423.vsd Printing Double-Byte Characters SQL/MX currently supports printing of a single byte characters only. A double-byte character printed in a report is represented by a question mark (?).
A Migration from SQL/MP Report Writer to SQL/MX Report Writer This appendix discusses unsupported and changed commands and options, error handling, and the use of single and double quotation marks in SQL/MX report writer. Unsupported Commands, Options, and Functions • • • • • • • • • • • • ADD DEFINE OUT_REPORT RESET REPORT SELECT RESET SESSION SET SESSION WRAP CN[.
Migration from SQL/MP Report Writer to SQL/MX Report Writer • • • • • Error Handling RESET REPORT TOTAL (column-name) allows only one column. Subqueries are not allowed within SQL/MX report writer commands. For logical folding, SQL/MX report writer does not maintain spaces between columns. The next folded column begins at the first print position of the next line, regardless of the SPACE option setting.
Index A A display descriptor 4-33 ADD DEFINE command A-1 Aggregate functions applied conditionally 3-33 groups of rows 3-16 using 3-17 Alias name 4-14 Alphanumeric character comparison 3-11 Ampersand A-1 AND operator 3-14 Arithmetic expression, evaluation of 3-23 Arithmetic operators 3-23 AS clause formatting Julian timestamp 4-43 in DETAIL command 4-34 Julian timestamp data type requirement 4-44 overview 4-32 Averages, computing 3-1 AVG function 3-18 B BETWEEN comparing a set of columns 3-10 one parameter
Index D Columns (continued) omitting from output line 4-13 qualified names for 3-2 selecting data from 3-5 Command file defining report in 2-15 Command file, defining report in 2-15 Commands canceling formatting 2-12 continuation prompt 2-2 entering 2-10 executing again 2-14 executing previous 2-14 repeating 2-14 report formatting 1-14 stored 2-14 Comparison predicate comparing character values 3-11 comparing uppercase characters 3-11 example of 3-7 operators 3-7 rules 3-12 Compiling SELECT command 3-24 C
Index E Decorations display of 4-33 form of 4-37 Defaults catalog, logical name 2-2 data type formats 4-30 date and time formats 4-43 decimal character for numeric print item 4-48 directory 2-1 heading 4-13 margin, bottom 4-4 margin, top 4-4 overflow character 4-33 report format 1-3, 1-6 report format example 1-6 schema,logical name 2-2 spacing 4-10 subtotal label 4-51 Descriptor See Display descriptors Detail alias names 4-49 DETAIL command 4-13 Detail line description of 1-7 expressions in 3-23 items in
Index F F I F display descriptor example 4-34 using 4-34 zero suppression 4-38 FC command 4-55 Field filler characters 4-1, 4-40 Field size 4-19 File See Command file Fitting lines on a page 4-6, 4-21 Fixed-point display 4-33 FL modifier 4-40 Folding a line 2-7 Footings break 1-8, 4-24 page 1-8 Format of data types 4-30 Formatting clauses, summary of 1-14 Formatting commands canceling 2-12 summary of 1-14 FROM clause 3-4 Functions, summary of report 1-17 I display descriptor overview 4-33 using 4-35 ze
Index M Lines (continued) numbering, in a report 4-27 spacing of 4-10 LINE_NUMBER function 4-27 LINE_SPACING, setting 4-10 LIST command abbreviation not allowed A-1 ALL parameter 4-6 LIST NEXT must be typed A-1 options 2-6 using 2-6 LIST_COUNT A-1 Locating data in database 3-2 Log file creating 2-13 creating command file from 2-14 displaying current 2-4 printing reports with 2-13 Logical line description 1-14 size 1-14 Logical tables definition 3-6 views 3-26 LOGICAL_FOLDING layout option spaces between c
Index P Operators arithmetic 3-23 Boolean 3-7 comparison 3-7 Options layout, definition 2-5 setting and displaying 2-5 style, definition 2-5 OR operator 3-7 Order arithmetic evaluation 3-23 condition evaluation 3-14 select list 3-17 ORDER BY clause break groups, relation to 4-15 description of 3-17 example 1-6 row grouping 3-17 Outer query 3-30 OUT_REPORT A-1 Overflow character, single item 4-33 OVERFLOW_CHAR style option 4-40 P Page breaks 4-6 fitting lines on 4-6, 4-21 footing 1-8, 4-6 length 4-6 lengt
Index R R Replacing commands 2-12 REPORT FOOTING command 4-25 Report formatting clauses, summary of 1-15 commands, summary of 1-14 Report functions summary of 1-17 using 3-24 Reports customized 1-8 date and time, current 3-2 defining 2-15 development steps 1-4 footing 1-8, 4-24 formatted 1-8 formatted, example 1-8 functions summary 1-17 layout and style 1-5 printing as LOG file A-1 saving 2-16 subquery used with 3-30 title 1-8 RESET command 2-5 RESET LAYOUT command 2-6 RESET REPORT A-1 RESET REPORT SELECT
Index T SHOW LAYOUT definition 1-17 example 4-2 Single spacing 2-6, 4-10 SKIP clause 4-10 Sorting data 3-1 Source data 3-5 SPACE clause 4-11 SPACE layout option 4-10 Spacing double 4-10 items 4-1 line 4-1 of items and lines 4-11 single 4-10 Special characters redefining 4-48 ! 1-5 $, inserting in value 4-35 Stored commands 2-14 Strings output line, insert in 4-2 spacing 4-10 Style options definition 1-3 summary of 1-15 Subqueries considerations 3-32 IF/THEN/ELSE 4-46 IN predicate 3-9 in report writer comm
Index U U UNDERLINE_CHAR style option 4-48 UNION operator 4-56 Unnamed parameters 3-25 UPSHIFT function 3-11 USA date-time format 4-44 User-defined parameters in SELECT command 3-25 & (ampersand) A-1 * in select list 3-6 *, in select list 3-32 _ (underscore) 3-8 V Values, justification of 4-32 VARCHAR data type, comparison of 3-12 VARCHAR_WIDTH style option 4-40 Views creating 3-26 using 3-2 W WHERE clause 1-7, 3-4 Wild-card characters 3-8 % 3-8 _ (underscore) 3-8 WINDOW layout option, using 2-8 Window
Index Special Characters HP NonStop SQL/MX Report Writer Guide—527194-002 Index-10
