Formatting Output of ISQL Queries

Formatting of database query results makes the output of a database query more presentable and understandable. The formatted output of an ISQL database query can be either displayed on the screen, written to a file, or spooled to a printer to produce a hard copy of the report.

ISQL includes several statements that provide simple formatting of c-treeSQL queries. The following table summarizes the ISQL query-formatting statements.

Statement	Summary
DISPLAY	Displays text, variable values, and/or column values after the specified set of rows (called a break specification). See "DISPLAY" for details.
COMPUTE	Performs aggregate-function computations on column values for the specified set of rows, and assigns the results to a variable. DISPLAY statements can then refer to the variable to display its value. See "COMPUTE" for details.
BREAK	Specifies at what point ISQL processes associated DISPLAY and COMPUTE statements. BREAK statements can specify that processing occurs after a change in a column’s value, after each row, after each page, or at the end of a query. DISPLAY and COMPUTE statements have no effect until you issue a BREAK statement with the same break specification. See "BREAK" for details.
DEFINE	Defines a variable and assigns a text value to it. When DISPLAY statements refer to the variable, ISQL prints the value. See "DEFINE" for details.
COLUMN	Controls how ISQL displays a column’s values (the FORMAT clause) and/or specifies alternative column-heading text (the HEADING clause). See "COLUMN" for details.
TITLE	Specifies text and its positioning that ISQL displays before or after it processes a query. See "TITLE" for details.
CLEAR	Removes settings made by the previous DISPLAY, COMPUTE, COLUMN, BREAK, DEFINE, or TITLE statements. See "CLEAR" for details.
SET LINESIZE SET PAGESIZE SET REPORT SET ECHO	Specifies various attributes that affect how ISQL displays queries and results.

The rest of this section provides an extended example that illustrates how to use the statements together to improve formatting.

All the examples use the same ISQL query. The query retrieves data about outstanding customer orders. The query joins two tables, CUSTOMERS and ORDERS. The examples for the TABLE statement on "HOST or SH or !" show the columns and data types for these sample tables.

The following example shows the query and an excerpt of the results as ISQL displays them without the benefit of any query-formatting statements.

Example Unformatted Query Display from ISQL


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 Enterprises Inc.                             Scottsdale

                                      13      3000000

Aerospace Enterprises Inc.                             Scottsdale

                                      14      1500000

Chemical Construction Inc.                                 Joplin

                                      11      3000000

Chemical Construction Inc.                                 Joplin

                                      12      7500000

Luxury Cars Inc.                                 North Ridgeville

                                      21      6000000

Luxury Cars Inc.                                 North Ridgeville

                                      20      5000000

Although this query retrieves the correct data, the formatting is inadequate:

The display for each record wraps across two lines, primarily because of the column definitions for the text columns CUSTOMER_NAME and CUSTOMER_CITY. ISQL displays the full column width (50 characters for each column) even though the contents don’t use the entire width.
It’s not clear that the values in the ORDER_VALUE column represent money amounts.

The next section shows how to use the COLUMN statement to address these formatting issues.

In addition, you can use DISPLAY, COMPUTE, and BREAK statements to present order summaries for each customer. "Summarizing Data with DISPLAY, COMPUTE, and BREAK Statements" shows how to do this. Finally, you can add text that ISQL displays at the beginning and end of query results with the TITLE statement, as described in "Adding Beginning and Concluding Titles with the TITLE Statement".

All of these statements are independent of the actual query. You do not need to change the query in any way to control how ISQL formats the results.