HP c-tree-SQL ISQL and Tools manual Formatting Column Display with the Column Statement

ISQL and Tools

3.4.1Formatting 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 state- ment. 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 3-2: 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 for- mat 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 dis- play.

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 Table 3-3: Numeric Format Strings for the COLUMN Statement on page 3-18.)

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.

3-6 FairCom Corporation