DB2 Server for VSE & VM: Interactive SQL Guide and Reference

FORMAT



>>-FORMAT------------------------------------------------------->
 
      .-------------------------------------------------------------------.
      V                                                                   |
>-------+--+-BTITLE-+---+--------+-------------------------------------+--+>
        |  '-TTITLE-'   +-string-+                                     |
        |               '-ERASE--'                                     |
        +-COLumn--column_id----+-DPLACes--integer--+-------------------+
        |                      +-NAME--string------+                   |
        |                      +-WIDTH--integer----+                   |
        |                      |        .-OFF--.   |                   |
        |                      '-ZEROs--+------+---'                   |
        |                               '-ON---'                       |
        +-EXCLude--+---------+--+-column_id----------------+-----------+
        |          '-ALL BUT-'  |    .--------------.      |           |
        |                       |    V              |      |           |
        |                       '-(-----column_id---+---)--'           |
        +--+-GROUP----+---+-+--------+--+-column_id----------------+-+-+
        |  +-SUBTotal-+   | '-EXCEPT-'  |    .--------------.      | | |
        |  '-TOTal----'   |             |    V              |      | | |
        |                 |             '-(-----column_id---+---)--' | |
        |                 '-ERASE------------------------------------' |
        +-INCLude--+-----------------------------------------+---------+
        |          '-+------+--+-column_id----------------+--'         |
        |            '-ONLY-'  |    .--------------.      |            |
        |                      |    V              |      |            |
        |                      '-(-----column_id---+---)--'            |
        +-NULL--string-------------------------------------------------+
        | .-ON--.                                                      |
        +-+-----+------------------------------------------------------+
        | '-OFF-'                                                      |
        |          .-ON--.                                             |
        +-OUTLINE--+-----+---------------------------------------------+
        |          '-OFF-'                                             |
        |              .-2-------.                                     |
        +-SEParator--+-+---------+---BLANKs--+-------------------------+
        |            | '-integer-'           |                         |
        |            '-string----------------'                         |
        |          .-20------.                                         |
        '-VARChar--+---------+-----------------------------------------'
                   '-integer-'
 
>--------------------------------------------------------------><

FORMAT is an ISQL display command that controls the format of the query result currently being displayed. Modifications made to the display format by a FORMAT command are reflected on the current display and the printed output obtained from any subsequent PRINT command.

You can perform formatting only on the first 45 columns of a query result.

Any formatting command containing the keywords EXCLUDE, GROUP, INCLUDE, ON/OFF, OUTLINE, SUBTOTAL, or TOTAL, causes the query to be reexecuted.
Note:You can use DBCS characters in strings or column lists.

BTITle
specifies the bottom title to be printed on reports. This bottom title is centered on the bottom line of the report. Unless specified, no bottom title is printed.

string
is the characters to use for the bottom title on a subsequent PRINT command.

The maximum length of the bottom title is that which fits on a line of the page (up to a maximum of 100 characters). If the bottom title exceeds the maximum, only those characters that fit on the line are printed.

Enclose the title in single quotation marks if it contains any blanks, but do not include any single quotation marks in the title itself.

ERASE
causes the current bottom title to be deleted, resulting in no bottom title.

If FORMAT BTITLE is issued without either ERASE or a character string, the current bottom title is displayed if it does not contain DBCS data. If the title does contain DBCS data, message ARI7970I is issued.

TTITle
specifies the top title to be printed on reports. This top title is centered on the top line of the report between the date and page number. If no top title is specified, the first 100 characters of the SELECT statement are used for the top title on printed reports.

string
is the characters to use for the top title on a subsequent PRINT command.

The maximum length of the top title is that which fits on the top line between the date and page number (to a maximum of 100 characters). If the top title exceeds the maximum, only those characters that fit between the date and page number are printed.

Enclose the title in single quotation marks if it contains any blanks, but do not include any single quotation marks in the title itself.

ERASE
causes the current top title to be deleted and the default to be used. The default TTITLE is the first 100 characters of the SELECT statement.

If FORMAT TTITLE is issued without ERASE or a character string, the current top title is displayed if it does not contain DBCS data. If the title does contain DBCS data, message ARI7970I is issued.

COLumn
provides display formatting for a particular column.

column_id
specifies the column to be formatted.

If a number n is specified, it identifies the column to be formatted as the nth column of the query result. The number refers to the position of the columns provided by the SELECT statement, not those currently displayed. Therefore, you must choose a number that represents the position of the desired column in the SELECT clause of the SELECT statement that provided the query result.

