Product Documentation

FairCom ISQL

Previous Topic

Next Topic

Formatting Column Display with the COLUMN Statement

You can specify the width of the display for character columns with the COLUMN statement’s “An” format string. Specify the format string in the FORMAT clause of the COLUMN statement. You need to issue separate COLUMN statements for each column whose width you want to control in this manner.

The following example shows COLUMN statements that limit the width of the CUSTOMER_NAME and CUSTOMER_CITY columns, and re-issues the original query to show how they affect the results.

Example Controlling Display Width of Character Columns


ISQL> COLUMN CUSTOMER_NAME FORMAT "A19"

ISQL> COLUMN CUSTOMER_CITY FORMAT "A19"

ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value

from customers c, orders o

where o.customer_id = c.customer_id

order by c.customer_name;


CUSTOMER_NAME CUSTOMER_CITY ORDER_ID ORDER_VALUE

------------- ------------- -------- -----------

Aerospace Enterpris Scottsdale 13 3000000

Aerospace Enterpris Scottsdale 14 1500000

Chemical Constructi Joplin 11 3000000

Chemical Constructi Joplin 12 7500000

Luxury Cars Inc. North Ridgeville 21 6000000

Luxury Cars Inc. North Ridgeville 20 5000000


Note that ISQL truncates display at the specified width. This means you should specify a value in the FORMAT clause that accommodates the widest column value that the query will display.

To improve the formatting of the ORDER_VALUE column, use the COLUMN statement’s numeric format strings. Issue another COLUMN statement, this one for ORDER_VALUE, and specify a format string using the “$”, “9”, and “,” format-string characters:

  • The format-string character 9 indicates the width of the largest number. Specify enough 9 format-string characters to accommodate the largest value in the column.
  • The format-string character $ directs ISQL to precede column values with a dollar sign.
  • The comma (,) format-string character inserts a comma at the specified position in the display.

For the ORDER_VALUE column, the format string “$99,999,999.99” displays values in a format that clearly indicates that the values represent money. (For a complete list of the valid numeric format characters, see COLUMN.)

The following example shows the complete COLUMN statement that formats the ORDER_VALUE column. As shown by issuing the COLUMN statement without any arguments, this example retains the formatting from the COLUMN statements in the previous example.

Example Customizing Format of Numeric Column Displays

ISQL> column order_value format "$99,999,999.99"

ISQL> column; -- Show all the COLUMN statements now in effect:

column CUSTOMER_NAME format "A19" heading "CUSTOMER_NAME"

column CUSTOMER_CITY format "A19" heading "CUSTOMER_CITY"

column ORDER_VALUE format "$99,999,999.99" heading "ORDER_VALUE"

ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value

from customers c, orders o

where o.customer_id = c.customer_id

order by c.customer_name;

CUSTOMER_NAME CUSTOMER_CITY ORDER_ID ORDER_VALUE

------------- ------------- -------- -----------

Aerospace Enterpris Scottsdale 13 $3,000,000.00

Aerospace Enterpris Scottsdale 14 $1,500,000.00

Chemical Constructi Joplin 11 $3,000,000.00

Chemical Constructi Joplin 12 $7,500,000.00

Luxury Cars Inc. North Ridgeville 21 $6,000,000.00

Luxury Cars Inc. North Ridgeville 20 $5,000,000.00

.

.

.

TOCIndex