SQL

Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

SQL

DISPLAY

Display

You can view the data in the current table created by SELECT. If you choose to display the data or AUTODISPLAY is ON, the table is listed.

The whole table is listed in your scrolled buffer. This has a limited size (64k bytes) but, if session logging is turned on, the output from the session is also written to the session log (SirSQL.slg).

Lines are as wide as necessary to hold all columns and no paging is done.

If you choose to alter the appearance of your table with the column formatting commands, re-issue the display command to see the altered display. The DISPLAY command displays a table and sets the table as the most recently displayed table. This table is then altered by any display commands. It is not necessary to do a SELECT command from a table before displaying it. If a specific table is not specified on the DISPLAY command, the last table used to store the result of a SELECT is displayed.

(N.B. Re-issuing a DISPLAY command re-displays the last table created by a SELECT not the last table named on a previous DISPLAY command. If you have not just done a SELECT, and do not specify a table name, you may display the result of a table created by the system as part of running the menus.)

There are commands for formatting the currently displayed table. Any format changes are held as part of the table and are permanent changes. Formatting options include:

including or excluding columns
changing the display format of columns
specifying headings or footings
specifying totals and sub-totals

There are two commands to create output files:

The PRINT command writes a file for subsequent printing. (Use an appropriate operating system command to output the file on a printer.)

The WRITE command outputs a file without headings, which may be easier if it is to be used as input to another program.

EXCLUDE and INCLUDE

EXCLUDE column_list
Specifies columns to exclude from the display. ALL BUT specifies columns to include. As many column names or numbers can be specified in the column_list as necessary. Excluded columns can be brought back with the INCLUDE command. Abbreviation: EXCL

INCLUDE column_list
Includes columns that were previously excluded with the EXCLUDE command. If no columns are specified, all excluded columns are included. ONLY specifies that the named columns are included in the display and that all other columns are excluded. Abbreviation: INCL

Formatting Commands

