Manuals / HP / Computer Equipment / Printer

HP c-tree-SQL ISQL and Tools manual Example 3-1 Unformatted Query Display from Isql

Models: c-tree-SQL ISQL and Tools

1 99

Download 99 pages 4.83 Kb

Page 31

ISQL Statements

All the examples use the same ISQL query. The query retrieves data on outstanding customer orders. The query joins two tables, customers and orders. The examples for the TABLE state- ment on Section 3.7.13 "HOST or SH or !" on page 3-31 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 3-1: 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 defini- tions for the text columns customer_name and customer_city. ISQL displays the full col- umn 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 sum- maries for each customer. Section 3.4.2 "Summarizing Data with DISPLAY, COMPUTE, and BREAK Statements" on page 3-7 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 Section 3.4.3 "Adding Beginning and Concluding Titles with the TITLE Statement" on page 3- 9.

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.

FairCom Corporation

3-5

Image 31

HP c-tree-SQL ISQL and Tools Example 3-1 Unformatted Query Display from Isql, Customername Customercity Orderid Ordervalue

Contents

For use with c-treeSQL Server Isql and Tools Reference GuidePage Table of Contents Data Load Utility dbload Iii Tutorial Source Code FairCom Corporation Audience Syntax Diagram ConventionsDocumentation Overview Purpose of this Manual Isql and Tools Related Documentation Overview Introduction Isql and Tools FairCom Corporation Isql Quick TourIntroductory Tutorial Init Isql Commit Work Custmast DefineManage Isql Create Table Custmast Complete Introductory Tutorial Code DoneQuick Tour ISQLTutorial2.sql Relational Model and Indexing Tutorial Quick Tour Values Insert Into Insert IntoValues QTY Price Commit Work Complete Relational Model and Indexing Tutorial Source Code ISQLTutorial3.sql Locking Tutorial @ISQL @iSQLTutorial3.sql Isql Delete from Custmast Complete Locking Tutorial Source Code ISQLTutorial4.sql Transaction Processing Tutorial Transaction Rollback Work Complete Transaction Processing Tutorial Source Code Arguments Isql StatementsStarting Interactive SQL Syntax Connectstring PasswordStatement History Support Username Statement Summary Formatting Output of Isql QueriesIsql Statements Isql Statements for Statement History Support Display Isql Statements for Query Formatting Summary Customername Customercity Orderid Ordervalue Example 3-1 Unformatted Query Display from Isql Example 3-2 Controlling Display Width of Character Columns Formatting Column Display with the Column Statement Ordervalue Example 3-3 Customizing Format of Numeric Column DisplaysCustomername Customercity Example 3-4 Specifying Column Breaks and Values with Display Numorders Customername Example 3-6 Specifying a Query Header and Footer with Title Transaction Support Help and Table Statements Example Isql Reference1 @ Execute Syntax Description Breakspec Break Syntax Skip n ExamplesCustomername History Clear Syntax Break Column Compute Display Title ArgumentColumn Syntax Option Format formatstring ColumnnameHeading headingtext Character Description Date-Time Format Strings for the Column Statement Not Null INT Null ? Type Length ColnameChar Amount OrderinfoSysdate Compute FLDColumn Title NEW Title Variablename AVG MAX MIN SUM Count Value Define Syntax @columnname ColpositionDisplayvalue COL columnnumber Edit Syntax Related Statements Exit or Quit SyntaxExit Isql GET query.sql 10 GET Syntax Isql RUN Orderstate Lotlocation Help CommandsclausesHelp Syntax History Syntax Following example illustrates usage of the History statement Host or SH or Syntax HostcommandFrom List Syntax Isql History Quit or Exit Syntax16 RUN Syntax Quit Linesize Save Syntax18 SET Syntax Pagesize Pagesize numberlines TBL Connection databasename DefaultTransaction Isolation Level isolationlevel Isql Break on Isql Select TBL from Systables TBL Show Syntax Database Connections Is Default ?Spool Syntax Filename on Isql Spool OFF Start SyntaxArgument … Isql Spool STK on Not Null Varchar Null ? Type Length Colname Not Null INT Type Length Colname Null ? TablenameDate Double Title SyntaxLeft Center Right COL n Text Isql Clear Title Isql and Tools FairCom Corporation Introduction Data Load Utility dbload Options Dbload Command Line SyntaxCommandsfile Prerequisites for Dbload Data Load Utility dbload Data File Formats Next Record Commands FileVariable Length Records Fixed Length Records Char Short Long Float Double Define Record Statement Null For Each Statement Examples Example 4-1 Sample dbload commands files Record name redefined Compilation ErrorsPosition not specified for fixed length record Fatal Errors Isql and Tools FairCom Corporation Prerequisites for Dbdump Data Unload Utility dbdump Dbdump Command Line Syntax Short Long Float Double Data Unload Utility dbdump For Record Statement Define Record custrec of Fixed Length 37 AS Isql and Tools FairCom Corporation Schema Export Utility dbschema Username.triggername Username.procedurenameOutfile Username.tablename END Schema Export Utility dbschemaDbschema Import Begin Isql and Tools FairCom Corporation Commit Work Select * from Custmast Delete from Custmast Tutorial Source Code Money IntegerVARCHAR6 Commit Work Delete from Custmast Tutorial Source Code Imdesc VARCHAR48 Select * from orderlist Select * from orderitems Isql and Tools FairCom Corporation Index-i Symbols Index-ii FairCom Corporation Isql Index-iii FairCom Corporation Index