If a number is not specified, column_id refers to the current column heading of the column to be formatted. Enclose the name in single quotation marks if it either contains a blank or refers to a column with a solely numeric heading.

If the column_id represents a column being excluded, the formatting specified is performed on the excluded column although you cannot see the formatting until the column is included.

The following keywords describe how the current display is to be formatted for the column specified by column_id:

DPLACes
specifies the number of decimal places (integer) to be displayed for a numeric field. Rounding is not performed. The DPLACes must be less than the column width.

NAME
specifies a column heading to be used for the display.

string
is the actual column heading to be displayed.

A maximum of 30 characters can be used for the column heading. All characters except single quotation marks are valid. Enclose the column heading in single quotation marks if it contains a blank, but do not include any single quotation marks in the column heading itself.

WIDth
specifies the display length attribute (integer) of the column.

integer
For character type columns, the leftmost integer characters are displayed.

For numeric type columns, the leftmost integer significant digits counting the sign and decimal marker, if any, are displayed. The sign character for a positive number is a blank.

For VARCHAR type columns, characters up to the current setting of VARCHAR (using a SET or FORMAT command) are displayed. For GRAPHIC type columns, integer represents the number of DBCS characters. Two bytes are reserved for the SO/SI characters.

ZEROs
specifies whether leading zeros for numeric columns are displayed (ON) or not (OFF).

EXCLude
specifies columns to be excluded (omitted) from the display. When SQL processes a FORMAT command containing this keyword, it reexecutes the query and repositions the display to the top of the query result.

ALL BUT
indicates that all columns except those specified are excluded. For example: 
   format exclude all but (1 job 4)

includes the JOB column, and columns 1 and 4, but excludes all other columns.

column_id
specifies the column to be formatted.

If a number is specified, it identifies the column to be formatted as the nth column of the query result. The number refers to the position of the columns provided by the SELECT statement, not those currently being displayed. Therefore, you must choose a number that represents the position of the desired column in the SELECT clause of the SELECT statement that provided the query result.

If a number is not specified, column_id refers to the current column heading of the column to be formatted. Enclose the name in single quotation marks if it contains a blank or refers to a column with a solely numeric heading.

When more than one column is specified, separate the column_ids with a blank and enclose them in parentheses.

For example, the command: 

   format exclude (3 5)

causes the third and fifth column of the original position in the query result to be excluded from the display. The command: 

   format exclude job

prevents the JOB column from being displayed during the current query result. If JOB is selected two or more times, only the first occurrence of the JOB column is excluded.

GROUP

specifies the columns to outline (when outlining is on) and the columns to use for determining when subtotals are taken. Subtotals are taken whenever the values change in the columns specified. When SQL processes a FORMAT command containing this keyword, it reexecutes the query and repositions the display to the top of the query result.

SUBTotal
specifies the columns in which subtotals are to be calculated. Subtotals are taken whenever the values change in the columns being grouped. A (final) total is also provided for the columns unless otherwise specified by a FORMAT TOTAL command. When SQL processes a FORMAT command containing this keyword, it reexecutes the query and repositions the display to the top of the query result.

TOTal
specifies the columns in which (final) totals are to be calculated. If not specified, totals are provided for all columns being subtotaled. When SQL processes a FORMAT command containing this keyword, it reexecutes the query and repositions the display to the top of the query result.

EXCEPT
specifies the columns to be grouped or totalled or subtotaled except those specified by column_id.

column_id
is the name or position of each column in the query result to be grouped for outlining, subtotals, or totals.

When used with the SUBTOTAL and TOTAL keywords, column_id specifies the columns on which subtotals or totals are to be calculated.

If a number is specified, it identifies the column to be formatted as the nth column of the query result. The number refers to the position of the columns provided by the SELECT statement, not those currently displayed. Therefore, you must choose a number that represents the position of the desired column in the SELECT clause of the SELECT statement that provided the query result.

If a number is not specified, column_id refers to the current column heading of the column to be formatted. Enclose the name in single quotation marks if it contains a blank or refers to a column with a solely numeric heading.

When specifying more than one column, separate the column_ids with a blank and enclose them in parentheses.

If the column represents one that you are excluding, ISQL groups the columns using that excluded column, although you do not see the excluded column.

Subtotals or totals are calculated on the excluded column. You cannot see the subtotals or totals until the column is included. When arithmetic errors occur, the value of the column in error is calculated as zero.

The usual use of GROUP is to first order the rows of the columns that you want grouped. Use an ORDER BY clause in the SELECT statement issued to obtain the query result. The left to right ordering of the columns themselves depends on the order in which they appear in the SELECT clause of the SELECT statement.

