In Chapter 2, Querying Tables, you learned how to produce, display, and print query information. In this chapter, you will learn the techniques to arrange the information in a report format. You can:
To illustrate the formatting changes that you can make to columns, you first need a query result. Type the following statement:
select projno,actno,acstaff,acstaff + .25,acstdate - from proj_act - where projno = 'AD3100' - or projno = 'AD3111' - or projno = 'AD3112' - order by projno,actno
The query result for this statement (shown in Figure 19) is used over the next several topics. Do not end it until asked to do so.
Figure 19. A Query Result to Be Used to Illustrate Formatting Techniques
+--------------------------------------------------------------------------------+ | PROJNO ACTNO ACSTAFF EXPRESSION 1 ACSTDATE | | ------ ------ ------- -------------- ---------- | | AD3100 10 0.50 0.75 1982-01-01 | | AD3111 60 0.80 1.05 1982-01-01 | | AD3111 60 0.50 0.75 1982-03-15 | | AD3111 70 1.50 1.75 1982-02-15 | | AD3111 70 0.50 0.75 1982-03-15 | | AD3111 80 1.25 1.50 1982-04-15 | | AD3111 80 1.00 1.25 1982-09-15 | | AD3111 180 1.00 1.25 1982-10-15 | | AD3112 60 0.75 1.00 1982-01-01 | | AD3112 60 0.50 0.75 1982-02-01 | | AD3112 60 0.75 1.00 1982-12-01 | | AD3112 60 1.00 1.25 1983-01-01 | | AD3112 70 0.75 1.00 1982-01-01 | | AD3112 70 0.50 0.75 1982-02-01 | | AD3112 70 1.00 1.25 1982-03-15 | | AD3112 70 0.25 0.50 1982-08-15 | | AD3112 80 0.35 0.60 1982-08-15 | | AD3112 80 0.50 0.75 1982-10-15 | | AD3112 180 0.50 0.75 1982-08-15 | | * End of Result *** 19 Rows Displayed ***Cost Estimate is 1********************| +--------------------------------------------------------------------------------+
Field procedures can affect the order of the rows when you use the ORDER BY clause. For more information about field procedures, see the DB2 Server for VSE & VM SQL Reference manual.
Using the query result described above, change the number of blanks used to separate the columns by typing the following display command:
format separator 4 blanks
FORMAT is the name of the command; SEPARATOR describes the kind of formatting to be done. After this command has been processed, four blanks are displayed between columns. Now separate the columns with a vertical bar and a couple of blanks by typing the following command:
format separator ' | '
This defines a separator that consists of a blank, followed by a vertical bar, followed by a blank. The quotation marks were used to include the blanks as part of the separator. Quotation marks are needed whenever the separator you are defining contains a blank. For example, the 3-character separator:
|||
does not require quotation marks, whereas this separator does:
'| |'
The result of typing the above FORMAT command is shown in Figure 20.
Figure 20. A Query Result with a Formatted Separator between Columns
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | ACSTAFF | EXPRESSION 1 | ACSTDATE | | | ------ | ------ | ------- | -------------- | ---------- | | | AD3100 | 10 | 0.50 | 0.75 | 1982-01-01 | | | AD3111 | 60 | 0.80 | 1.05 | 1982-01-01 | | | AD3111 | 60 | 0.50 | 0.75 | 1982-03-15 | | | AD3111 | 70 | 1.50 | 1.75 | 1982-02-15 | | | AD3111 | 70 | 0.50 | 0.75 | 1982-03-15 | | | AD3111 | 80 | 1.25 | 1.50 | 1982-04-15 | | | AD3111 | 80 | 1.00 | 1.25 | 1982-09-15 | | | AD3111 | 180 | 1.00 | 1.25 | 1982-10-15 | | | AD3112 | 60 | 0.75 | 1.00 | 1982-01-01 | | | AD3112 | 60 | 0.50 | 0.75 | 1982-02-01 | | | AD3112 | 60 | 0.75 | 1.00 | 1982-12-01 | | | AD3112 | 60 | 1.00 | 1.25 | 1983-01-01 | | | AD3112 | 70 | 0.75 | 1.00 | 1982-01-01 | | | AD3112 | 70 | 0.50 | 0.75 | 1982-02-01 | | | AD3112 | 70 | 1.00 | 1.25 | 1982-03-15 | | | AD3112 | 70 | 0.25 | 0.50 | 1982-08-15 | | | AD3112 | 80 | 0.35 | 0.60 | 1982-08-15 | | | AD3112 | 80 | 0.50 | 0.75 | 1982-10-15 | | | AD3112 | 180 | 0.50 | 0.75 | 1982-08-15 | | | * End of Result ************* 19 Rows Displayed ****** Cost Estimate is 1 **| +--------------------------------------------------------------------------------+
There can be occasions when you obtain a query result and decide that it contains columns that you do not want in your report. You can end the result and retype the statement with the correct columns listed in the SELECT clause, but there is an alternative to retyping.
Suppose you want to exclude the ACSTAFF and ACSTDATE columns in the current display. Type:
format exclude (acstaff acstdate)
Issuing a FORMAT command with EXCLUDE instructs ISQL to exclude the columns specified from the current display. When specifying more than one column name, enclose them in parentheses and separate the names with a blank. The above FORMAT command displays Figure 21.
Figure 21. A Query Result Formatted to Exclude Two Columns
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | EXPRESSION 1 | | | ------ | ------ | -------------- | | | AD3100 | 10 | 0.75 | | | AD3111 | 60 | 1.05 | | | AD3111 | 60 | 0.75 | | | AD3111 | 70 | 1.75 | | | AD3111 | 70 | 0.75 | | | AD3111 | 80 | 1.50 | | | AD3111 | 80 | 1.25 | | | AD3111 | 180 | 1.25 | | | AD3112 | 60 | 1.00 | | | AD3112 | 60 | 0.75 | | | AD3112 | 60 | 1.00 | | | AD3112 | 60 | 1.25 | | | AD3112 | 70 | 1.00 | | | AD3112 | 70 | 0.75 | | | AD3112 | 70 | 1.25 | | | AD3112 | 70 | 0.50 | | | AD3112 | 80 | 0.60 | | | AD3112 | 80 | 0.75 | | | AD3112 | 180 | 0.75 | | | * End of Result ************* 19 Rows Displayed ****** Cost Estimate is 1 **| +--------------------------------------------------------------------------------+
You can use a number instead of a column name to identify the excluded column. The number refers to the column's position in the SELECT clause. For example, the ACSTAFF and ACSTDATE columns can be excluded by typing:
format exclude (3 5)
Sometimes it is easier to define columns you want included. For example, if you want to include only the third column of a query result that contained many columns, you can type:
format exclude all but (3)
The parentheses can be omitted when only one column is specified.
The effects of excluding a column from the display can be reversed. You can use the INCLUDE option to include the ACSTAFF and ACSTDATE columns in the current display. Do this by typing:
format include (acstaff acstdate)
Again, numbers can be used instead of column names. For example, to include only the first and third columns of a query result, you can type:
format include only (1 3)
The above FORMAT command displays Figure 22.
Figure 22. A Query Result Formatted for Include-Only Columns
+--------------------------------------------------------------------------------+ | PROJNO | ACSTAFF | | | ------ | ------- | | | AD3100 | 0.50 | | | AD3111 | 0.80 | | | AD3111 | 0.50 | | | AD3111 | 1.50 | | | AD3111 | 0.50 | | | AD3111 | 1.25 | | | AD3111 | 1.00 | | | AD3111 | 1.00 | | | AD3112 | 0.75 | | | AD3112 | 0.50 | | | AD3112 | 0.75 | | | AD3112 | 1.00 | | | AD3112 | 0.75 | | | AD3112 | 0.50 | | | AD3112 | 1.00 | | | AD3112 | 0.25 | | | AD3112 | 0.35 | | | AD3112 | 0.50 | | | AD3112 | 0.50 | | | * End of Result *** 19 Rows Displayed ***Cost Estimate is 1********************| +--------------------------------------------------------------------------------+
To display all the columns again, type:
format include
The query result you are using has EXPRESSION 1 as a column heading. To make the heading more meaningful, use the NAME keyword of the FORMAT command. Type the following command:
format column 'expression 1' name 'staff + .25'
The above FORMAT command displays Figure 23.
Figure 23. A Query Result Formatted to Change a Displayed Column Heading
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | ACSTAFF | STAFF + .25 | ACSTDATE | | | ------ | ------ | ------- | -------------- | ---------- | | | AD3100 | 10 | 0.50 | 0.75 | 1982-01-01 | | | AD3111 | 60 | 0.80 | 1.05 | 1982-01-01 | | | AD3111 | 60 | 0.50 | 0.75 | 1982-03-15 | | | AD3111 | 70 | 1.50 | 1.75 | 1982-02-15 | | | AD3111 | 70 | 0.50 | 0.75 | 1982-03-15 | | | AD3111 | 80 | 1.25 | 1.50 | 1982-04-15 | | | AD3111 | 80 | 1.00 | 1.25 | 1982-09-15 | | | AD3111 | 180 | 1.00 | 1.25 | 1982-10-15 | | | AD3112 | 60 | 0.75 | 1.00 | 1982-01-01 | | | AD3112 | 60 | 0.50 | 0.75 | 1982-02-01 | | | AD3112 | 60 | 0.75 | 1.00 | 1982-12-01 | | | AD3112 | 60 | 1.00 | 1.25 | 1983-01-01 | | | AD3112 | 70 | 0.75 | 1.00 | 1982-01-01 | | | AD3112 | 70 | 0.50 | 0.75 | 1982-02-01 | | | AD3112 | 70 | 1.00 | 1.25 | 1982-03-15 | | | AD3112 | 70 | 0.25 | 0.50 | 1982-08-15 | | | AD3112 | 80 | 0.35 | 0.60 | 1982-08-15 | | | AD3112 | 80 | 0.50 | 0.75 | 1982-10-15 | | | AD3112 | 180 | 0.50 | 0.75 | 1982-08-15 | | | * End of Result ************* 19 Rows Displayed ****** Cost Estimate is 1 **| +--------------------------------------------------------------------------------+
Numbers can also be used to identify the column heading to change; for example:
format column 4 name 'staff + .25'
Remember, the 4 refers to the column's position in the SELECT clause, not the position of the column displayed.
The query result on your display shows two decimal places for both the ACSTAFF and ACSTAFF + .25 columns. You control the number of decimal places displayed using the DPLACES option of the FORMAT command. For example, to display only one decimal place for the STAFF + .25 column, type:
format column 'staff + .25' dplaces 1
This displays a result similar to Figure 24.
Figure 24. A Query Result Formatted to Display One Decimal Place
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | ACSTAFF | STAFF + .25 | ACSTDATE | | | ------ | ------ | ------- | -------------- | ---------- | | | AD3100 | 10 | 0.50 | 0.7 | 1982-01-01 | | | AD3111 | 60 | 0.80 | 1.0 | 1982-01-01 | | | AD3111 | 60 | 0.50 | 0.7 | 1982-03-15 | | | AD3111 | 70 | 1.50 | 1.7 | 1982-02-15 | | | AD3111 | 70 | 0.50 | 0.7 | 1982-03-15 | | | AD3111 | 80 | 1.25 | 1.5 | 1982-04-15 | | | AD3111 | 80 | 1.00 | 1.2 | 1982-09-15 | | | AD3111 | 180 | 1.00 | 1.2 | 1982-10-15 | | | AD3112 | 60 | 0.75 | 1.0 | 1982-01-01 | | | AD3112 | 60 | 0.50 | 0.7 | 1982-02-01 | | | AD3112 | 60 | 0.75 | 1.0 | 1982-12-01 | | | AD3112 | 60 | 1.00 | 1.2 | 1983-01-01 | | | AD3112 | 70 | 0.75 | 1.0 | 1982-01-01 | | | AD3112 | 70 | 0.50 | 0.7 | 1982-02-01 | | | AD3112 | 70 | 1.00 | 1.2 | 1982-03-15 | | | AD3112 | 70 | 0.25 | 0.5 | 1982-08-15 | | | AD3112 | 80 | 0.35 | 0.6 | 1982-08-15 | | | AD3112 | 80 | 0.50 | 0.7 | 1982-10-15 | | | AD3112 | 180 | 0.50 | 0.7 | 1982-08-15 | | | * End of Result ************* 19 Rows Displayed ****** Cost Estimate is 1 **| +--------------------------------------------------------------------------------+
You can show leading zeros for a numeric column. Do this for the ACTNO column by typing:
format column actno zeros on
The above FORMAT command displays Figure 25.
Figure 25. A Query Result Formatted to Display Leading Zeros
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | ACSTAFF | STAFF + .25 | ACSTDATE | | | ------ | ------ | ------- | -------------- | ---------- | | | AD3100 | 00010 | 0.50 | 0.7 | 1982-01-01 | | | AD3111 | 00060 | 0.80 | 1.0 | 1982-01-01 | | | AD3111 | 00060 | 0.50 | 0.7 | 1982-03-15 | | | AD3111 | 00070 | 1.50 | 1.7 | 1982-02-15 | | | AD3111 | 00070 | 0.50 | 0.7 | 1982-03-15 | | | AD3111 | 00080 | 1.25 | 1.5 | 1982-04-15 | | | AD3111 | 00080 | 1.00 | 1.2 | 1982-09-15 | | | AD3111 | 00180 | 1.00 | 1.2 | 1982-10-15 | | | AD3112 | 00060 | 0.75 | 1.0 | 1982-01-01 | | | AD3112 | 00060 | 0.50 | 0.7 | 1982-02-01 | | | AD3112 | 00060 | 0.75 | 1.0 | 1982-12-01 | | | AD3112 | 00060 | 1.00 | 1.2 | 1983-01-01 | | | AD3112 | 00070 | 0.75 | 1.0 | 1982-01-01 | | | AD3112 | 00070 | 0.50 | 0.7 | 1982-02-01 | | | AD3112 | 00070 | 1.00 | 1.2 | 1982-03-15 | | | AD3112 | 00070 | 0.25 | 0.5 | 1982-08-15 | | | AD3112 | 00080 | 0.35 | 0.6 | 1982-08-15 | | | AD3112 | 00080 | 0.50 | 0.7 | 1982-10-15 | | | AD3112 | 00180 | 0.50 | 0.7 | 1982-08-15 | | | * End of Result ************* 19 Rows Displayed ****** Cost Estimate is 1 **| +--------------------------------------------------------------------------------+
Stop the display of leading zeros in the ACTNO column by typing:
format column actno zeros off
You may want to modify the displayed length attribute of a column to fit your report on the paper being used for printing. For example, to change the length attribute of the PROJNO column of the current query result, type:
format column projno width 8
The above FORMAT command displays Figure 26.
Figure 26. A Query Result Formatted to Display a Different Column Width
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | ACSTAFF | STAFF + .25 | ACSTDATE | | | -------- | ------ | ------- | -------------- | ---------- | | | AD3100 | 10 | 0.50 | 0.75 | 1982-01-01 | | | AD3111 | 60 | 0.80 | 1.05 | 1982-01-01 | | | AD3111 | 60 | 0.50 | 0.75 | 1982-03-15 | | | AD3111 | 70 | 1.50 | 1.75 | 1982-02-15 | | | AD3111 | 70 | 0.50 | 0.75 | 1982-03-15 | | | AD3111 | 80 | 1.25 | 1.50 | 1982-04-15 | | | AD3111 | 80 | 1.00 | 1.25 | 1982-09-15 | | | AD3111 | 180 | 1.00 | 1.25 | 1982-10-15 | | | AD3112 | 60 | 0.75 | 1.00 | 1982-01-01 | | | AD3112 | 60 | 0.50 | 0.75 | 1982-02-01 | | | AD3112 | 60 | 0.75 | 1.00 | 1982-12-01 | | | AD3112 | 60 | 1.00 | 1.25 | 1983-01-01 | | | AD3112 | 70 | 0.75 | 1.00 | 1982-01-01 | | | AD3112 | 70 | 0.50 | 0.75 | 1982-02-01 | | | AD3112 | 70 | 1.00 | 1.25 | 1982-03-15 | | | AD3112 | 70 | 0.25 | 0.50 | 1982-08-15 | | | AD3112 | 80 | 0.35 | 0.60 | 1982-08-15 | | | AD3112 | 80 | 0.50 | 0.75 | 1982-10-15 | | | AD3112 | 180 | 0.50 | 0.75 | 1982-08-15 | | | * End of Result ************* 19 Rows Displayed ****** Cost Estimate is 1 **| +--------------------------------------------------------------------------------+
Columns defined as variable-character have an additional command to control the displayed length attribute. See the explanation of the VARCHAR keyword in the FORMAT command description in Chapter 10, ISQL Commands.
You have now finished formatting the report. To produce a copy, type a PRINT command before typing the END command.
You can specify more than one keyword in a single FORMAT command. For example, the following command combines several keywords:
format separator ' | ' column 'expression 1' name - 'staff + .25' dplaces 1 column actno zeros off
Because ISQL treats this information as a single command, considerable processing time is saved. Multiple-keyword entry is described in more detail under Using More Than One Keyword in a FORMAT Command.
EXERCISE 3 (Answers are in Appendix A, Answers to the Exercises, page ***.)
|