The formatting options specify the appearance of the displayed table. (These are identical to column options on FORMAT clauses on a SELECT command.

You can specify the command as FORMAT COLUMN or just COLUMN followed by the column references. For example, suppose a table had been created with two columns SALARY and NAME. The following display commands alter the width of these:

COLUMN  SALARY   WIDTH 8
COLUMN  NAME     WIDTH 30

The following SELECT achieves the same results as the table is created:

SELECT SALARY NAME FROM EMPLOYEE -
       FORMAT COLUMN SALARY WIDTH 8 -
       FORMAT COLUMN NAME   WIDTH 30

Headings and Footings

The following commands set the titles and headings of the overall report.

HEADING [ RIGHT | CENTER | LEFT ] heading_specifications
FOOTING [ RIGHT | CENTER | LEFT ] footing_specifications

Specifies the heading or footing of the report. By default, the first 50 characters of the SELECT statement are used as the left-justified heading.

The text can be RIGHT justified, placed in the CENTER of the report or LEFT justified. LEFT is the default.

The heading or footing can have multiple character strings plus three predefined names and positioning characters. The predefined names are DATE, TIME and PAGE. The positioning characters are "X", "T" and "/":

DATE: Specifies the current date in the heading or footing. The date format is determined by the setting of the system parameter DATE.
TIME: Specifies the current time in the heading or footing. The time format is determined by the setting of the system parameter TIME.
PAGE: Specifies the page number in the heading or footing. The page number is 1.
nX: Skips n columns before the next print position.
nT: Tabs to a particular column for the next print position.
/: Skips to a new line.

Example: Heading specification

HEADING CENTER 'Report 1'  25T 'Produced on' 2X DATE 2X PAGE

There are two other commands which can also be used to alter the headings. These are:

BTITLE  'footing string' | ERASE

Sets the report footing to the specified string. The ERASE option clears the footing line. There is no default footing. Abbreviation: BTI

TTITLE  'heading string' | ERASE

Sets the report heading to the specified string. The ERASE option resets the heading back to the default . Abbreviation: TT

Grouping and Totalling

Grouping and totalling is controlled by a number of commands:

BREAK defines the columns to be used for breakpoints.
GROUP can be used as an alternative syntax to BREAK offering the same functionality.
OFF and ON turn all totalling off, and back on again, without altering the definition of any of the totals.
SUBTOTAL and TOTAL define the type of subtotalling and totalling to be performed.

`BREAK`

BREAK column, ....[([break_heading,] [C,] [G,] [L,] [P,])],....

Specify BREAK (abbreviation BRE) to control subtotalling. When a column is designated as a break column, a break occurs for each new value in the column. The table should have been produced sorted on the break columns in order to get meaningful results.

One line of subtotals is produced for a given break level with one value for any given numeric column. By default, a total (Sum) is produced for each numeric column at each break level. Specify the type of total with the SUBTOTAL and TOTAL commands. Grand totals are also produced.

BREAK clears any previous break settings, subtotalling, and totalling. It sets the specified columns as breaks in the order specified, major to minor.

Optionally specify a break heading and the codes C, G, L and P. Enclose the options for a column or a column list in parentheses and separate multiple options with a comma. An option applies to the preceding columns. Specify a minus sign "-" in front of an option to turn off that option if it is the default.

break heading: Specifies a character string to display at the break point. The default is either the type of subtotal being produced, (Count, Sum, Max, etc.) or the column name. Column name is used if different statistics for different numeric columns at the same break level are specified.
C: Specifies that the Column headings are re-printed when this column breaks. This is the default.
G: Specifies that the column is Grouped. When a column is grouped, the first line after a break contains the new value and all subsequent lines are blank until the next break. This is the default.
L: Specifies that the subtotals are displayed in the Left margin.
P: Specifies that a Page eject is done when this column breaks.

BREAK educ ('Salary total',-C,L,P)

`GROUP`

GROUP [ EXCEPT ] [ ERASE ] ( column-list )

GROUP: Performs the same functions as BREAK with different syntax. The combination of BREAK and SUBTOTAL and of GROUP and SUBTOTAL can be used to provide identical functionality. GROUP is provided for compatibility with the SQL standard.
EXCEPT: Specifies that the break level grouping is removed on the specified columns.
ERASE: Specifies that the break level grouping is removed from all columns.

`OFF | ON`

OFF | ON

OFF: Suspends all grouping, subtotalling and totalling.
ON: Re-enables all grouping, subtotalling and totalling.

SUBTOTAL

SUBTOTAL [ COUNT | MAX | AVG | MIN | STD | SUM ]
         [ break column, ...  ] [(subtotal column, ...)]
         [ EXCEPT  ( column, ...  ) ]
         [ ERASE ]

SUBTOTAL (abbreviation STOT) sets subtotals for all numeric columns or for specified columns for a specified break column.

The settings for SUBTOTAL apply to the calculation and display of grand totals unless altered with the TOTAL command.

See the SET SPACES and SET SPACED system parameters for control of spacing around SUBTOTAL lines.

The type of subtotalling can be specified. There can only be one type of subtotalling on a given column for a break level. The options are:

COUNT: A count of the non-missing values in the column. This can be used on non-numeric columns as well as numeric.
MAX: The maximum value found in the column. This can be used on non-numeric columns as well as numeric.
AVG: The average value for non-missing occurrences in the column. This applies to numeric columns only.
MIN: The minimum value found in the column. This can be used on non-numeric columns as well as numeric.
STD: The standard deviation of the column. This applies to numeric columns only.
SUM: The total of values in the column. This applies to numeric columns only. This is the default subtotal.

Break and Subtotal Columns

Two types of columns can be specified on a SUBTOTAL command, and two formats are used to differentiate these. First, the break column(s) for which the subtotals are produced. Break columns are simply listed. Second, the column(s) that are to be subtotalled. Enclose the columns to be subtotalled in parentheses.

EXCEPT: By default, subtotalling is on for all numeric columns. EXCEPT sets subtotalling off for specified columns. EXCEPT is cumulative. Set subtotalling back on for a column by specifying it as a subtotal column. EXCEPT applies to all break columns and cannot be specified for individual break columns.
SUBTOTAL EXCEPT (ID)
ERASE: Sets subtotalling off for all columns.

Examples:

To display averages for all numeric columns when the break column EDUC changes:

SUBTOTAL AVG EDUC

To display the maximum of salary on any break:

SUBTOTAL MAX (SALARY)

To display the minimum of SALARY when the break column EDUC changes:

SUBTOTAL MIN EDUC (SALARY)

TOTAL

TOTAL    [ COUNT | MAX | AVG | MIN | STD | SUM ]
         [ total column, ...]
         [EXCEPT  ( column, ...  ) ]
         [ ERASE ]

TOTAL (abbreviation TOT) sets totals for all columns or for specified columns. Name the column(s) to total. Either enclose the column specifications in parentheses or simply list the columns. Use the SET SPACET system parameters to control spacing of total lines.

COUNT: A count of the non-missing values in the column. This can be used on non-numeric columns as well as numeric.
MAX: The maximum value found in the column. This can be used on non-numeric columns as well as numeric.
AVG: The average value for non-missing occurrences in the column. This applies to numeric columns only.
MIN: The minimum value found in the column. This can be used on non-numeric columns as well as numeric.
STD: The standard deviation of the column. This applies to numeric columns only.
SUM: The total of values in the column. This applies to numeric columns only. There can be one type of totalling on a given column. SUM is the default totalling.
EXCEPT: Totalling is on for all numeric columns. EXCEPT sets totalling off for specified columns and resets any previous specification.
ERASE: Sets totalling off for all columns.

Examples:

To produce the average salary from all records in the table:

TOTAL AVG SALARY

To produce totals for all numeric columns except CURRPOS:

TOTAL EXCEPT (CURRPOS)

PRINT and WRITE

PRINT
         [ OUTPUT filename ]
         [ LOWER | UPPER ]

WRITE
         [ OUTPUT filename ]
         [ LOWER | UPPER ]

These commands write a copy of the current displayed table to a file. PRINT writes a formatted file with column headings, underlines, blank lines, etc. This is more suitable for printing.

WRITE writes an unformatted file with a space between each column and displays a list of column positions used for each column. This may be more suitable as input to another program.

Use the Include/Exclude commands to vary the columns in the output.

OUTPUT: Abbreviation: OUT. Specifies the output filename. If OUTPUT is not specified, the current setting for the system parameter OUTPUT is used. An output file must be specified if a default has not been set.
PRINT OUTPUT 'TEST.LST'
LOWER | UPPER: Abbreviations: LC, LOWERCASE. LOWER specifies that the output uses upper and lowercase characters. This is the default. UPPER specifies that the output maps all characters to uppercase.