|
|
|
COLUMN
Syntax
COLUMN [ column_name
[ FORMAT " format_string " ] | [ HEADING " heading_text " ] ] ;
Description
The COLUMN statement controls how ISQL displays a column’s values (the FORMAT clause) and specifies alternative column-heading text (the HEADING clause).
The COLUMN statement without any arguments displays the current column specifications.
Arguments
column_name
The name of the column affected by the COLUMN statement. If the COLUMN statement includes column_name but omits both the FORMAT and HEADING clauses, ISQL clears any formatting and headings in effect for that column. The formatting specified for column_name also applies to DISPLAY statements that specify the same column.
FORMAT " format_string "
Specifies a quoted string that formats the display of column values. Valid values for format strings depend on the data type of the column.
Character |
The only valid format string for character data types is of the form “An”, where n specifies the width of the column display. The A character must be upper case. |
Numeric |
Table: Numeric Format Strings for the COLUMN Statement on page 3-18 shows valid format strings for numeric data types. |
Date-time |
Table: Date-Time Format Strings for the COLUMN Statement on page 3-18 shows valid format strings for date-time data types. The format strings consist of keywords that SQL interprets and replaces with formatted values. Any other character in the format string are displayed as literals. The format strings are case sensitive. For instance, SQL replaces ‘DAY’ with all uppercase letters, but follows the case of ‘Day’. Note that the SQL scalar function TO_CHAR offers comparable functionality and is not limited to SQL statements issued within ISQL. See the c-treeSQL Reference Manual for details on TO_CHAR. |
COLUMN format strings also affect display values in DISPLAY statements that specify the same column or a COMPUTE value based on the column.
HEADING “heading_text ”
Specifies an alternative heading for the column display. The default is the column name.
Format String Details
Numeric Format Strings for the COLUMN Statement
Character |
Example |
Description |
|---|---|---|
9 |
99999 |
Number of 9s specifies width. If the column value is too large to display in the specified format, ISQL displays # characters in place of the value. |
0 |
09999 |
Display leading zeroes. |
$ |
$9999 |
Prefix the display with ‘$’. |
B |
B9999 |
Display blanks if the value is zero. |
, |
99,999 |
Display a comma at position specified by the comma. |
. |
99,999.99 |
Display a decimal point at the specified position. |
MI |
99999MI |
Display ‘-’ after a negative value. |
PR |
99999PR |
Display negative values between ‘<’ and ‘>’. |
Date-Time Format Strings for the COLUMN Statement
Character |
Description |
|---|---|
CC |
The century as a 2-digit number. |
YYYY |
The year as a 4-digit number. |
YYY |
The last 3 digits of the year. |
YY |
The last 2 digits of the year. |
Y |
The last digit of the year. |
Y,YYY |
The year as a 4-digit number with a comma after the first digit. |
Q |
The quarter of the year as 1-digit number (with values 1, 2, 3, or 4). |
MM |
The month value as 2-digit number (in the range 01-12). |
MONTH |
The name of the month as a string of 9 characters (‘JANUARY’ to ‘DECEMBER ’) |
MON |
The first 3 characters of the name of the month (in the range ‘JAN’ to ‘DEC’). |
WW |
The week of year as a 2-digit number (in the range 01-52). |
W |
The week of month as a 1-digit number (in the range 1-5). |
DDD |
The day of year as a 3-digit number (in the range 001-365). |
DD |
The day of month as a 2-digit number (in the range 01-31). |
D |
The day of week as a 1-digit number (in the range 1-7, 1 for Sunday and 7 for Saturday). |
DAY |
The day of week as a 9 character string (in the range ‘SUNDAY’ to ‘SATURDAY’. |
DY |
The day of week as a 3 character string (in the range ‘SUN’ to ‘SAT’). |
J |
The Julian day (number of days since DEC 31, 1899) as an 8 digit number. |
TH |
When added to a format keyword that results in a number, this format keyword (‘TH’) is replaced by the string ‘ST’, ‘ND’, ‘RD’ or ‘TH’ depending on the last digit of the number. |
AMPM |
The string ‘AM’ or ‘PM’ depending on whether time corresponds to forenoon or afternoon. |
A.M.P.M. |
The string ‘A.M.’ or ‘P.M.’ depending on whether time corresponds to forenoon or afternoon. |
HH12 |
The hour value as a 2-digit number (in the range 00 to 11). |
HHHH24 |
The hour value as a 2-digit number (in the range 00 to 23). |
MI |
The minute value as a 2-digit number (in the range 00 to 59). |
SS |
The seconds value as a 2-digit number (in the range 00 to 59). |
SSSSS |
The seconds from midnight as a 5-digit number (in the range 00000 to 86399). |
MLS |
The milliseconds value as a 3-digit number (in the range 000 to 999). |
Examples
The following examples are based on a table, ORDERS, with columns defined as follows:
ISQL> table orders
COLNAME NULL ? TYPE LENGTH
------- ------ ---- ------
order_id NOT NULL INT 4
customer_id INT 4
steel_type CHAR 20
order_info CHAR 200
order_weight INT 4
order_value INT 4
order_state CHAR 20
ISQL displays the order_info column, at 200 characters, with lots of blank space preceding the values:
ISQL> select order_info from orders where order_value < 1000000
ORDER_INFO
----------
Solid Rods 5 in. diameter
1 record selected
You can improve formatting by using the character format string to limit the width of the display:
ISQL> column ORDER_INFO format "A28" heading "Details"
ISQL> select order_info from orders where order_value < 1000000;
ORDER_INFO
----------
Solid Rods 5 in. diameter
1 record selected
ISQL> -- Illustrate some options with numeric format strings.
ISQL> -- No column formatting:
ISQL> select order_value from orders where order_value < 1000000;
ORDER_VALUE
-----------
110000
1 record selected
ISQL> -- Format to display as money, and use different heading:
ISQL> column order_value format "$999,999,999.99" heading "Amount"
ISQL> select order_value from orders where order_value < 1000000;
AMOUNT
------
$110,000.00
1 record selected
The following examples use the single-value system table, SYSCALCTABLE, and the sysdate scalar function, to illustrate some date-time formatting. The sysdate function returns today’s date.
ISQL> select sysdate from syscalctable; -- No formatting
SYSDATE
-------
05/07/1998
ISQL> column sysdate format "Day"
ISQL> select sysdate from syscalctable
SYSDATE
-------
Thursday
1 record selected
ISQL> column sysdate format "Month"
ISQL> select sysdate from syscalctable
SYSDATE
-------
May
1 record selected
ISQL> column sysdate format "DDth"
ISQL> select sysdate from syscalctable
SYSDATE
-------
7th
1 record selected
Note: If the select-list of a query includes column titles, they override formatting specified in COLUMN statements for those columns. The following example illustrates this behavior.
ISQL> select fld from syscalctable; -- No formatting
FLD
---
100
1 record selected
ISQL> column fld heading "column title" -- Specify heading in COLUMN statement
ISQL> select fld from syscalctable;
COLUMN TITLE
------------
100
1 record selected
ISQL> select fld "new title" from syscalctable; -- Specify title in select list
NEW TITLE
---------
100
1 record selected
