HP c-tree-SQL ISQL and Tools manual Example 3-3 Customizing Format of Numeric Column Displays

Page 33

ISQL Statements

Example 3-3: Customizing Format of Numeric Column Displays

ISQL> column order_value format "$99,999,999.99"

ISQL> column; -- Show all the COLUMN statements now in effect: column CUSTOMER_NAME format "A19" heading "CUSTOMER_NAME" column CUSTOMER_CITY format "A19" heading "CUSTOMER_CITY" column ORDER_VALUE format "$99,999,999.99" heading "ORDER_VALUE"

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;

ORDER_ID

ORDER_VALUE

CUSTOMER_NAME

CUSTOMER_CITY

-------------

-------------

--------

-----------

Aerospace Enterpris

Scottsdale

13

$3,000,000.00

Aerospace Enterpris

Scottsdale

14

$1,500,000.00

Chemical Constructi

Joplin

11

$3,000,000.00

Chemical Constructi

Joplin

12

$7,500,000.00

Luxury Cars Inc.

North Ridgeville

21

$6,000,000.00

Luxury Cars Inc.

North Ridgeville

20

$5,000,000.00

.

 

 

 

.

 

 

 

.

 

 

 

3.4.2Summarizing Data with DISPLAY, COMPUTE, and BREAK State- ments

Now that the query displays the rows it returns in a more acceptable format, you can use DIS- PLAY, COMPUTE, and BREAK statements to present order summaries for each customer.

All three statements rely on a break specification to indicate to ISQL when it should perform associated processing. There are four types of breaks you can specify:

•Column breaks are processed whenever the column associated with the break changes in value

•Row breaks are processed after display of each row returned by the query

•Page breaks are processed at the end of each page (as defined by the SET PAGESIZE state- ment)

•Report breaks are processed after display of all the rows returned by the query

While DISPLAY and COMPUTE statements specify what actions ISQL takes for a particular type of break, the BREAK statement itself controls which type of break is currently in effect. A consequence of this behavior is that DISPLAY and COMPUTE statements don't take effect until you issue the BREAK statement with the corresponding break specification.

Also, keep in mind that there can be only one type of break in effect at a time. This means you can format a particular query for a single type of break.

In our example, we are interested in a column break, since we want to display an order sum- mary for each customer. In particular, we want to display the name of the customer along with the number and total value of orders for that customer. And, we want this summary displayed

FairCom Corporation

3-7

Page 32 Page 34
Image 33
Page 32 Page 34
Contents For use with c-treeSQL Server Isql and Tools Reference GuidePage Table of Contents Data Load Utility dbload Iii Tutorial Source CodeFairCom Corporation Documentation Overview Syntax Diagram ConventionsPurpose of this Manual AudienceIsql and Tools Related DocumentationOverview IntroductionIsql and Tools FairCom Corporation Introductory Tutorial Quick TourInit IsqlManage DefineIsql Create Table Custmast Isql Commit Work CustmastDone Complete Introductory Tutorial CodeQuick Tour ISQLTutorial2.sql Relational Model and Indexing TutorialQuick Tour Insert Into Values Insert IntoValues 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 Starting Interactive SQL Isql StatementsSyntax ArgumentsStatement History Support PasswordUsername ConnectstringIsql Statements Formatting Output of Isql QueriesIsql Statements for Statement History Support Statement SummaryDisplay 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 StatementExample 3-3 Customizing Format of Numeric Column Displays OrdervalueCustomername 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 Statements1 @ Execute Syntax Isql ReferenceDescription ExampleBreakspec Break SyntaxExamples Skip nCustomername History Clear SyntaxColumn Syntax ArgumentOption Break Column Compute Display TitleColumnname Format formatstringHeading headingtext Character Description Date-Time Format Strings for the Column StatementNull ? Type Length Colname Not Null INTChar Orderinfo AmountSysdate Column Title FLDNEW Title ComputeVariablename AVG MAX MIN SUM CountValue Define SyntaxDisplayvalue ColpositionCOL columnnumber @columnnameEdit Syntax Exit or Quit Syntax Related StatementsExit Isql GET query.sql 10 GET SyntaxIsql RUN Help Syntax Help CommandsclausesHistory Syntax Orderstate LotlocationFollowing example illustrates usage of the History statement Hostcommand Host or SH or SyntaxFrom List Syntax 16 RUN Syntax Quit or Exit SyntaxQuit Isql History18 SET Syntax Save SyntaxPagesize LinesizePagesize numberlines Transaction Isolation Level isolationlevel Connection databasename DefaultIsql Break on Isql Select TBL from Systables TBL TBLShow Syntax Spool Syntax Is Default ?Filename on Database ConnectionsArgument … Start SyntaxIsql Spool STK on Isql Spool OFFNot Null Varchar Null ? Type Length Colname Not Null INTTablename Type Length Colname Null ?Date Left Center Right COL n Title SyntaxText DoubleIsql Clear Title Isql and Tools FairCom Corporation Introduction Data Load Utility dbloadCommandsfile Dbload Command Line SyntaxPrerequisites for Dbload OptionsData Load Utility dbload Data File FormatsVariable Length Records Commands FileFixed Length Records Next RecordChar Short Long Float Double Define Record StatementNull For Each StatementExamples Example 4-1 Sample dbload commands filesCompilation Errors Record name redefinedPosition not specified for fixed length record 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 Outfile Username.procedurenameUsername.tablename Username.triggernameDbschema Schema Export Utility dbschemaImport Begin ENDIsql and Tools FairCom Corporation Commit Work Select * from Custmast Delete from Custmast Tutorial Source CodeInteger MoneyVARCHAR6 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
Top Page Image Contents