Optional SQL and DATATRIEVE formatting clauses allow you to
modify data displays or query characteristics for interactive SQL
users, DATATRIEVE users, or both. The optional formatting clauses
(QUERY NAME and EDIT STRING) and DATATRIEVE clauses (QUERY HEADER
and DEFAULT VALUE) can be used with the following statements:
o CREATE TABLE
o CREATE DOMAIN
o CREATE VIEW
o ALTER TABLE
o ALTER DOMAIN
The following diagram shows the format for these clauses:
(B)0[m[1msql-and-dtr-clause = [m
[1m [m
[1mqwq> [1;4mQUERY[m[1m [1;4mHEADER[m[1m IS qw> <quoted-string> wqqqqqqqqqqqqqqqqqqqwq> [m
[1m x mqqqqqq / <qqqqqqqqj x [m
[1m tq> [1;4mEDIT[m[1m [1;4mSTRING[m[1m IS <quoted-string> qqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x x [m
[1m tq> [1;4mQUERY[m[1m [1;4mNAME[m[1m FOR qwq> DTR qqqqqqqqwq> IS <quoted-string> qu [m
[1m x mq> DATATRIEVE qj x [m
[1m mq> [1;4mDEFAULT[m[1m [1;4mVALUE[m[1m FOR qwq> DTR qqqqqqqqwq> IS literal[m [1m qqqqj [m
[1m mq> DATATRIEVE qj [m
[1m [m
o A query header specifies a string, enclosed in quotation
marks, that interactive SQL or DATATRIEVE displays in place
of the column name when it retrieves values from a column.
Query headers allow you to specify descriptive headings for
columns.
Both interactive SQL and DATATRIEVE display any query headers
you specify in SQL definitions.
o An edit string specifies a string, enclosed in quotation
marks, that controls how interactive SQL or DATATRIEVE formats
the display of values in a column.
Both interactive SQL and DATATRIEVE use edit strings you
specify in SQL definitions to control display formatting for
those definitions.
DATATRIEVE recognizes columns with null values and displays
them according to the edit string for the missing value.
o A query name specifies a string, enclosed in quotation marks,
that you can use instead of the column name when formulating
DATATRIEVE queries. Query names are useful for abbreviating
long column names in DATATRIEVE queries.
SQL does not recognize query names in interactive queries; the
QUERY NAME clause is useful only when you use DATATRIEVE to
retrieve the data.
o If you specify a default value for a column and do not specify
that column in a DATATRIEVE STORE or MODIFY statement,
DATATRIEVE stores the default value specified in the SQL
definition.
SQL does not recognize default values in INSERT or UPDATE
statements; the DEFAULT VALUE clause is useful only when you
use DATATRIEVE STORE or MODIFY statements.
1 – QUERY HEADER Clause
The QUERY HEADER clause specifies the column header that SQL uses
in displays of result tables that contain that column.
If you include the QUERY HEADER clause, SQL uses the query header
as the column header. If you omit the clause, SQL uses the column
name as the column header.
The column header can include any character except a carriage
return, a line feed, or a control character. To include a double
quotation mark in a column header, enclose it in single quotation
marks.
The following example defines a query header for one column and a
DATATRIEVE query name for another column:
SQL> ALTER TABLE TEMP
cont> ADD STATE CHAR (2)
cont> QUERY NAME FOR DATATRIEVE IS 'ST'
cont> ADD SEX CHAR (1)
cont> QUERY HEADER IS 'S'/'E'/'X';
These statements define query headers and query names for the
STATE and SEX columns. The slash character (/) specifies that
the header is split into three lines, so the header for the SEX
column is 1 character wide, like the column itself.
Both SQL and DATATRIEVE display the query header used in this
example. Only DATATRIEVE recognizes the query name.
2 – EDIT STRING Clause
The EDIT STRING clause specifies the output format of a column
value. SQL uses the EDIT STRING clause as the default format when
writing a column value to a file or output device.
To specify the format of a column value, use a string of one
or more edit characters. Specify the edit string characters
in single quotation marks without embedded spaces. In general,
each edit character corresponds to 1 character position in the
displayed output. For example, 999999 specifies that the output
is 6 digits in 6 character positions.
To enter more of the same edit characters, shorten the edit
string by placing a repeat count in parentheses following the
edit character. For example, the edit string 9(6) is equal to
999999.
You can change the character that SQL and DATATRIEVE display for
the currency symbol ($), decimal point (.), and digit separator
(,) edit string characters.
To make your output conform to other conventions for numeric and
monetary notation, override the system defaults for these symbols
by redefining the following logical names:
o SYS$CURRENCY: Specifies the character SQL substitutes for
the dollar sign ($) edit string character. The default is a
dollar sign.
o SYS$RADIX_POINT: Specifies the character SQL substitutes for
the decimal point (.) edit string character. The default is a
decimal point.
o SYS$DIGIT_SEP: Specifies the character SQL substitutes for the
comma (,) edit string character. The default is a comma.