HP c-tree-SQL ISQL and Tools manual Numorders Customername

Page 35

ISQL Statements

The following example also issues two more DISPLAY statements to display the variable val- ues. As before, the DISPLAY statements must specify the customer_name break. They also indent their display farther to indicate the relationship with the previously issued DISPLAY.

As before, this example uses the COLUMN and DISPLAY statements from previous exam- ples. ISQL processes DISPLAY statements in the order they were issued. Use the DISPLAY statement, without any arguments, to show the current set of DISPLAY statements in effect.

Also, in the query results, notice that the column formatting specified for the order_value col- umn carries over to the tot_value variable, which is based on order_value.

Example 3-5: Calculating Statistics on Column Breaks with COMPUTE


ISQL> compute count of order_id in					num_orders		on	customer_name
ISQL> compute sum		of order_value in tot_value					on	customer_name
ISQL> display col		10	"Total	number	of	orders:", num_orders			on customer_name;
ISQL> display	col	10	"Total	value of orders:", tot_value on customer_name;
ISQL> display	--	See all the DISPLAY				statements		currently	active:

display	col 5		"Summary of activity for"		,customer_name on customer_name
display	col	10	"Total	number of orders:" ,num_orders		on customer_name
display	col	10	"Total	value of orders:"	,tot_value	on customer_name

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
Summary of	activity		for Aerospace Enterpris
Total	number of orders:			2
Total	value of		orders:	$4,500,000.00
Chemical Constructi		Joplin		11	$3,000,000.00
Chemical Constructi		Joplin		12	$7,500,000.00
Summary of	activity		for Chemical Constructi
Total	number of orders:			2
Total	value of		orders: $10,500,000.00
Luxury Cars Inc.		North	Ridgeville	21	$6,000,000.00
Luxury Cars Inc.		North	Ridgeville	20	$5,000,000.00

Summary of activity for Luxury Cars Inc.

Total number of orders:

2

Total value of orders: $11,000,000.00

.

.

.

3.4.3Adding Beginning and Concluding Titles with the TITLE Statement

You can add some finishing touches to the query display with the TITLE statement.

FairCom Corporation

3-9

Image 35

Contents For use with c-treeSQL Server Isql and Tools Reference GuidePage Table of Contents Data Load Utility dbload Iii Tutorial Source CodeFairCom Corporation Audience Syntax Diagram ConventionsDocumentation Overview Purpose of this ManualIsql and Tools Related DocumentationOverview IntroductionIsql and Tools FairCom Corporation Isql Quick TourIntroductory Tutorial InitIsql Commit Work Custmast DefineManage Isql Create Table CustmastQuick Tour DoneComplete Introductory Tutorial Code ISQLTutorial2.sql Relational Model and Indexing TutorialQuick Tour Values Insert IntoValues Insert Into QTY Price Commit WorkComplete 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 TutorialTransaction Rollback Work Complete Transaction Processing Tutorial Source Code Arguments Isql StatementsStarting Interactive SQL SyntaxConnectstring PasswordStatement History Support UsernameStatement Summary Formatting Output of Isql QueriesIsql Statements Isql Statements for Statement History SupportDisplay Isql Statements for Query Formatting SummaryCustomername Customercity Orderid Ordervalue Example 3-1 Unformatted Query Display from IsqlExample 3-2 Controlling Display Width of Character Columns Formatting Column Display with the Column StatementCustomername Customercity Example 3-3 Customizing Format of Numeric Column DisplaysOrdervalue 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 StatementsExample Isql Reference1 @ Execute Syntax DescriptionBreakspec Break SyntaxCustomername ExamplesSkip n History Clear SyntaxBreak Column Compute Display Title ArgumentColumn Syntax OptionHeading headingtext ColumnnameFormat formatstring Character Description Date-Time Format Strings for the Column StatementChar Null ? Type Length ColnameNot Null INT Sysdate OrderinfoAmount Compute FLDColumn Title NEW TitleVariablename AVG MAX MIN SUM CountValue Define Syntax@columnname ColpositionDisplayvalue COL columnnumberEdit Syntax Exit Exit or Quit SyntaxRelated Statements Isql GET query.sql 10 GET SyntaxIsql RUN Orderstate Lotlocation Help CommandsclausesHelp Syntax History SyntaxFollowing example illustrates usage of the History statement From HostcommandHost or SH or Syntax List Syntax Isql History Quit or Exit Syntax16 RUN Syntax QuitLinesize Save Syntax18 SET Syntax PagesizePagesize numberlines TBL Connection databasename DefaultTransaction Isolation Level isolationlevel Isql Break on Isql Select TBL from Systables TBLShow Syntax Database Connections Is Default ?Spool Syntax Filename onIsql Spool OFF Start SyntaxArgument … Isql Spool STK onNot Null Varchar Null ? Type Length Colname Not Null INTDate TablenameType Length Colname Null ? Double Title SyntaxLeft Center Right COL n TextIsql Clear Title Isql and Tools FairCom Corporation Introduction Data Load Utility dbloadOptions Dbload Command Line SyntaxCommandsfile Prerequisites for DbloadData Load Utility dbload Data File FormatsNext Record Commands FileVariable Length Records Fixed Length RecordsChar Short Long Float Double Define Record StatementNull For Each StatementExamples Example 4-1 Sample dbload commands filesPosition not specified for fixed length record Compilation ErrorsRecord name redefined Fatal Errors Isql and Tools FairCom Corporation Prerequisites for Dbdump Data Unload Utility dbdumpDbdump Command Line Syntax Short Long Float Double Data Unload Utility dbdumpFor Record Statement Define Record custrec of Fixed Length 37 AS Isql and Tools FairCom Corporation Schema Export Utility dbschema Username.triggername Username.procedurenameOutfile Username.tablenameEND Schema Export Utility dbschemaDbschema Import BeginIsql and Tools FairCom Corporation Commit Work Select * from Custmast Delete from Custmast Tutorial Source CodeVARCHAR6 IntegerMoney Commit Work Delete from Custmast Tutorial Source CodeImdesc VARCHAR48 Select * from orderlist Select * from orderitems Isql and Tools FairCom Corporation Index-i SymbolsIndex-ii FairCom Corporation IsqlIndex-iii FairCom Corporation Index