| ||
| SQL | | SET, SHOW and CLEAR |
SET
ON.
CLEAR
OFF.
SHOW
Specify the name of the parameter and, optionally, any values with these commands. Certain parameters can also be set by options on the SQL execution statement.
The values of some settings are used to set column formats when a table is created. Altering these settings has no effect on existing tables. You can update the format of columns in existing tables with the column command.
Parameter List
AUTODISP
Controls whether display mode is automatically entered after a
AUTOSAVE
Only used from the menu system.
Controls whether a SAVE command is automatically executed when exiting the system.
Abbreviation: AUTOD
Default:
CLEARCASE
Enables use of the database case structure during retrieval
processing. When Case is
SET, there is an implicit CIR record
type added to the records in the FROM list of a
SELECT. Joins and aggregations are done within each case
rather than over all the records in the database. There is an implied
Join by caseid. When aggregations are performed, there is an implied
GROUP BY caseid clause on the SELECT
specification. The setting of case has no effect on caseless databases.
CLEAR disables use of the database case structure during
retrieval processing. In join operations, records are joined across all records
in the database. System and user created paths may join records within case even
with cases cleared. Aggregations occur within records or tables.
Default:
SETCASELIM n
Specifies a limit on processing cases in a
SELECT.
When the limit is reached, the process stops and prompts to continue with three
options; continue and receive a further warning after another
CASELIM number of rows are created; continue with a different
CASELIM; stop the SELECT.
CLEAR disables the check.
Abbreviation:
CLIM
Default: 1000CMPTRIM
Specifies that trailing blanks are trimmed prior to string
comparisons.
Default: SETCMPUPPER
Specifies letters are translated to uppercase prior to string
comparisons.
Default: SETCOLHEAD
Sets the printing of column headings at break points in the output of
the
DISPLAY command.
Default: SETCONTINUE '-'
Sets the character to indicate a command is continued on the next
line to the specified character. There must always be a continuation character,
CONTINUE cannot be CLEARed though it may be
set to ' ' (a blank).
Abbreviation: CONT
Default: A
hyphen ' - '.DATABASE
Sets the default database if more than one is connected.
Default:
The last database connected.DATE 'format'
Sets the default date format. Any date column without a specific
format, uses this format as a default.
CLEAR clears the
default date map. If the column comes from a database, the format described in
the schema is used as a default.
Default: 'Mmm DD, YYYY'.DEFINE_SECURITY
Specifies that tables created with
SELECT have
security definitions. When connecting to a tabfile with security definitions
(see Permissions),
specify group and user names. With
DEFINE_SECURITY set, you are the DBA (DataBase Administrator)
for the new table created with
SELECT.CLEAR specifies that created
tables are Public access.
Default: SETDETAIL
Controls whether detail lines are displayed when subtotaling is
requested with the
BREAK or GROUP commands.
CLEAR suppresses the printing of the detail lines. Subtotal
and total lines are printed.
Abbreviation: DET, DTL
Default: SETDOUBLE
Displays a blank line between rows.
CLEAR specifies
that rows are displayed single-spaced, that is with no blank lines between them.
Abbreviation: DBL
Default: CLEARDPLACES n
Sets the number of decimal places used in printing decimal numbers to
n.
CLEAR resets to the default number of decimal places.
Abbreviation: DPL
Default: 2 decimal places.ECHO
Specifies that any input from an alternate input file or from a call
procedure is echoed to the screen.
Default: CLEAREXEC
Specifies that
SELECT commands are translated and
executed after the last line of the statement is entered.
CLEAR prompts to translate and then execute the query.
Default: SETEXPONENT n
Specifies that the non-integer numeric values are displayed in scientific notation.
n is the number of decimals to display and zero (0) is used to indicate that
values are displayed in normal decimal notation.
CLEAR specifies that decimal notation is used.
Default:
CLEARFAMILY family_name[/password]
Specifies the default family and password for procedure file
references.
CLEAR clears the default family name and password.
Abbreviation: FAM
Default: Last family referencedGROUPING
Specifies that if break levels are set, the break column value is
printed the first time that the value changes. For the subsequent lines the
break column value is suppressed.
CLEAR specifies that if
break levels are displayed, the break column values are printed on every line.
Abbreviation: GRP
Default: SETGRPSIZE n
Specifies the size of the internal buffer used by the
GROUP
BY option on SELECT. If the number of different
values grouped by, is less than or equal to this value, all processing is done
in memory. If the number of entries exceeds this value, a disk sort is
performed. CLEAR specifies that the GROUP BY
in-core sort size is 1.
Default: 256.INPUT filename [NOABORT]
Specifies that input commands are read from the specified file
instead of interactively. This continues until an
END command
is encountered or end-of-file is detected. This is normally used for batch runs
and is set as a parameter on the execution statement. Use this command
interactively to run stored command sequences.If an error occurs on an input file, the processing of that file
stops at that point.
NOABORT specifies that the command in
error is written to the LOG file, and processing continues.This can be used to import an SQL
export file.
Default: Commands are read interactively.LABEL
Specifies that column headings and break titles are labels instead of
names.
CLEAR specifies that variable names are used for column
headings and break titles.
Abbreviation: LAB
Default:
CLEARLOWER
Specifies that the report produced by the
PRINT
command has mixed uppercase and lowercase characters. This is identical to
CLEAR UPPER . CLEAR LOWER specifies that the
report produced by the PRINT command is in uppercase only and
lowercase characters are translated to uppercase. This is identical to
SET UPPER.
Abbreviation: LC
Default:
SETMEMBER member_name [/password]
Specifies the default member name and password.
CLEAR specifies that there is no default
member.
Abbreviation: MEM
Default: Last member referencedMISSCHAR 'c'
Specifies the character used to display a column when it contains
missing values. Specify one character which fills the column. See also
SET NULL. CLEAR sets the character to blank.
This is equivalent to specifying SET MISSCHAR ' '.
Abbreviation: MISS
Default: Asterisk ' * 'NULL 'string'
Specifies a string displayed when a field contains missing values.
NULL takes precedence over MISSCHAR.
CLEAR clears the parameter; fields with missing values are
filled with the MISSCHAR character.
Default:
CLEAROUTPUT filename
Specifies the default print file.
CLEAR specifies
that there is no default output file and must be named on the
OUTPUT clause on PRINT or WRITE commands.
Abbreviation: OUT
Default: CLEAR.PATH
Paths are automatically used by
SELECT.
CLEAR specifies that paths are not automatically used by
SELECT and you must name a path explicitly in a
SELECT to use it. SHOW PATH [ path_name ]
As well as showing the current path parameter setting, SHOW
PATH shows the definitions of all paths or of the pathname specified.
Default: SETRECLIM n
Specifies a limit on the number of records read by a
SELECT. When the limit is reached, the process stops and
prompts whether to continue. There are three options: continuing and receiving a
further warning after another RECLIM number of records is
processed, continuing with a different RECLIM, and stopping
the SELECT at that point. CLEAR disables the
record limit option.
Abbreviation: RLIM
Default: 1000SELLIM n
Specifies a limit on the number of new rows selected by a
SELECT. When this number of new rows is reached, the process
stops and prompts whether to continue with three options; continue and receive a
further warning after another SELLIM number of rows are
created; continue with a different SELLIM; stop the
SELECT. CLEAR disables the select limit on
new rows.
Abbreviation: SLIM
Default: 1000SELSIZE n
Specifies the size of the internal buffer used for holding of the
results of a sub-query on
SELECT. If the number of different
values selected in the sub-query, is less than or equal to this value, all
processing is done in memory. If the number of entries exceeds this value, disk
I/O is performed. This parameter controls the number of entries that are kept in
memory. CLEAR specifies that the in-core size is 1.
Default: 256.SEPARATE 'string'
Specifies the string used to separate columns on the display.
CLEAR specifies that columns are not separated.
Abbreviation: SEP
Default: Two blanks.SINGLE
Detail lines are single spaced on a display. There are no blank lines
between consecutive detail lines. This parameter is changed by
SET
DOUBLE or SET TRIPLE. To see the value of all line
spacing parameters, use SHOW SPACING.
Abbreviation:
SGL
Default: SET.SPACEC n m
Specifies that there are n blank lines between the page
heading and the column headings and m blank lines between the column
headings and the underline beneath the column headings on displays.
CLEAR specifies that there are no blank lines.
Default: n =
1 , m = 0SPACED n m
Specifies that there are n blank lines between the column
heading underlining and the detail lines and m blank lines between the
detail lines and the underline prior to the subtotals on displays.
CLEAR specifies that there are no blank lines.
Default:
CLEARSPACES n m
Specifies that there are n blank lines between the underline
under the detail lines and the subtotal line and m blank lines between
the subtotal lines and the next column headings on displays.
CLEAR specifies that there are no blank lines.
Default: n
= 0 ,m = 1SPACET n m
Specifies that there are n blank lines between the subtotal
lines and the grand total underline and m blank lines between the grand
total underline and the grand total line on displays.
CLEAR
specifies that there are no blank lines.
Default: n = 0 , m = 1SRTSIZE n
Specifies the number of entries in to sort.
Sorting is done with the
ORDER BY clause on
SELECT for detail rows.
CLEAR sets the sort size to the default of 1000.STATS
Specifies that statistics are displayed automatically at the end of
execution of a
SELECT. These same statistics are displayed
whenever a SELECT reaches a CASELIM, RECLIM
or SELLIM and prompts for a decision to continue.
CLEAR suppresses the display of statistics after a
SELECT.
Default: CLEARSUBTOTAL
Specifies that subtotals are displayed when a
BREAK
or GROUP command is given for a report.
CLEAR specifies that subtotals are not automatically produced
for breaks or groups for a report.
Abbreviation: STOT
Default: SETTABFILE name
Sets the default tabfile.
CONNECTing to a tabfile
sets that tabfile to the default.
Abbreviation: TF
Default:
$SYSTEMTABSIZE n
A synonym for
SELSIZE. The use of a sub-query on
SELECT requires the holding of the results of the sub-query.
This parameter controls the size of the internal buffer. If the number of
different values selected in the sub-query, is less than or equal to this value,
all processing is done in memory. If the number of entries exceeds this value, a
disk I/O is performed. This parameter controls the number of entries that are
kept in memory. CLEAR specifies that the in-core size is 1.
Default: 256.TIME 'format'
Sets the default time format.
CLEAR specifies that
system default time format is used.
Default: 'HH:MM:SS'TOTALS 'string'
Specifies that totals are produced for displays with a break or group
option. The
'string' is a label displayed on the total line
in the left margin. If not specified, the default label is
TOTAL. CLEAR disables the total line
display.
Abbreviations: TOT, TOTAL
Default:
CLEARTRANSFER_VALLAB
Specifies whether value labels are transferred to the new table
created by select wherever they exist.
CLEAR specifies that
value labels are not transferred to the new table.
Default:
SETTRIPLE
Specifies that the detail lines are displayed triple spaced, with two
blank lines between each detail line. This parameter is altered with
SET
SINGLE and SET DOUBLE. To see the value of this
parameter, use SHOW SPACING.
Abbreviation:
TPL
Default: Detail lines are single spaced.UNDERCOL
Specifies that the column headings are underlined.
CLEAR specifies no underlining.
Abbreviations: UCOL,
UNDCOL
Default: SETUNDHEAD
Specifies that an underline is printed before a subtotal.
CLEAR specifies no underlining.
Abbreviation: UHEAD
Default: SETUPPER
Specifies that a report produced by
PRINT is in
uppercase. (Identical to CLEAR LOWER.)CLEAR
UPPER specifies that reports are in mixed lower and upper case.
(Identical to SET LOWER. )
Abbreviation: UC
Default: CLEARVALLAB
Specifies that value labels are used in place of values, for columns
with value labels defined.
CLEAR specifies that values are
used regardless of whether value labels exist.
Default:
CLEARVARCHAR n
Specifies the display size for character columns. This does not
effect the field size created on tables by
SELECT. Field size
is determined by the definition of the column or expression.
CLEAR sets the display width of character fields to zero; they
are not displayed.
Abbreviation: VCHR
Default: 20WIDTH {n | LABEL | MIN}
Sets the width of output columns. This can be the specified number
of characters (n); the width necessary to accommodate the column and value
labels (LABEL); or, for numeric fields, to the minimum number
of characters necessary to accommodate the widest number in the column
(MIN). The MIN option can only be used at
the table level in the display processor. CLEAR sets the
column widths to 8 for numeric data and to the string length specified in the
schema for strings.
Default: CLEARWORK filename [PASSWORD password]
Specifies the default workspace for SAVE and
GET. CLEAR specifies that there is no
default workspace.
Default: SirSQL.wspZEROS ON | OFF | 'string'
Specifies the printing of leading zeros for numbers.
ON means leading zeros are printed; OFF
means they are suppressed. Also specifies a string to be printed if the value is
zero. CLEAR is the same as SET ZEROS
OFF.
Abbreviation: ZERO
Default:
CLEAR