Rows containing arithmetic errors from an outer select are displayed together at the end of the list, followed by rows containing NULL values. Rows containing arithmetic errors are displayed as asterisks (*).

ERASE
deletes all the previous specifications of GROUP for the current query result. When used with SUBTOTAL, ERASE suspends subtotals. When used with TOTAL, ERASE suspends totals.

INCLude
reverses the effect of a previous FORMAT EXCLUDE command. When SQL processes a FORMAT command containing this keyword, it reexecutes the query and repositions the display to the top of the query result.

ONLY
indicates that only those columns specified are displayed; all others are excluded.
Note:Do not use ONLY for a column name with an INCLUDE keyword. In this case, use its column number.

column_id
specifies the column to be formatted.

If a number n is specified, it identifies the column to be formatted as the nth column of the query result. The number refers to the position of the columns provided by the SELECT statement, not those currently being displayed. You must choose a number that represents the position of the desired column in the SELECT clause of the SELECT statement that provided the query result.

If a number is not specified, column_id refers to the current column heading of the column to be formatted. Enclose the name in single quotation marks if it contains a blank, or if it refers to a column with a solely numeric heading.

When more than one column is specified, separate the column IDs with a blank and enclose them in parentheses.

When arithmetic errors occur, the value of the column in error is calculated as zero.

The columns that are not mentioned in the INCLUDE command, and that are not currently being excluded, continue to participate in the display. If INCLUDE is issued by itself and with no options, all excluded columns are restored.

NULL
specifies the characters to display for null fields.

string
specifies the actual characters to display (up to a maximum of 20). If blanks are included, you must enclose the string in single quotation marks, but do not use single quotation marks in the string itself.

For example, the following command, 

   format null empty

causes the word EMPTY to be displayed for all null fields.

A question mark (?) is displayed for null values unless otherwise specified with a FORMAT or SET command.

ON

OFF
controls the status of outlining, subtotals, and totals on query results. When SQL processes a FORMAT command containing this keyword, it reexecutes the query and repositions the display to the top of the query result. Until OFF is specified, outlining, subtotals, and totals are active.

ON
permits outlining, subtotals, and totals. The ON status stays in effect for the current query result until you type FORMAT OFF.

OFF
suspends outlining, subtotals, and totals.

OUTLINE
controls the outline report format for columns specified with FORMAT GROUP. When SQL processes a FORMAT command containing this keyword, it reexecutes the query and repositions the display to the top of the query result.

If OUTLINE is not specified, outlining is performed whenever GROUP is specified unless you type FORMAT OFF.

ON
specifies that successive duplicate values in grouped columns are repeated only when they are the first line at the top of the display or at the beginning of each page on printed reports. If the first line is a subtotal line or a blank line between groups, successive duplicate values are not displayed or printed at the beginning of the next group.

OFF
specifies that successive duplicate values in grouped columns are to be displayed wherever they occur.

SEParator integer BLANKs
specifies the number of spaces (integer) to be displayed between columns. The maximum number of blanks that can be specified is 254. Unless otherwise specified with a FORMAT or SET command, the separation between columns consists of two blanks.

SEParator string
specifies the characters to be displayed between columns. If blanks are included, the string must be enclosed in single quotation marks, but do not include any single quotation marks in the separator itself.

For example, if you want a vertical line between the columns, you type: 

   format separator ' | '

which places a blank, a vertical bar, and a blank between all columns. The maximum number of characters that can be used for a separator is 254.

VARChar
specifies the display width of variable length columns.

integer
is the length desired up to 254. Unless otherwise specified with a SET command, ISQL displays only the first 20 characters of a variable-length column.

The SET command value for VARCHAR columns can be overridden for a particular query by specifying the desired value with this keyword on the FORMAT command.

When you type a FORMAT VARCHAR, the SELECT statement is reissued and you are returned to the beginning of the query result.

Example

The following FORMAT commands used during a query exclude the first column, change the name of the PRSTAFF column heading to ESTMEAN, display all projected mean staff numbers in this column with three decimal places, and display leading zeros: 

   format exclude 1
   format column prstaff name estmean
   format column estmean dplaces 3
   format column estmean zeros on
Note:If you rename a column heading, further FORMAT commands that refer to that column by name must use the new name. When referring to the column by a number, you must specify its original position in the SELECT clause of the SELECT statement.

You can specify more than one keyword in a FORMAT command. For example, the FORMAT commands described above can be expressed with a single command: 

   format exclude 1 column prstaff name estmean dplaces 3 zeros on

By using more than one keyword in a single FORMAT command, you can reduce the amount of data you must type and improve the performance of ISQL.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]