HP NonStop SQL/MP Report Writer Guide Abstract This manual explains how to use the HP NonStop™ SQL/MP report writer commands, clauses, and functions, and the SQLCI options that relate to reports.The manual tells how to produce formatted reports using data from a NonStop SQL/MP database. Product Version NonStop SQL/MP D30 Supported Release Version Updates (RVUs) This publication supports D40.00 and all subsequent D-series RVUs, and G01.
Document History Part Number Product Version Published 107934 NonStop SQL/MP D30 December 1994 527213-001 NonStop SQL/MP D30 September 2003
HP NonStop SQL/MP Report Writer Guide Index Figures What’s New in This Manual v Manual Information v New and Changed Information Tables v About This Manual vii Audience vii How to Use This Manual vii Related Manuals viii Notation Conventions x 1. Introduction to the NonStop SQL/MP Report Writer Data Organization 1-1 SQL Database Reports 1-2 Report Development Steps 1-3 Report Layout and Style 1-4 Report Writer Components 1-10 2.
. Customizing a Report Contents Using Views 3-26 Using Subqueries 3-28 Developing Multistep Queries 3-32 4.
Figures (continued) Contents Figures (continued) Figure 2-11. Figure 2-12. Figure 2-13. Figure 3-1. Figure 3-2. Figure 3-3. Figure 3-4. 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. Figure 4-23. Figure 4-24. Figure 4-25.
Figures (continued) Contents Figures (continued) Figure 4-27. Figure 4-28. Figure 4-29. Figure 4-30. Figure 4-31. Figure 4-32. Figure 4-33. Figure A-1.
What’s New in This Manual Manual Information HP NonStop SQL/MP Report Writer Guide Abstract This manual explains how to use the HP NonStop™ SQL/MP report writer commands, clauses, and functions, and the SQLCI options that relate to reports.The manual tells how to produce formatted reports using data from a NonStop SQL/MP database. Product Version NonStop SQL/MP D30 Supported Release Version Updates (RVUs) This publication supports D40.00 and all subsequent D-series RVUs, and G01.
What’s New in This Manual New and Changed Information HP NonStop SQL/MP Report Writer Guide—527213-001 vi
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/MP database. The manual also provides an overview of the report writer commands and SQLCI 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 Manuals About This Manual Related Manuals This manual is part of the NonStop SQL library of manuals (as shown in Figure i). The library includes the following manuals: • • • • • • • • Introduction to NonStop SQL provides an overview of the SQL/MP relational database management system. SQL Quick Start describes how to run SQLCI, how to execute simple queries on a database, how to modify data, and how to produce a formatted report.
Related Manuals About This Manual Figure i. NonStop SQL/MP Library Map Introductory Manuals Introduction to NonStop SQL (C30.07)* NonStop SQL Quick Start (C30.07)* Usage Guides NonStop SQL/MP Install and Management Guide NonStop SQL/MP Query Guide Programming Manuals NonStop SQL/MP Version Management Guide NonStop SQL/MP Report Writer Guide NonStop SQL/MP Programming Manual for C NonStop SQL/MP Programming Manual for COBOL85 NonStop SQL Programming Manual for Pascal (C30.
Notation Conventions About This Manual Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: 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.
General Syntax Notation About This Manual { } Braces. A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned 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.
General Syntax Notation About This Manual a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections. For example: ALTER [ / OUT file-spec / ] LINE [ , attribute-spec ]… !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).
1 Introduction to the NonStop SQL/MP Report Writer NonStop SQL/MP is a relational database management system that uses the industry standard SQL language to define and manipulate data. The NonStop SQL/MP conversational interface (SQLCI) includes a report writer that enables you to format data retrieved with a SELECT command and to display or print the formatted data in a report.
SQL Database Reports Introduction to the NonStop SQL/MP Report Writer Figure 1-1. Sample Content of Tables COLUMNS PARTNUM 212 244 255 2001 6500 6603 7102 7301 PARTDESC PRICE PC SILVER, 20 MB PC GOLD, 30 MB PC DIAMOND, 60 MB GRAPHIC PRINTER, M1 DISK CONTROLLER PRINTER CONTROLLER SMART MODEM, 1200 SMART MODEM, 2400 QTY_AVAILABLE 3525 4426 3321 2100 2532 430 2200 2332 2500.00 3000.00 4000.00 1100.00 95.00 45.00 275.00 425.
Introduction to the NonStop SQL/MP Report Writer Report Development Steps options, layout options, or report formatting commands. However, if you want to design your own report, you can revise the default format by entering report formatting commands or by using the SET LAYOUT or SET STYLE commands to change the default values of layout or style options. Using the report writer features, the SELECT command, and the LIST command, you can develop a report in stages and view each change as you make it.
Introduction to the NonStop SQL/MP Report Writer Report Layout and Style You can specify layout options, such as margins, the number of lines per page, line spacing, and whether default headings are to print the above columns of data. 3. Customizing the style Use the style options to customize subtotal labels, the style of date and time formats, and special characters representing the underline and the decimal point. 4.
Report Layout and Style Introduction to the NonStop SQL/MP Report Writer Default Report 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. Figure 1-2 shows an example of a default report produced with these commands: >> >> >> +> +> +> +> +> S> VOLUME SALES; SET LIST_COUNT 0; SELECT P.PARTNUM, PARTDESC, QTY_ORDERED, ORDERNUM FROM PARTS P, ODETAIL O WHERE P.
Introduction to the NonStop SQL/MP Report Writer Report Layout and Style These are the components of the default report: • • Detail lines ° Each detail line contains all elements of the select list in the order you specify them in the SELECT command. (In Figure 1-2, the select list is the columns shown on the same line as SELECT.) ° Values are displayed in the default display formats.
Introduction to the NonStop SQL/MP Report Writer • Detail lines: A detail line is printed for each row of the result table described by the select list in the SELECT command.
Report Layout and Style Introduction to the NonStop SQL/MP Report Writer Figure 1-3. A Sample Formatted Report >> SET LIST_COUNT 0; >> SET LAYOUT RIGHT_MARGIN 60; >> SELECT P.PARTNUM, PARTDESC, PRICE, UNIT_PRICE, +> QTY_ORDERED, ORDERNUM +> FROM SALES.PARTS P, SALES.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.
Report Layout and Style Introduction to the NonStop SQL/MP Report Writer 7. Break footing 8. Total lines 9. Report footing 10. Page footing 11. Report Layout and Style Figure 1-4. Example of a Formatted Report Summary of Orders 09/25/94 Report Author and Version: Tom Jones, v. 2 Part No. Unit Price PC SILVER, 20 MB 212 2450.00 Total Price (2) Order No. (3) (4) 29400.00 20000.00 2500.00 * (5) 400410 500450 49400.00 Suggested Price: (1) (6) 2500.00 (7) PC GOLD, 30 MB 244 3500.00 3500.
Report Writer Components Introduction to the NonStop SQL/MP Report Writer Figure 1-5. Example of a Formatted Report Summary of Orders 09/25/94 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 275.00 275.00 275.00 7102 1925.00 1375.00 1650.00 * 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/MP Report Writer • • • • Report Writer Components Clauses Style options Layout options Report functions This subsection provides a summary of the report writer components. For a detailed description of these components, refer to the SQL/MP 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/MP Report Writer Report Writer Components between items. For a print item formatted with the AS clause descriptor Cn.w, use w as the field width. For example, if a VARCHAR column named JOBRESP is formatted with AS C0.30, use 30 as the field width for that item. Report Formatting Commands Report formatting commands specify the content of detail lines, titles, footings, and column headings.
Report Writer Components Introduction to the NonStop SQL/MP Report Writer Table 1-2. Report Formatting Command Clauses (page 2 of 2) Clause What the Clause Specifies SKIP Advance a specific number of lines before printing the next print item SPACE Number of blanks to print before the next print item TAB Print position of the next print item Note. For detailed descriptions of the NEED, PAGE, SKIP, SPACE, and TAB clauses, see the DETAIL command in the SQL/MP Reference Manual.
Report Writer Components Introduction to the NonStop SQL/MP Report Writer Table 1-4.
Introduction to the NonStop SQL/MP Report Writer Report Writer Components Table 1-6. SQLCI Commands Command Description CANCEL Cancels the current SELECT command. EXECUTE Executes a compiled command. LIST Displays rows retrieved by the SELECT command. LOG Starts or ends the logging of session activity to a file. OUT Specifies or closes the output file. OUT_REPORT Directs the output of a SELECT command to a specific report file or closes the current report file. PREPARE Compiles a command.
Introduction to the NonStop SQL/MP Report Writer HP NonStop SQL/MP Report Writer Guide—527213-001 1-16 Report Writer Components
2 Using SQLCI and the Report Writer You can control the environment of a report writing session by using general SQLCI commands. To develop a report, you can do the following: • • • • • • Start and exit an SQLCI session. Set up attributes of your session environment such as default options, the default volume, and output files. Use windows to view vertical segments of a report. Enter report formatting commands. Execute commands repeatedly. Define reports in command files.
Setting Up Your Session Environment Using SQLCI and the Report Writer If an object is on the current default volume and subvolume, you can omit everything but the table or view name. The system expands the name by using the current default system, volume, and subvolume. You can use the ENV command to display the current default settings, as shown in Figure 2-1. Figure 2-1.
Using SQLCI and the Report Writer Setting Up Your Session Environment Other elements of your session environment are: CATALOG The current default catalog; this catalog does not affect your report writing activity. LANGUAGE The language of the text in the message file. LOG The file to which SQLCI logs your session activity. SQLCI writes the commands you enter and the output produced by the commands to the log file.
Setting Up Your Session Environment Using SQLCI and the Report Writer truncated, and whether warning messages are displayed. Figure 2-2 illustrates the setting and displaying options. Figure 2-2.
Setting Up Your Session Environment Using SQLCI and the Report Writer 0. For more information on the summary of the options, see Report Writer Components on page 1-10. Listing Rows of a Report You retrieve rows of data by using the SELECT command. You can specify the number of rows to be displayed or printed by setting the LIST_COUNT session option and by using the LIST command. By default, LIST_COUNT is set to ALL when you begin your SQLCI session.
Using SQLCI and the Report Writer Setting Up Your Session Environment output lines are listed in addition to any headings, titles, footings, and other output that is not counted. LIST ALL lists all selected rows and then returns you to the standard prompt and cancels the SELECT command. Canceling a SELECT Command To stop listing rows and cancel a SELECT command, enter the CANCEL command at the S> prompt: S> CANCEL; If you accidentally cancel the SELECT command, you can execute it again.
Setting Up Your Session Environment Using SQLCI and the Report Writer 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, you can use the SET LAYOUT WINDOW command to specify which vertical portion of the report you want to see. For example, suppose a report is designed for a printer that prints 120 single-byte characters per line.
Setting Up Your Session Environment Using SQLCI and the Report Writer Figure 2-6. Defining a Window for Report Output S> SET LAYOUT WINDOW EMPNUM; S> LIST FIRST 1; EMPNUM FIRST_NAME 23 ROGER LAST_NAME JOBCODE GREEN S> 1000 VST0206.vsd In the preceding example, you can specify SET LAYOUT WINDOW TAB 60 to produce the same result. You can use the TAB form of SET LAYOUT WINDOW at either the standard prompt or the select-in-progress prompt.
Setting Up Your Session Environment Using SQLCI and the Report Writer For an overview of how to set margins, see Setting Margins on page 4-1. Figure 2-7. 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.
Entering Report Formatting Commands Using SQLCI and the Report Writer Entering Report Formatting Commands The report formatting commands must be associated with a specific SELECT command. You can enter these commands only at the select-in-progress prompt (S>). This prompt appears after you enter the SELECT command, but only if the number of rows to be listed is greater than the current value of the LIST_COUNT session option.
Using SQLCI and the Report Writer Entering Report Formatting Commands There can be only one version of each of the following commands in effect 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.
Using SQLCI and the Report Writer Specifying Output Files specifies that column 1 will be subtotaled whenever the value of any break item changes. However, if you enter the SUBTOTAL COL 2 command, only column 2 will be subtotaled: S> BREAK ON DEPTNUM, JOBCODE; S> SUBTOTAL COL 1; . .
Using SQLCI and the Report Writer Executing a Command Repeatedly OUT_REPORT Files You can direct output from a SELECT command, both default reports and customized reports, to a specific file by using the OUT_REPORT command. Enter the following command to direct reports to a disk file named DRAFT: >> OUT_REPORT DRAFT; The following command directs reports to a spooler collector for a printer: >> OUT_REPORT $S.
Using SQLCI and the Report Writer Executing a Command Repeatedly Repeating Stored Commands You can use the FC and ! commands to repeat a command you have entered during your SQLCI session. The exclamation point command repeats a command but does not allow you to modify it. The FC command allows you to edit the command before reexecuting it.
Using SQLCI and the Report Writer Defining Reports in Command Files Figure 2-9. Example of Repeating Stored Commands Example 1 >> FC SELECT P >> SELECT PARTNUM, QTY_AVAILABLE .. I, PRICE >> SELECT PARTNUM, QTY_AVAILABLE, PRICE .. Insert change and press RETURN. Press RETURN. +> FROM SALES.PARTS; Press RETURN. .. S> Example 2 Request History. SQLCI >> HISTORY 7 displays most 3 > SELECT PARTNUM, QTY_ AVAILABLE FROM SALES.PARTS; recent commands.
Defining Reports in Command Files Using SQLCI and the Report Writer Saving Report Commands in an Command file To create a report and test the results as you work, follow these steps: 1. If you want to enter report formatting commands, you must prevent the entire SELECT command output from displaying immediately. To do this, set the LIST_COUNT option to a number less than the number of rows that will be retrieved.
Using SQLCI and the Report Writer Defining Reports in Command Files You might want to change a layout or style option. For example, you can increase the number of spaces between columns to 5 as follows: S> SET LAYOUT SPACE 5; S> LIST FIRST 1; ORDERNUM PARTNUM UNIT_PRICE -------- ------- ---------Continue adding and revising formatting commands and listing lines until you are satisfied with your report definition. To return to the standard SQLCI prompt, enter either LIST ALL or CANCEL.
Using SQLCI and the Report Writer • Defining Reports in Command Files You can save other commands such as the VOLUME command. For example, the following command saves the command that begins with the letters VOLUME $I from the history buffer: SAVE COMMAND VOLUME $I TO ORDFILE; You can use the HISTORY command to display commands you might want to save.
Using SQLCI and the Report Writer Defining Reports in Command Files Figure 2-10. Modifying OBEY Commands >> OBEY ORDFILE; >> SET SESSION AUTOWORK ON; . . >> SELECT O.ORDERNUM, ORDER_DATE, CUSTNUM, . . +> UNIT_PRICE*QTY_ORDERED HEADING "TOTAL PRICE"; Commands are displayed as they execute. Revise title. . . S> FC BREAK TITLE S> BREAK TITLE O.ORDERNUM (SKIP 1, "CUSTOMER ", CUSTNUM, R"Cust. No.", CUSTNUM, S> BREAK TITLE O.ORDERNUM (SKIP 1, "Cust. No. ", CUSTNUM, . . . .
Using SQLCI and the Report Writer Using the Report Writer to Produce Text From Data Creating an Command File From a Log File You can use a log file as a command file. When you start logging, specify that you want only commands written to the log file. For example, the following command specifies only commands are to be logged to a file named REPDEFN: >> LOG REPDEFN COMMANDS CLEAR; Next, enter commands in the same order as shown in Saving Report Commands in an Command file on page 2-16.
Using SQLCI and the Report Writer Using the Report Writer to Produce Text From Data RPXSTATS produces the following three files: • • • SQLUPDS, for the statistics FILEINF, an intermediate command file that creates the SQLFILES file SQLFILES, a file for the INVOKE directives and FILEINFO commands The RUNXREPS command file purges any pre-existing data from these files using the CLEAR option.
Using SQLCI and the Report Writer Using the Report Writer to Produce Text From Data Figure 2-11. Example of Using the Report Writer to Produce Text From Data ?SECTION COLSTATS SET LIST_COUNT 0; SET LAYOUT PAGE_LENGTH ALL; SET STYLE HEADINGS OFF; SELECT C.TABLENAME, UNIQUEENTRYCOUNT, SECONDHIGHVALUE, SECONDLOWVALUE, COLNAME FROM COLUMNS C, TABLES T WHERE C.TABLENAME LIKE ?TBOL AND C.TABLENAME = T.
Using SQLCI and the Report Writer Using the Report Writer to Produce Text From Data Figure 2-12. Example of Using the Report Writer to Produce Text From Data ?SECTION BASETAB SET LIST_COUNT 0; SET LAYOUT PAGE_LENGTH ALL; SET STYLE HEADINGS OFF; SELECT B.TABLENAME, ROWCOUNT, STATISTICSTIME FROM BASETABS B, TABLES T WHERE B.TABLENAME LIKE ?TBOL AND B.TABLENAME = T.
Using SQLCI and the Report Writer Using the Report Writer to Produce Text From Data Figure 2-13.
3 Selecting Data for a Report Before you can define a report, you must select the data by entering a SELECT command. The report formatting commands you specify refer to columns (or print items) specified in the select list of the SELECT command.
Selecting Data for a Report Developing a Query After you know what information you want, you can compose a SELECT command to retrieve the data. The SELECT command must retrieve all the data you need, including information in columns, titles, headings, and footings. In the following pages, one approach to composing a SELECT command is presented. Figure 3-1 illustrates the general content of a report on suppliers and the parts they supply.
Developing a Query 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.
Developing a Query Selecting Data for a Report for each column of the table. The table you specify must be on the current default subvolume, or you must qualify the table name in the FROM clause, as shown: >> SELECT * FROM SALES.PARTS; To select the data needed for the report in Figure 3-1, you must join rows of the three tables. First, consider the joining of rows from the PARTS and PARTSUPP tables that have the same PARTNUM value: >> >> +> +> VOLUME INVENT; SELECT * FROM SALES.
Selecting Data for a Report Developing a Query FROM clause to use as an abbreviation for qualifying column names. For example, the following command defines P and PS as explicit correlation names: >> SELECT * +> FROM SALES.PARTS P, PARTSUPP PS +> WHERE P.PARTNUM = PS.PARTNUM; Correlation names are required in some types of subqueries. These subqueries are discussed in Using Subqueries on page 3-28.
Developing a Query Selecting Data for a Report You select specific columns by specifying the column names. The select list for the supplier parts summary consists of the column names in boldface type that appear in the following example: >> SELECT S.SUPPNUM, +> SUPPNAME, +> CITY, +> STATE, +> P.PARTNUM, +> QTY_AVAILABLE, +> PARTCOST, +> PRICE +> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S +> WHERE P.PARTNUM = PS.PARTNUM +> AND PS.SUPPNUM = S.
Developing a Query Selecting Data for a Report 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. >> >> +> +> +> +> +> VOLUME INVENT; SELECT S.SUPPNUM, SUPPNAME, P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S WHERE P.PARTNUM = PS.PARTNUM AND PS.SUPPNUM = S.
Developing a Query Selecting Data for a Report Table 3-1. Search Condition Predicates (page 2 of 2) Predicate Purpose IN Determines if a value is equal to any of the values in a list or in a collection of values; for example: PARTNUM IN (100, 120, 150) (Part number must be 100, 120, or 150.
Developing a Query Selecting Data for a Report The next quantified predicate selects rows with a part cost equal to any of the parts supplied by supplier number 1: +> AND PARTCOST = ANY ( SELECT PARTCOST FROM PARTSUPP +> WHERE SUPPNUM = 1 ) ; For more examples of quantified predicates as well as examples of EXISTS predicates, see the SQL/MP Reference Manual. • The following BETWEEN predicate specifies only suppliers of parts numbered from 4000 through 6103: +> AND P.
Developing a Query Selecting Data for a Report To confirm the result of the previous query, you can select the PARTLOC information for the two parts. The QTY_ON_HAND value is greater than 500 in at least one location. >> SELECT * FROM INVENT.PARTLOC +> WHERE PARTNUM IN ( 2001, 2403 ); S> LIST ALL; LOC_CODE PARTNUM QTY_ON_HAND -------- ------- ----------A10 A88 G88 P10 2001 2403 2403 2001 800 735 32 0 --- 4 row(s) selected.
Selecting Data for a Report Developing a Query +> WHERE STREET, CITY, STATE = +> "2300 BROWN BLVD", "FRESNO", "CALIFORNIA"; Note. To compare character data that uses collations, see “COMPARISON” in the SQL/MP Reference Manual Comparing Character Values If a column contains text (character data), you must enclose the comparison value in single or double quotation marks. You must enter the characters you want to match exactly as the characters are stored in the column.
Developing a Query Selecting Data for a Report The following rules describe how the system compares character values in comparison and LIKE predicates. (BETWEEN and IN predicates follow the same rules as comparison predicates.) • • • • • Trailing blanks are significant for fixed-length columns. For example, the value "DISK" inserted in a CHAR(6) column is "DISK". Only the data inserted is significant for variable-length columns. For example, the value "DISK" inserted in a VARCHAR(6) column is "DISK".
Selecting Data for a Report Developing a Query Note. Specifying a pattern beginning with percent (%) in a LIKE predicate can result in a scan of a complete table. You should not use this type of pattern when executing a query online unless other predicates in the query provide access paths through indexes or primary keys. If you need to use this type of pattern, execute the query in batch mode when the system has fewer demands on its resources.
Developing a Query Selecting Data for a Report Rows with part numbers between 2000 and 3000 or part numbers greater than 6000 are not selected. All other rows are selected. Consider these points: • The AND that appears in a BETWEEN predicate does not connect two predicates or search conditions. • You can specify NOT BETWEEN, NOT IN, and NOT LIKE, but you cannot specify NOT =; you must use <> to indicate not equal.
Grouping Data for Calculations Selecting Data for a Report Figure 3-4. Example of 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 2001 400410 2003 700410 2003 600480 400410 6301 400410 6400 6401 800660 600480 7301 400410 7301 --- 15 row(s) selected. VST0304.
Selecting Data for a Report Grouping Data for Calculations >> SELECT PARTNUM, SUM (QTY_ORDERED) +> FROM SALES.ODETAIL +> GROUP BY PARTNUM +> ORDER BY PARTNUM; S> LIST NEXT 2; PARTNUM (EXPR) ------- ------------------212 20 244 47 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 • • Grouping Data for Calculations In summary, use the ORDER BY clause to arrange rows in sequence; use the GROUP BY clause to combine values and create one row from a group of rows. If you want to include the part description in the first query, you can join the ODETAIL and PARTS tables. Because rows in the result table for a specific part number all contain the same part description, you can include the part description as a grouping column without changing the result.
Selecting Data for a Report Grouping Data for Calculations examining the report, you can locate groups that have at least two orders: for example, BROWN MEDICAL CO. >> SELECT C.CUSTNUM, CUSTNAME, COUNT (DISTINCT ORDERNUM) +> FROM SALES.CUSTOMER C, SALES.ORDERS O +> WHERE C.CUSTNUM = O.CUSTNUM +> GROUP BY C.
Selecting Data for a Report Grouping Data for Calculations >> VOLUME SALES; >> SELECT P.PARTNUM, MIN(UNIT_PRICE), MAX(UNIT_PRICE) +> FROM PARTS P, ODETAIL O +> WHERE P.PARTNUM = O.PARTNUM +> GROUP BY P.PARTNUM; S> LIST NEXT 3; PARTNUM (EXPR) (EXPR) ------- ------------ -----------212 2450.00 2500.00 244 2800.00 3500.00 255 3800.00 4000.00 Determining Which Columns to Group By specifying different sets of columns in the GROUP BY clause, you change the results of the functions you apply to the group.
Selecting Data for a Report Selecting Distinct Rows +> GROUP BY JOBCODE, DEPTNUM; JOBCODE DEPTNUM (EXPR) ------- ------- ------------------100 1000 1 100 1500 1 500 1000 3 600 1500 2 900 1000 1 900 1500 1 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.
Selecting Data for a Report Using Expressions to Calculate Report Values The PARTSUPP table contains rows that record a part number, the supplier number, the part cost, and the quantity received. If the supplier changes the cost of a part, more than one row of the PARTSUPP might describe the same part number and supplier. If you want to count the number of distinct suppliers of each part, you can use the following query: >> SELECT PARTNUM, COUNT ( DISTINCT SUPPNUM ) +> FROM INVENT.
Using Expressions to Calculate Report Values Selecting Data for a Report Figure 3-5. Expressions in the Select List >> VOLUME INVENT; >> SELECT P.PARTNUM, +> PARTCOST, +> PARTCOST * QTY_AVAILABLE, +> QTY_AVAILABLE * (PRICE - PARTCOST) +> FROM SALES.PARTS P, PARTSUPP PS, 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.
Using Parameters With SELECT Commands Selecting Data for a Report Figure 3-6. Expressions in the Detail Line >> VOLUME INVENT; >> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE +> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S +> WHERE P.PARTNUM = PS.PARTNUM AND PS.SUPPNUM = S.SUPPNUM; S> DETAIL P.PARTNUM HEADING "Part No.", +> PARTCOST HEADING "Unit Cost", +> PARTCOST * QTY_AVAILABLE HEADING "Total Cost", +> QTY_AVAILABLE * (PRICE - PARTCOST) HEADING "Profit"; S> LIST NEXT 2; Part No.
Selecting Data for a Report Preparing a SELECT Command value. You cannot use the CURRENT function or any other date-time functions to specify a parameter value. You cannot refer to parameters in a report formatting command. For another method of passing values to commands, see Using TACL to Pass Parameters on page 4-44. Suppose a SELECT command in an command file named SALESUM consists of the following commands: SET LIST_COUNT 0; VOLUME SALES; SELECT P.
Preparing a SELECT Command Selecting Data for a Report command to determine the system resources required to execute the query. For descriptions of the PREPARE, EXECUTE, and EXPLAIN commands, see the SQL/MP Reference Manual. For example, suppose you want to print information about the orders from a customer assigned to a specific sales representative. This report is produced each month for sales representatives and all of their customers.
Selecting Data for a Report Using Views To prepare the SELECT command enter the following command: >> OBEY SELPREP; ( PREPARE command is displayed here.) . . --- SQL command prepared. To execute the prepared SELECT command for each report and print the report, enter the following command: >> OBEY PRINTREP; Using Views A view is a logical table derived by projecting a subset of the columns or selecting a subset of the rows from one or more tables or from another view.
Selecting Data for a Report Using Views To further simplify your task, you can specify in an EDIT file the set of report formatting commands to print the invoice. The following commands define one version of an invoice. These commands are stored in a file named INVOICE. For explanations and examples of the report formatting commands used in this report, see Section 4, Customizing a Report.
Using Subqueries Selecting Data for a Report Figure 3-7. Example of an Invoice INVOICE Customer : DATASPEED 300 SAN GABRIEL WAY NEW YORK, NEW YORK 10014 Part No. 244 2001 2403 5100 Unit Price 3500.00 1100.00 620.00 150.00 Quantity 3 3 6 10 Order Date : 870410 Deliv. Date : 870410 Order No. 100210 Total $ 10,500.00 $ 3,300.00 $ 3,720.00 $ 1,500.00 $ 19,020.00 Terms 60 days net. VST0307.vsd Using the view saves time if you want to define several reports based on the same data.
Selecting Data for a Report Using Subqueries +> WHERE PARTNUM = 2003); S> LIST ALL; SUPPNUM PARTNUM PARTCOST ------- ------- -------2 2003 1400.00 10 2003 1450.00 --- 2 row(s) selected. >> This query finds all suppliers who charge more than the minimum price for part number 2003. The subquery is evaluated once to determine the minimum cost for the part. Each row selected by the main query is compared to the result of the subquery.
Selecting Data for a Report Using Subqueries The following specification uses an implicit correlation name to achieve the same result as the preceding query: >> +> +> +> +> +> SELECT SUPPNUM, PARTNUM, PARTCOST FROM INVENT.PARTSUPP WHERE PARTCOST > (SELECT AVG(PARTCOST) FROM INVENT.PARTSUPP P WHERE PARTSUPP.PARTNUM = P.PARTNUM) ORDER BY SUPPNUM; Defining explicit correlation names provides clearer documentation of what the query does.
Selecting Data for a Report Using Subqueries VOLUME SALES; SET LIST_COUNT 0; SELECT X.PARTNUM, PARTDESC, 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.
Developing Multistep Queries Selecting Data for a Report Figure 3-8. Example of 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 Consider the following points when using subqueries: • • • • A subquery must be enclosed in parentheses.
Selecting Data for a Report Developing Multistep Queries Multilevel Group Aggregates Grouping Data for Calculations on page 3-15 describes ways to apply aggregate functions to groups of rows. If you want to apply aggregate functions to multiple levels of groups, you must specify more than one query and use temporary tables. For example, suppose you want to report the average salary for each department and within each department for each job classification. Follow these steps: 1.
Developing Multistep Queries Selecting Data for a Report 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.
Developing Multistep Queries Selecting Data for a Report 4. Select the report information from the DEPTTEMP table: >> >> +> +> S> +> +> +> S> SET LIST_COUNT 0; 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.
Selecting Data for a Report Developing Multistep Queries 3. Select the information for the report. Include an expression in the select list to compute the percent of the department average: >> +> +> +> S> +> +> S> SELECT E.DEPTNUM, EMPNUM, LAST_NAME, SALARY, SALARY/AVGSAL*100.00 FROM PERSNL.EMPLOYEE E, TEMPTABS.AVGTEMP A WHERE E.DEPTNUM = A.DEPTNUM; DETAIL DEPTNUM, EMPNUM, LAST_NAME, SALARY AS F10.2, COL 5 AS F10.
4 Customizing a Report You can use the report formatting commands and the layout and style options to enhance a report. The examples in this section show you how to produce the special effects you might want in a report.
Defining the Layout Customizing a Report When you are working at a terminal, the default OUT_REPORT file is the terminal. The default right margin is 80 for most terminals. To display the current right margin, enter the following: >> SHOW LAYOUT RIGHT_MARGIN; RIGHT_MARGIN 80 If you are designing a report to be printed on a wider page, you can set the right margin as needed.
Defining the Layout Customizing a Report Figure 4-2. Default Margin Settings - Printer Report Printed Report Left Margin 0 Print Position 1 Right Margin At 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.
Defining the Layout Customizing a Report 4 blank lines appear here. ) Summary of Employees CUSTNUM CUSTNAME ------- -------- The first blank line is the default top margin. The next three blank lines are the specified skips. The blank line following the title is the default skip that always follows the title line. You use the same technique for bottom margins, except SKIP follows the footing text if specified.
Defining the Layout Customizing a Report PAGE_LENGTH This layout option specifies the number of lines from the top to the bottom of a page. For example, the following command sets a page length of 55 lines: >> SET LAYOUT PAGE_LENGTH 55; The default page length for a printed report is 60 lines. On a terminal, the default page length is ALL; the entire report is a single page unless you specify the PAGE clause.
Defining the Layout Customizing a Report The report in Figure 4-3 illustrates features related to page breaks and page numbering. The horizontal lines indicate where the page breaks occur. The following command selects the data for the report: >> +> +> +> SELECT * FROM PERSNL.EMPLOYEE E, PERSNL.DEPT WHERE E.DEPTNUM = DEPT.DEPTNUM ORDER BY E.
Defining the Layout Customizing a Report The NEED clause is useful when you want to keep a set of lines together, such as a complete address. In the next example, a page break will not occur in the middle of information about a single supplier. >> +> S> +> +> +> +> +> S> S> SELECT * FROM INVENT.SUPPLIER ORDER BY SUPPNAME; DETAIL NEED 4, "Supplier No.
Defining the Layout 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 15 2 DATADRIVE CORP 3 100 MAC ARTHUR 4 DALLAS, TEXAS 75244 5 Supplier No. 6 Supplier No. 3 7 HIGH DENSITY INC 8 7600 EMERSON 9 NEW YORK, NEW YORK 10230 10 S> VST0402.
Defining the Layout Customizing a Report LINE_SPACING This layout option indicates to the report writer how many lines to advance between report lines. The default setting for LINE_SPACING when you begin an SQLCI session is 1, which results in a single-spaced report. For double spacing, enter the following: >> SET LAYOUT LINE_SPACING 2; SKIP clause You can specify a SKIP clause as a print item in a detail line, title, or footing.
Defining the Layout Customizing a Report SPACE clause You can use the SPACE clause as a print item in a detail line, title, or footing to insert a specified number of spaces before displaying or printing the next item. 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.
Specifying the Items in a Detail Line Customizing a Report 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.
Naming Select List and Detail Line Items Customizing a Report S> DETAIL EMPNUM HEADING "Employee No.", +> LAST_NAME NOHEAD, +> 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 Column Headings on page 4-16. Naming Select List and Detail Line Items You can assign an alias name to any item in the select list by using the NAME command.
Organizing Rows Into Break Groups Customizing a Report +> +> HEADING "Profit" NAME PROFIT; The following TOTAL command refers to the PROFIT column: 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. Organizing Rows Into Break Groups In addition to grouping rows for aggregate function calculations, you can group rows into break groups.
Organizing Rows Into Break Groups Customizing a Report Figure 4-6 shows the resulting report. Figure 4-6. Example of Break Groups SREP CUSTNUM ORDERNUM 220 324 500450 1234 100210 7777 100250 221 5635 101220 222 926 200300 PARTNUM UNIT_PRICE 212 255 2001 2002 2402 244 2001 2403 5100 244 5103 6301 6500 255 5103 7102 7301 244 2500.00 3900.00 1100.00 1500.00 330.00 3500.00 1100.00 620.00 150.00 3500.00 400.00 245.00 95.00 3900.00 400.00 275.00 425.00 3500.
Labeling Information Customizing a Report Labeling Information A report can contain various types of labels: column headings, titles, footings, line numbers, and text inserted between print items in an output line. Column Headings • • • • You can choose to have default column headings or customized column headings in a report. If you use the default report format, default headings appear.
Labeling Information Customizing a Report 5. The column name is the default heading for a print item that is a column of a table or view. The column name is defined when the database administrator creates the table or view or adds the column to the table or view. To display column names, use the INVOKE command to display a table or view definition. 6. The heading EXPR is the default heading for expressions, functions, and numeric literals specified in the DETAIL command. 7.
Labeling Information Customizing a Report • The width of the display field for a print item that is a table or view column is determined by the default display format for the data type of the column. You can override the default width by specifying an AS clause: for example, CONCAT (FIRST_NAME, SPACE 1, LAST_NAME) AS A25. You cannot use an AS clause with columns of the INTERVAL data type.
Labeling Information Customizing a Report • • • A page title appears at the top of each page of a report. A report title follows the page title on the first page of the report. A break title precedes the break group for which it is defined. Only one REPORT TITLE and one PAGE TITLE command are in effect at a time. There can be one BREAK TITLE command for each item specified in a BREAK ON command.
Labeling Information Customizing a Report S> PAGE TITLE "Customer Delivery Summary ", +> TAB 45, +> "Cust. No.", +> C.CUSTNUM AS I4 ; S> SET LAYOUT PAGE_LENGTH 10; S> LIST N 10; Figure 4-8 shows the first nine rows of report output, which appears on four pages. The customer number in each page title is taken from the first row of output on the page.
Labeling Information Customizing a Report Break Titles You can specify a break title for each break group. You can have more than one BREAK TITLE command in effect at a time, but each command must relate to a different break column. In a break title, you can include columns from the select list, named print items from the DETAIL print list, string literals, and arithmetic expressions.
Labeling Information Customizing a Report Figure 4-9. Example of Break Titles Part No. Quantity CUSTNUM Break Title Customer No. 21 CENTRAL UNIVERSITY CUSTNUM 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 Footings A report can contain three types of footings: • • • A page footing appears at the bottom of each page of a report.
Labeling Information Customizing a Report 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.
Labeling Information Customizing a Report Figure 4-11. Example of 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.
Labeling Information Customizing a Report +> ("Earliest Delivery Date: ", +> DELIV_DATE AS I6, +> SKIP 1) ; S> BREAK FOOTING R.ORDERNUM +> ("Order", R.ORDERNUM, +> SPACE 3, +> "Salesperson's number: ", +> SALESREP, +> SKIP 1) ; S> 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. The ORDERNUM value is taken from the last row of each order break group. Figure 4-12. Example of Break Footings Part No.
Labeling Information Customizing a Report 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 you want to number the entries in a listing of parts and the suppliers who supply them. The numbering sequence is to be restarted at each new page. The following commands produce the list: >> +> +> S> S> +> +> +> +> +> +> +> +> S> S> S> +> S> SELECT * FROM INVENT.SUPPLIER, INVENT.
Labeling Information Customizing a Report Figure 4-13. Example of 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.
Formatting Data Values Customizing a Report Text Inserted into a Line You can insert text into a detail line, title, or footing by specifying a string literal as a print item. Figure 4-13 illustrates this technique. You can use the technique to create the following effects: • • Insert commas between concatenated items. Insert descriptions of an item that precedes or follows the string literal.
Formatting Data Values Customizing a Report Table 4-1. Default Display Formats (page 2 of 2) SQL Data Type Default Display Format DECIMAL (n,s) In+1 (or Fn+2.s if s > 0) DECIMAL (n,s) UNSIGNED In (or Fn+1.s if s > 0) PIC S9(i) [V9(s)] Ii+1 (or Fi+2.s if s > 0) PIC 9(i) [V9(s)] Ii (or Fi+1.s if s > 0) PIC SV9(s) Fs+2.s PIC V9(s) Fs+1.s FLOAT (where precision is 1 through 22) E14.7 FLOAT (where precision is 23 through 54) E24.17 REAL E14.7 DOUBLE PRECISION E24.
Formatting Data Values Customizing a Report Display Format Specifications A display format specification can include several elements: display descriptors, scalesign descriptors, decorations, and modifiers. • 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.
Formatting Data Values Customizing a Report or too large for the field. You can specify multiple conditions. For more information on examples of using decorations, see Monetary Values on page 4-32. • You can specify modifiers to control justification, insert filler characters, and specify an overflow character for the print item. A modifier applies to a single print item and overrides report defaults.
Formatting Data Values Customizing a Report In a DETAIL command print item, the AS clause must precede the HEADING and NAME options. In the next example, the scale-sign descriptor specifies a scale factor for a very large number. The total cost value is scaled to thousands of dollars. The descriptor -3P specifies a scale factor of 10** -3 (or 0.001). You cannot specify a scale-sign descriptor for an I display descriptor. >> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST +> FROM SALES.PARTS P, INVENT.
Formatting Data Values Customizing a Report If you calculate any totals or subtotals, the mask must accommodate the resulting values. If the previous query is modified to calculate the sum of salaries for each job 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 PERSNL.
Formatting Data Values Customizing a Report If you want the dollar sign adjacent to the value, you can modify the original query by adding a PF modifier and by revising the mask descriptor: >> +> +> S> +> +> S> Job SELECT JOBCODE, AVG(SALARY) FROM PERSNL.EMPLOYEE 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.
Formatting Data Values Customizing a Report The previous decoration specification assumes that all values will be positive. If you expect negative or zero values, you must expand the decoration as shown: AS "[MA1'CR',MPF'$',ZA1' '] F13.2" In this clause, three decorations apply to the value: • • • 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.
Formatting Data Values Customizing a Report The I w[. m] display descriptor also suppresses leading zeros if you omit m. For example, I8 does not print leading zeros but I8.6 does if the integer consists of fewer than 6 digits. Descriptor Result 18 355 18.6 000355 In a mask, you can use the uppercase character Z to suppress leading and trailing zeros. 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.
Formatting Data Values Customizing a Report CONCAT (LAST_NAME STRIP, ", ", FIRST_NAME) or CONCAT (FIRST_NAME STRIP, SPACE 1, LAST_NAME) The first clause produces BENEDETTI, JULIO, and the second clause produces JULIO BENEDETTI. You can use a similar technique to combine city and state or to combine addresses in one field. If you specify STRIP, the report writer strips trailing blanks from the value before concatenating it.
Formatting Data Values Customizing a Report 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; for example: >> SET STYLE VARCHAR_WIDTH 120; If you set the width to a value greater than the number of characters on a line, you should format the variable-length print items as described in, Single Items on Multiple Lines on page 4-37.
Formatting Dates and Times Customizing a Report ° • 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. Decorations in a display format specify characters to be inserted in a print item value depending on whether the value is positive, negative, zero, or too large for the field. For a general description of decorations, see Monetary Values on page 4-32.
Formatting Dates and Times Customizing a Report Date and Time Values A report can contain dates and times selected from columns of the following data types: DATETIME, DATE, TIME, or TIMESTAMP. You can also use date-time literals or expressions to generate dates and times for a report. For a complete description of date-time data types and literals, see the SQL/MP Reference Manual. You can compute a date and time by using the COMPUTE_TIMESTAMP, CURRENT_TIMESTAMP, or CURRENT function.
Formatting Dates and Times Customizing a Report Julian Timestamp Formats By using the AS DATE/TIME clause, you can specify the date format and time format of a Julian timestamp. The following command specifies a page title that includes the local date and time: S> PAGE TITLE "Supplier Parts Summary", TAB 30, +> CURRENT_TIMESTAMP AS DATE * TIME *; S> LIST N 1; Supplier Parts Summary 02/24/87 09:43:04 PM . . . . By default, the date and time are printed in these formats: M2/D2/Y2 and HP2:M2:S2.
Formatting Dates and Times Customizing a Report Table 4-2.
Formatting Dates and Times Customizing a Report The following print items produce identical formats: CURRENT DATEFORMAT (CURRENT, DEFAULT) A current date and time generated by either of these functions would appear as follows: 2000-04-15:18:22:05.61003 If you specify the following print item: DATEFORMAT (CURRENT, EUROPEAN) the date and time would appear as follows: 15.04.2000 18.22.05.61003 You can request a different number of significant digits in the seconds value.
Using TACL to Pass Parameters Customizing a Report You can use the JULIANTIMESTAMP function with a TIMESTAMP column named NEXT_MEETING: JULIANTIMESTAMP (NEXT_MEETING) AS DATE "MA DB2, Y4" To get the day of the week printed as a word such as Sunday, you can use the following print item: JULIANTIMESTAMP (CURRENT) AS DATE "DA" For a TIMESTAMP column named XX, you can use the following print item: JULIANTIMESTAMP (XX) AS DATE "DA" If your database contains Julian timestamps in a column, but you want to pri
Using TACL to Pass Parameters Customizing a Report Figure 4-17. Example of TACL Macro for Passing Parameter 1 ? TACL MACRO # FRAME 2 #SET #INLINEPREFIX // [#PUSH #INLINEPREFIX] 3 #SET #INLINEECHO 4 #PUSH RPTMONTH 5 #SET RPTMONTH %*% 6 SQLCI /INLINE/ 7 // SET LIST_COUNT 0; 8 // OBEY SELREPT; 9 // REPORT TITLE "Accounts Summary for [RPTMONTH]", SPACE, -1 [#PUSH #INLINEECHO ] // "(Printed on ", CURRENT_TIMESTAMP AS DATE *, ")" ; 10 // LIST ALL; 11 // EXIT; 12 #UNFRAME VST0417.
Conditional Printing of Items or Line Entries Customizing a Report 2. Defines the inline prefix as two slashes (//). TACL sends prefixed lines to the inline process (in this case, SQLCI) and waits until the inline process issues another prompt. 3. Sets the inline echo variable to enable echoing. 4. Defines the variable RPTMONTH. 5. Sets RPTMONTH to the parameter value entered with the RUN command (in this case, June, 2001). 6. Runs SQLCI, specifying the INLINE run option. 7.
Conditional Printing of Items or Line Entries Customizing a Report +> ELSE ("Postpone")) +> HEADING "REVIEW PLAN"; S> LIST N 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 RESE
Redefining Special Characters Customizing a Report RAYMOND JANE Sales 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-32. For another type of conditional printing, see Filler Characters on page 4-38. Note.
Calculating Totals Customizing a Report The next command sets all style options to their default values: >> RESET STYLE *; Calculating Totals You can calculate totals of print-item values. The values must be numeric. A total appears at the end of the report. For example, suppose you want to total the quantity ordered of all parts in the following report: >> >> +> +> S> S> SET LIST_COUNT 0; SELECT PARTNUM, SUM (QTY_ORDERED) FROM SALES.
Calculating Totals Customizing a Report +> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S +> WHERE P.PARTNUM = PS.PARTNUM +> AND PS.SUPPNUM = S.SUPPNUM +> AND P.PARTNUM IN (5100, 5101, 5103) +> ORDER BY S.SUPPNUM, P.PARTNUM; S> DETAIL PARTNUM, +> PARTCOST AS F8.2, +> PARTCOST * QTY_AVAILABLE NAME TOTAL_COST, +> QTY_AVAILABLE * (PRICE - PARTCOST) NAME PROFIT; +> TOTAL TOTAL_COST, PROFIT; S> LIST ALL; Figure 4-18 shows the resulting report. Figure 4-18.
Calculating Subtotals Customizing a Report Figure 4-19. Example of Formatted Total Values PARTNUM 5100 5100 5101 5103 5100 5101 5103 PARTCOST 100.00 105.00 135.00 265.00 95.00 125.00 250.00 TOTAL_COST 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.
Calculating Subtotals Customizing a Report +> +> S> +> +> +> +> +> +> +> +> S> S> +> +> +> +> S> +> S> S> S> +> +> S> S> S> S> 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.2 HEADING "Total Cost/(dollars)" CENTER NAME TOTAL_COST, QTY_AVAILABLE * (PRICE - PARTCOST) AS F11.
Calculating Subtotals Customizing a Report Figure 4-20. Example of Report With Subtotals (Page 1 of 2) Supplier Parts Summary Date: April 20, 1987 Available Part Units Number 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.
Calculating Subtotals Customizing a Report Figure 4-21. Example of 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 Supplier : 15 3103 3210 4102 5100 5101 5103 5504 5505 3300 3314 6540 3237 2400 3328 2630 3830 Unit Cost (dollars) MAGNETICS INC, 1100.00 450.00 470.00 20.00 100.00 75.00 ATTRACTIVE CORP, 19.00 105.00 135.00 265.
Calculating Subtotals Customizing a Report • • 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. You can delete all subtotal commands by entering: S> RESET REPORT SUBTOTAL; RESET REPORT BREAK also resets subtotals by deleting the specified break columns.
Calculating Subtotals Customizing a Report +> +> +> +> +> +> S> S> S> S> OD.ORDERNUM, PARTNUM, UNIT_PRICE AS F8.2 HEADING "PRICE", 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-22. Customer Orders Summary CUSTNUM ORDERNUM PARTNUM PRICE QUANTITY TOTAL PRICE 21 200320 5504 165.00 5 825.00 6201 195.
Calculating Subtotals Customizing a Report command. You can use the UNION operator to specify a select list column that contains a conditional value and can be subtotaled. In the following example, a union of three SELECT statements (shown in boldface type) retrieves the data for a report.
Printing Double-Byte Characters Customizing a Report Figure 4-23. Example of 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.
Printing Double-Byte Characters Customizing a Report This subsection describes how to avoid splitting double-byte characters during the following operations: • • • • Wrapping or folding text to a new line Truncating text using display descriptors Overlapping text when tabbing backwards Truncating subtotal labels Note. In the examples in this subsection, the values c1, c2, c3, and so forth represent doublebyte characters, and the @ and # characters represent bytes that appear incorrect.
Printing Double-Byte Characters Customizing a Report Figure 4-24. Splitting Double-Byte Characters With Text Wrapping >>SET LIST_COUNT 0; >>SELECT * FROM PERSNL.DEPT; >>S S>SET RIGHT_MARGIN 55; S>DETAIL col1, col2, tab 30, col3, tab 40 col4, col5; TAB 30 S>L F 3; TAB 40 2 spaces (default) DEPTNUM DEPTNAME MANAGER 1000 1500 2000 FINANCE PERSONNEL INVENTORY 23 213 32 RPTDEPT 9000 1000 9000 LOCATION c1c2 c1c2c3@@# c1c2c3###@@#@# >>S VST0424.
Printing Double-Byte Characters Customizing a Report Figure 4-25. Using the SPACE Clause to Adjust Display Columns >>SET LIST_COUNT 0; >>SELECT * FROM PERSNL.DEPT; >>S S>SET RIGHT_MARGIN 55; S>DETAIL col1, col2, tab 30, col3, tab 40 col4, +>SPACE 1, col5; TAB 30 S>L F 3; TAB 40 1 spaces DEPTNUM DEPTNAME MANAGER 1000 1500 2000 FINANCE PERSONNEL INVENTORY 23 213 32 RPTDEPT 9000 1000 9000 LOCATION c1c2 c1c2c3c4C5 c1c2c3c4c5c6c7 >>S VST0425.
Printing Double-Byte Characters Customizing a Report Figure 4-26. Using the TAB Clause to Adjust Display Columns >>SET LIST_COUNT 0; >>SELECT * FROM PERSNL.DEPT; >>S S>SET RIGHT_MARGIN 55; S>DETAIL col1, col2, tab 30, col3, tab 41 col4, col5; TAB 30 S>L F 3; TAB 41 2 spaces (default) DEPTNUM DEPTNAME MANAGER 1000 1500 2000 FINANCE PERSONNEL INVENTORY 23 213 32 RPTDEPT 9000 1000 9000 LOCATION c1c2 c1c2c3c4c5 c1c2c3c4c5c6c7 >>S VST0426.vsd Note.
Printing Double-Byte Characters Customizing a Report Using An Odd-Numbered Width Specification When a double-byte character column is specified with either display descriptor A or display descriptor C, and the display-width specification is an odd number for either n or w, double-byte character splitting can occur. To avoid this condition, always specify an even-numbered display descriptor for a column that contains double-byte characters.
Printing Double-Byte Characters Customizing a Report In Figure 4-28, the display descriptor, A15, defines a width that is too small, which truncates the display column in the middle of the eighth double-byte character. To adjust for this condition, the display descriptor can be specified as A16. Figure 4-28. Splitting Double-Byte Characters With Incorrectly-Sized Display Descriptors >>SET LIST_COUNT 0; >>SELECT * FROM PERSNL.
Printing Double-Byte Characters Customizing a Report Figure 4-29. Splitting Double-Byte Characters With the TAB Clause >>SET LIST_COUNT 0; >>SELECT * FROM PERSNL.DEPT; S>DETAIL _KANJI"c1c2c3c4c5c6c7", TAB 8, DEPTNAME; S>L F 7; TAB 8 DEPTNAME c1c2c3#@INANCE c1c2c3@#ERSONNEL c1c2c3@@NVENTORY c1c2c3c@HIPPING c1c2c3@#ARKETING c1c2c3##ANADA SALES c1c2c3#@ERMNY SALES S> VST0429.
Printing Double-Byte Characters Customizing a Report Figure 4-30. Splitting Characters With Different Break and Subtotal Columns >>SET LIST_COUNT 0; >>SET SUBTOTAL_LABEL _KANJI"c1c2c3c4c5c6"; >>SELECT * FROM PERSNL.EMPLOYEE; S>DETAIL TAB 20, DEPTNUM, SALARY; S>BREAK ON DEPTNUM; S>SUBTOTAL SALARY; S>L F 3; DEPTNUM Break Column Width = 7 Subtotal Strings are Truncated With Garbled Characters Subtotal Lable Break and Subtotal Defined on Different Columns SALARY 9000 175500.00 c1c2c3@ 175500.
Printing Double-Byte Characters Customizing a Report Figure 4-31. Splitting Characters With Same Break and Subtotal Columns >>SET LIST_COUNT 0; Subtotal Lable >>SET SUBTOTAL_LABEL _KANJI"c1c2c3c4c5c6"; >>SELECT * FROM PERSNL.EMPLOYEE; S>DETAIL TAB 20, SALARY HEADING "This is SALARY heading"; BREAK and SUBTOTAL S>BREAK ON SALARY; are defined S>SUBTOTAL SALARY for the same column S>L F 3; This is SALARY heading 11 print positions are reserved for 175500.00 the salary value Subtotal c1c2c3c4c5@ 175500.
Printing Double-Byte Characters Customizing a Report Figure 4-32. Using an Even-Numbered Display Descriptor for Subtotal Labels >>SET LIST_COUNT 0; >>SET SUBTOTAL_LABEL _KANJI"c1c2c3c4c5c6"; >>SELECT * FROM PERSNL.EMPLOYEE; S>DETAIL DEPTNUM AS I8, SALARY; S>BREAK ON DEPTNUM; S>SUBTOTAL SALARY; S> S>L F 3; S> DEPTNUM SALARY 9000 175500.00 c1c2c3c4 1000 175500.00 c1c2c3c4 137000.10 3000 136000.00 S> 137000.10 VST0432.
Printing Double-Byte Characters Customizing a Report Figure 4-33. Using a Correctly-Sized Display Descriptor for Subtotal Column Lables >>SET LIST_COUNT 0; >>SET SUBTOTAL_LABEL _KANJI"c1c2c3c4c5c6"; >>SELECT * FROM PERSNL.EMPLOYEE; S>DETAIL DEPTNUM AS I12, JOBCODE, SALARY; S>BREAK ON DEPTNUM; S>SUBTOTAL SALARY; S> S>L F 5; S> DEPTNUM 9000 JOBCODE SALARY 100 175500.00 c1c2c3c4c5c6 1000 175500.00 100 137000.10 c1c2c3c4c5c6 3000 S> 137000.10 100 136000.00 VST0433.
Customizing a Report Printing Double-Byte Characters HP NonStop SQL/MP Report Writer Guide—527213-001 4- 70
A Comparison of the Report Writer and the Enform Language The NonStop SQL/MP report writer is used to produce reports based on data from a NonStop SQL/MP database. Enform is a language that produces reports based on data from a database that uses Enscribe files. This appendix compares features of the two report writing tools. Figure A-1 shows a sample Enform program followed by the SQLCI commands that produce the same report. There are many similarities between the two methods for producing reports.
Comparison of the Report Writer and the Enform Language Figure A-1. Enform Report and SQLCI Report Enform Report ?DICTIONARY $MKT.DICTRY SET @SUBTOTAL-LABEL TO "SUBTOTAL"; OPEN ORDERS, ODETAIL, PARTS; LINK ORDERS TO ODETAIL VIA ORDERNUM, PARTS TO ODETAIL VIA PARTNUM; LIST BY ORDERS.ORDERNUM, PARTS.PARTNUM AS M<9,999> HEADING "PART/NUMBER", QUANTITY HEADING "QTY", PRICE, (PRICE * QUANTITY) AS M HEADING "PRICE * QTQY", SUBTOTAL, TOTAL, WHERE ORDERS.
Comparison of the Report Writer and the Enform Language Table A-1. Enform Statements Enform Statements SQLCI With Report Writer LIST SELECT, DETAIL, and LIST commands FIND SELECT and LIST commands (1) CLOSE RESET PARAM command (1) CANCEL command DECLARE No user variables, user aggregates, (2) or user tables; can use aggregates in SELECT command for grouped rows only DELINK (2) DICTIONARY VOLUME command (1) EXIT EXIT command LINK SELECT...FROM table1, table2,...WHERE LINK OPTIONAL SELECT.
Comparison of the Report Writer and the Enform Language Table A-2. Enform Clauses (page 2 of 2) Enform Clauses SQLCI With Report Writer BEFORE CHANGE BREAK FOOTING command BY and BY DESC SELECT..GROUP BY..ORDER BY..
Comparison of the Report Writer and the Enform Language Table A-3. Enform Option Variables Enform Option Variable SQLCI With Report Writer @BLANK-WHEN-ZERO AS clause with BZ modifier (2) @BREAK-KEY SET SESSION BREAK_KEY command @CENTER-PAGE CENTER_REPORT layout option @COPIES OUT_REPORT..
Comparison of the Report Writer and the Enform Language Table A-4. Enform System Variables Enform System Variable SQLCI With Report Writer @DATE CURRENT_TIMESTAMP or CURRENT function @TIME CURRENT_TIMESTAMP or CURRENT function @LINENO LINE_NUMBER function @PAGENO PAGE_NUMBER function Table A-5.
Comparison of the Report Writer and the Enform Language Note that some aggregate functions are available within the SELECT command select list for grouped values. There are no user or target aggregates. Although SQLCI and the report writer do not directly provide the capability of multilevel group aggregates or conditional aggregates, you can perform these operations using multistep queries.
Comparison of the Report Writer and the Enform Language HP NonStop SQL/MP Report Writer Guide—527213-001 A- 8
Index A C A (alphanumeric) display descriptor 4-30 Aggregate functions applied conditionally 3-33 groups of rows 3-15 using 3-16 Aggregates, multilevel and conditional A-7 Alias name 4-13 Alphanumeric comparison 3-11 ALTER DEFINE command 2-2 AND operator 3-13 Arithmetic expression, evaluation of 3-22 Arithmetic operators 3-22 AS clause display descriptors 4-62 location of 4-32 using 4-37 AS DATE/TIME clause 4-40 Asterisk, in select list 3-32 Averages, computing 3-1 AVG function 3-17 C display descriptor
D Index Columns (continued) qualified names for 3-2 selecting data from 3-5 Command file creating from log file 2-20 creating text from data with 2-20 defining report in 2-15 saving report in 2-17 Commands continuation prompt 2-1 displaying previous 2-19 Enform A-6 entering 2-10 formatting, canceling 2-11 reexecuting 2-14 report formatting 1-12 stored 2-14 Comparing character values 3-11 Comparing uppercase values 3-11 Comparison predicate example of 3-7 operators 3-7 rules 3-12 Compiling SELECT command 3
E Index Defaults (continued) report description of 4-45 example of 1-5 spacing 4-8 subtotal label 4-51 subvolume 2-1, 2-3 system 2-1, 2-2, 2-3 volume 2-2, 2-3, 2-18 Descriptor See Display descriptors Detail alias names 4-49 DETAIL command 4-12 Detail line description of 1-6 expressions in 3-22 items in 4-11 naming items 4-13 Digit display 4-31 Display descriptors I display descriptors 4-30 using 4-30 with double-byte characters 4-62 Display formats specifications for 4-30 table of 4-28 Display modifiers 4
G Index Formatting commands (continued) summary of 1-12 Format, data 4-28 FROM clause 3-3 Functions, summary of report 1-14 G GROUP BY clause computing sums or averages with 3-17 select list relation 3-16 Grouping columns choosing 3-20 select list relation 3-16 Guardian timestamp See Julian timestamp H Headings suppressing 4-16 I I display descriptor using 4-32 with double-byte characters 4-68 zero suppression with 4-35 IF/THEN/ELSE clause subtotaling, substitute method 4-56 using 4-46 Implicit correla
M Index LIST_COUNT session option 2-5 Local date and time 3-2, 4-40 Locating data 3-2 Log files content of 2-12 creating command file from 2-13 displaying current 2-13 Logical line 1-11 Logical tables 3-6, 3-26 LOGICAL_FOLDING layout option double-byte characters 4-59 using 2-6 Lowercase comparison 3-11 M M (mask) display descriptor 4-30 Macros, TACL 4-44 Major change in report 2-18 Margins layout option settings for 4-1 wider than device 2-7 Masks conditional printing of text 4-46 date format 4-44 zero
P Index Organization of data 1-1 Orphans 4-4 OUT file, displaying current 2-12 Outer query 3-29 Output device width 2-3 OUT_REPORT command 2-21 OUT_REPORT file content of 2-13 displaying current 2-2 specifying 1-4 Overflow character, single item 4-31 OVERFLOW_CHAR style option 4-38 P Page breaks 4-4 fitting lines on 4-5, 4-20 footing 1-7, 4-5 length 4-5 numbering 4-5 title 4-18 PAGE clause 4-5 PAGE FOOTING command 4-23 PAGE TITLE command 4-19 Pages footing 1-7 PAGE_LENGTH layout option 4-5 PAGE_NUMBER fu
S Index Report functions (continued) using 3-23 REPORT TITLE command 4-45 Reports creating with text editor 2-13 defining 2-15 development steps 1-3 Enform A-1 footing 1-7, 4-22 functions summary 1-14 layout and style 1-4 saving 2-16 section 2-17 subquery used with 3-30 title 1-6 RESET command 2-3 RESET LAYOUT command 2-4 RESET STYLE command 2-4 RIGHT_MARGIN layout option 4-59 Rows averaging 3-15 break groups 4-1, 4-14 counting 3-17 description of 1-1 functions applied to groups of 3-33 grouping for calcu
T Index Special characters (continued) ! commands 2-14 $, inserting in value 4-32 % wild-card character 3-12 * in select list 3-6 =_DEFAULTS DEFINE 2-2 _wild-card character 3-8 SQLCI ending 2-1 standard prompt 2-6 starting 2-1 user-defined parameters A-7 SQLCI commands, summary of 1-14 Statements, Enform A-3 Stored commands 2-14 Strings output line, insert in 4-2 print item, insert in 4-24 spacing 4-8 Style options, summary of 1-13 Subqueries considerations 3-32 IF/THEN/ELSE 4-46 IN predicate 3-9 select l
U Index U UNDERLINE_CHAR style option 4-48 Underscore wild_card character 3-8 UNION operator 4-57 Unnamed parameters 3-24 Uppercase values comparison 3-11 UPSHIFT function 3-11 USA date-time format 4-42 User-defined parameters in SELECT command 3-24 in SQLCI commands A-1 Using parameters with SELECT commands 3-23 V Values, justification of 4-29 VARCHAR data type comparison of 3-11 display on multiple lines 4-37 VARCHAR date type comparison 3-11 VARCHAR_WIDTH style option 4-37 Variable-length characters 3
Z Index HP NonStop SQL/MP Report Writer Guide—527213-001 Index -10
