|
|
|
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 |
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 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.
