Commit Work, QTY Price | HP c-tree-SQL ISQL and Tools specification

Quick Tour

INSERT INTO custmast	VALUES ('1000',	'92867', 'CA', '1',
'Bryan Williams',	'2999 Regency',	'Orange');
INSERT INTO custmast	VALUES ('1001',	'61434', 'CT', '1',
'Michael Jordan',	'13 Main', 'Harford');
INSERT INTO custmast	VALUES ('1002',	'73677', 'GA', '1',
'Joshua Brown', '4356 Cambridge',		'Atlanta');
INSERT INTO custmast	VALUES ('1003',	'10034', 'MO', '1',

'Keyon Dooling', '19771 Park Avenue', 'Columbia');

COMMIT WORK;

The following SQL statement performs the query and displays a very small report. This query uses a join of 3 tables to list quantity and price for each item of an order. A basic introduction to the report generation capabilities provided by the iSQL Utility is shown in the form of the Column, Format and Heading syntax.

COLUMN cm_name FORMAT "A15" heading "NAME"

COLUMN oi_quantity FORMAT "A10" heading "QTY"

COLUMN im_price FORMAT "$A10" heading "PRICE"


SELECT custmast.cm_name,			orderitems.oi_quantity, itemmast.im_price
FROM custmast,	orderitems,			itemmast, orderlist
WHERE orderlist.ol_custnum				= custmast.cm_custnum AND
orderlist.ol_ordernum			= orderitems.oi_ordernum AND
orderitems.oi_itemnum			= itemmast.im_itemnum
ORDER BY orderlist.ol_custnum;
The report will appear as follows:
Name	QTY	PRICE
----	---	-----
Michael Jordan	2	$19.95
Michael Jordan	1	$9.99
Michael Jordan	1	$16.59
Joshua Brown	3	$16.59

2.2.4Done

When a client application has completed operations with the server, it must release resources by disconnecting from the data- base. iSQL is an application that provides an interface for inter- active SQL. It may not be explicit but a connection is made with the server when the isql tool is launched. Likewise, a disconnect occurs when the isql tool is exited.

Below is the interactive SQL for DONE:

ISQL> quit

This will return the process back to a regular command line prompt.

FairCom Corporation

2-7

Image 17

HP c-tree-SQL ISQL and Tools manual Commit Work, QTY Price

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 Documentation Overview Syntax Diagram ConventionsPurpose of this Manual Audience Isql and Tools Related Documentation Overview Introduction Isql and Tools FairCom Corporation Introductory Tutorial Quick TourInit Isql Manage DefineIsql Create Table Custmast Isql Commit Work Custmast Quick Tour DoneComplete Introductory Tutorial Code ISQLTutorial2.sql Relational Model and Indexing Tutorial Quick Tour Values Insert IntoValues Insert Into 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 Starting Interactive SQL Isql StatementsSyntax Arguments Statement History Support PasswordUsername Connectstring Isql Statements Formatting Output of Isql QueriesIsql Statements for Statement History Support Statement Summary 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 Customername 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 Statements 1 @ Execute Syntax Isql ReferenceDescription Example Breakspec Break Syntax Customername ExamplesSkip n History Clear Syntax Column Syntax ArgumentOption Break Column Compute Display Title Heading headingtext ColumnnameFormat formatstring Character Description Date-Time Format Strings for the Column Statement Char Null ? Type Length ColnameNot Null INT Sysdate OrderinfoAmount Column Title FLDNEW Title Compute Variablename AVG MAX MIN SUM Count Value Define Syntax Displayvalue ColpositionCOL columnnumber @columnname Edit Syntax Exit Exit or Quit SyntaxRelated Statements Isql GET query.sql 10 GET Syntax Isql RUN Help Syntax Help CommandsclausesHistory Syntax Orderstate Lotlocation Following example illustrates usage of the History statement From HostcommandHost or SH or Syntax List Syntax 16 RUN Syntax Quit or Exit SyntaxQuit Isql History 18 SET Syntax Save SyntaxPagesize Linesize Pagesize numberlines Transaction Isolation Level isolationlevel Connection databasename DefaultIsql Break on Isql Select TBL from Systables TBL TBL Show Syntax Spool Syntax Is Default ?Filename on Database Connections Argument … Start SyntaxIsql Spool STK on Isql Spool OFF Not Null Varchar Null ? Type Length Colname Not Null INT Date TablenameType Length Colname Null ? Left Center Right COL n Title SyntaxText Double Isql Clear Title Isql and Tools FairCom Corporation Introduction Data Load Utility dbload Commandsfile Dbload Command Line SyntaxPrerequisites for Dbload Options Data Load Utility dbload Data File Formats Variable Length Records Commands FileFixed Length Records Next Record Char Short Long Float Double Define Record Statement Null For Each Statement Examples Example 4-1 Sample dbload commands files Position not specified for fixed length record Compilation ErrorsRecord name redefined 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 Outfile Username.procedurenameUsername.tablename Username.triggername Dbschema Schema Export Utility dbschemaImport Begin END Isql and Tools FairCom Corporation Commit Work Select * from Custmast Delete from Custmast Tutorial Source Code VARCHAR6 IntegerMoney 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