HP Neoview SQL Reference Manual

HP Part Number: 544582–001 Published: August 2007 Edition: HP Neoview Release 2.2

Legal Notice

Confidential computersoftware. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial

Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under

vendor’s standard commercial license.

The informationcontained hereinis subject to change without notice. Theonly warranties forHP productsand services are set forth in the express

warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP

shall not be liable for technical or editorial errors or omissions contained herein.

Export of the information contained in this publication may require authorization from the U.S. Department of Commerce.

Microsoft, Windows, and Windows NT are U.S. registered trademarks of Microsoft Corporation.

Intel, Pentium, and Celeron are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other

countries.

Java is a U.S. trademark of Sun Microsystems, Inc.

Motif, OSF/1, UNIX, X/Open, and the "X" device are registered trademarks, and IT DialTone and The Open Group are trademarks of The Open

Group in the U.S. and other countries.

Open Software Foundation, OSF, the OSF logo, OSF/1, OSF/Motif, and Motif are trademarks of the Open Software Foundation, Inc. OSF MAKES

NO WARRANTY OF ANY KIND WITH REGARD TO THE OSF MATERIAL PROVIDED HEREIN, INCLUDING, BUT NOT LIMITED TO, THE

IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESSFOR A PARTICULAR PURPOSE. OSF shall not be liable for errors contained

herein or for incidental consequential damages in connection with the furnishing, performance, or use of this material.

and documentationare basedin parton the Fourth Berkeley SoftwareDistribution underlicense from The Regents of the Universityof California.

OSF acknowledgesthe followingindividuals and institutions for their role in its development:Kenneth C.R.C. Arnold, Gregory S. Couch, Conrad

of California.

Table of Contents

About This Document.......................................................................................................23

Supported Release Version Updates (RVUs)........................................................................................23

Intended Audience................................................................................................................................23

New and Changed Information in This Edition...................................................................................23

Document Organization.......................................................................................................................24

Notation Conventions...........................................................................................................................24

General Syntax Notation.................................................................................................................24

Related Documentation........................................................................................................................26

Neoview Customer Library.............................................................................................................26

Publishing History................................................................................................................................27

HP Encourages Your Comments..........................................................................................................27

1 Introduction...................................................................................................................29

SQL Language.......................................................................................................................................29

Security.................................................................................................................................................29

User IDs and Role............................................................................................................................29

Authorization ID.............................................................................................................................29

Data Consistency and Access Options..................................................................................................30

READ UNCOMMITTED ................................................................................................................30

READ COMMITTED ......................................................................................................................30

SERIALIZABLE or REPEATABLE READ.......................................................................................31

SKIP CONFLICT..............................................................................................................................31

Database Integrity and Locking............................................................................................................31

Lock Duration..................................................................................................................................31

Lock Granularity..............................................................................................................................31

Lock Mode.......................................................................................................................................31

Lock Holder.....................................................................................................................................32

Transaction Management......................................................................................................................32

Statement Atomicity........................................................................................................................32

User-Defined and System-Defined Transactions............................................................................33

User-Defined Transactions.........................................................................................................33

System-Defined Transactions.....................................................................................................33

Rules for DML Statements...............................................................................................................33

Effect of AUTOCOMMIT Option....................................................................................................33

Concurrency....................................................................................................................................33

Transaction Access Modes...............................................................................................................35

READ ONLY...............................................................................................................................35

READ WRITE.............................................................................................................................36

Transaction Isolation Levels............................................................................................................36

READ UNCOMMITTED............................................................................................................36

READ COMMITTED..................................................................................................................36

SERIALIZABLE or REPEATABLE READ .................................................................................36

Transaction Rollback Mode.............................................................................................................37

ANSI Compliance and Neoview SQL Extensions................................................................................37

ANSI-Compliant Statements...........................................................................................................37

Statements That Are Neoview SQL Extensions..............................................................................38

ANSI-Compliant Functions.............................................................................................................38

Neoview SQL Error Messages..............................................................................................................39

Table of Contents 3

2 SQL Statements.............................................................................................................41

Categories.............................................................................................................................................41

Data Definition Language (DDL) Statements.................................................................................41

Data Manipulation Language (DML) Statements...........................................................................42

Transaction Control Statements.......................................................................................................42

Resource Control and Optimization Statements.............................................................................42

Control Statements..........................................................................................................................43

Object Naming Statements..............................................................................................................43

Trigger Statements...........................................................................................................................43

Stored Procedure Statements...........................................................................................................43

ALTER MATERIALIZED VIEW Statement..........................................................................................44

Syntax Description of ALTER MATERIALIZED VIEW..................................................................44

Considerations for ALTER MATERIALIZED VIEW.......................................................................44

ALTER MVGROUP Statement..............................................................................................................45

Considerations for ALTER MVGROUP..........................................................................................45

Example of ALTER MVGROUP......................................................................................................45

ALTER SYNONYM Statement..............................................................................................................46

Syntax Description of ALTER SYNONYM .....................................................................................46

Considerations.................................................................................................................................46

Versioning Considerations..............................................................................................................46

Example of ALTER SYNONYM......................................................................................................46

ALTER TABLE Statement.....................................................................................................................47

Syntax Description of ALTER TABLE.............................................................................................47

Considerations for ALTER TABLE..................................................................................................49

Effect of Adding a Column on View Definitions.......................................................................49

Authorization and Availability Requirements...........................................................................49

Example of ALTER TABLE..............................................................................................................49

ALTER TRIGGER Statement.................................................................................................................50

Syntax Description of ALTER TRIGGER.........................................................................................50

Considerations for ALTER TRIGGER.............................................................................................50

Authorization and Availability Requirements...........................................................................50

ALTER VIEW Statement.......................................................................................................................51

Syntax Description of ALTER VIEW...............................................................................................51

Example of ALTER VIEW................................................................................................................51

BEGIN WORK Statement.....................................................................................................................52

Examples of BEGIN WORK............................................................................................................52

CALL Statement....................................................................................................................................53

Syntax Description of CALL............................................................................................................53

Considerations for CALL................................................................................................................53

Usage Restrictions......................................................................................................................53

Required Privileges....................................................................................................................53

Input Parameter Arguments......................................................................................................54

Output Parameter Arguments...................................................................................................54

Data Conversion of Parameter Arguments................................................................................54

Null Input and Output...............................................................................................................54

Transaction Semantics................................................................................................................54

Examples of CALL...........................................................................................................................54

COMMIT WORK Statement.................................................................................................................56

Considerations for COMMIT WORK..............................................................................................56

Begin and End a Transaction......................................................................................................56

Effect of Constraints...................................................................................................................56

Example of COMMIT WORK..........................................................................................................56

CREATE INDEX Statement...................................................................................................................58

Syntax Description of CREATE INDEX..........................................................................................58

4 Table of Contents

Considerations for CREATE INDEX...............................................................................................59

Authorization and Availability Requirements...........................................................................59

Limits on Indexes.......................................................................................................................59

Example of CREATE INDEX...........................................................................................................59

CREATE MATERIALIZED VIEW Statement........................................................................................60

Syntax Description of CREATE MATERIALIZED VIEW................................................................60

Considerations for CREATE MATERIALIZED VIEW....................................................................62

Types of Materialized Views...........................................................................................................62

Authorization..................................................................................................................................62

Materialized Views Clustering........................................................................................................63

Materialized Views Using Other Materialized Views.....................................................................63

Single Delta Materialized Views.....................................................................................................63

DML Operations on Materialized Views........................................................................................63

Indexes and Materialized Views.....................................................................................................63

Joins.................................................................................................................................................64

Restrictions for CREATE MATERIALIZED VIEW..........................................................................64

MJV (Materialized Join View)....................................................................................................64

MAV (Materialized Aggregate View) on a Single Table............................................................64

DML Operations on Materialized Views...................................................................................64

Example of CREATE MATERIALIZED VIEW................................................................................64

CREATE MVGROUP Statement...........................................................................................................66

Example of CREATE MVGROUP....................................................................................................66

CREATE SCHEMA Statement..............................................................................................................67

Syntax Description of CREATE SCHEMA......................................................................................67

Considerations for CREATE SCHEMA...........................................................................................67

Reserved Schema Names...........................................................................................................67

Example of CREATE SCHEMA.......................................................................................................67

CREATE SYNONYM Statement...........................................................................................................68

Syntax Description of CREATE SYNONYM ..................................................................................68

Considerations.................................................................................................................................68

Versioning Considerations..............................................................................................................68

Example of CREATE SYNONYM....................................................................................................68

CREATE TABLE Statement...................................................................................................................69

Syntax Description of CREATE TABLE...........................................................................................71

Considerations for CREATE TABLE................................................................................................73

Considerations for CREATE VOLATILE TABLE............................................................................74

Considerations for CREATE TABLE LIKE......................................................................................74

Reserved Table Names.....................................................................................................................74

Authorization and Availability Requirements................................................................................74

Limits for Tables..............................................................................................................................74

Calculating Row Size.......................................................................................................................75

Creating Partitions Automatically...................................................................................................75

Record Format.................................................................................................................................75

Generating Unique Values For a Column.......................................................................................75

Neoview SQL Extensions to CREATE TABLE................................................................................77

DISK POOL......................................................................................................................................77

Considerations for DISK POOL.................................................................................................77

Restrictions for DISK POOL.......................................................................................................78

Examples of CREATE TABLE..........................................................................................................78

Examples of CREATE TABLE AS....................................................................................................79

CREATE TRIGGER Statement..............................................................................................................81

Syntax Description of CREATE TRIGGER......................................................................................81

Considerations for CREATE TRIGGER...........................................................................................82

Authorization and Availability Requirements...........................................................................82

Trigger Types..............................................................................................................................82

Table of Contents 5

Restrictions on Triggers..............................................................................................................84

Recompilation and Triggers.......................................................................................................84

Triggers and Primary Keys........................................................................................................84

Examples of CREATE TRIGGER.....................................................................................................85

Before and After Triggers...........................................................................................................85

CREATE VIEW Statement.....................................................................................................................87

Syntax Description of CREATE VIEW.............................................................................................87

Considerations for CREATE VIEW.................................................................................................88

Reserved View Names...............................................................................................................88

Effect of Adding a Column on View Definitions.......................................................................88

Authorization and Availability Requirements...........................................................................88

Updatable and Non-Updatable Views.......................................................................................88

Examples of CREATE VIEW............................................................................................................89

DELETE Statement................................................................................................................................90

Syntax Description of DELETE........................................................................................................90

Considerations for DELETE............................................................................................................92

Authorization Requirements......................................................................................................92

Transaction Initiation and Termination......................................................................................92

Isolation Levels of Transactions and Access Options of Statements..........................................92

SET ON ROLLBACK Considerations........................................................................................92

SET ON ROLLBACK Restrictions..............................................................................................92

Examples of DELETE.......................................................................................................................92

DROP INDEX Statement.......................................................................................................................93

Syntax Description of DROP INDEX..............................................................................................93

Considerations for DROP INDEX...................................................................................................93

Authorization and Availability Requirements...........................................................................93

Indexes That Support Constraints.............................................................................................93

Example of DROP INDEX...............................................................................................................93

DROP MATERIALIZED VIEW Statement............................................................................................94

Syntax Description of DROP MATERIALIZED VIEW....................................................................94

Example of DROP MATERIALIZED VIEW....................................................................................94

DROP MVGROUP Statement...............................................................................................................95

Considerations for DROP MVGROUP............................................................................................95

Example of DROP MVGROUP........................................................................................................95

DROP SCHEMA Statement..................................................................................................................96

Syntax Description of DROP SCHEMA..........................................................................................96

Considerations for DROP SCHEMA...............................................................................................96

Authorization and Availability Requirements...........................................................................96

Example of DROP SCHEMA...........................................................................................................96

DROP SYNONYM Statement...............................................................................................................97

Syntax Description of DROP SYNONYM ......................................................................................97

Considerations.................................................................................................................................97

Versioning Considerations..............................................................................................................97

Example of DROP SYNONYM........................................................................................................97

DROP TABLE Statement.......................................................................................................................98

Syntax Description of DROP TABLE...............................................................................................98

Considerations for DROP TABLE...................................................................................................98

Authorization and Availability Requirements...........................................................................98

Example of DROP TABLE...............................................................................................................98

DROP TRIGGER Statement..................................................................................................................99

Syntax Description of DROP TRIGGER..........................................................................................99

Considerations for DROP TRIGGER...............................................................................................99

Authorization and Availability Requirements...........................................................................99

Examples of DROP TRIGGER.........................................................................................................99

DROP VIEW Statement.......................................................................................................................100

6 Table of Contents

Syntax Description of DROP VIEW...............................................................................................100

Considerations for DROP VIEW...................................................................................................100

Authorization and Availability Requirements.........................................................................100

Example of DROP VIEW...............................................................................................................100

EXECUTE Statement...........................................................................................................................101

Syntax Description of EXECUTE...................................................................................................101

Considerations for EXECUTE........................................................................................................102

Scope of EXECUTE...................................................................................................................102

Examples of EXECUTE..................................................................................................................102

EXPLAIN Statement............................................................................................................................104

Syntax.............................................................................................................................................104

Considerations...............................................................................................................................105

Case Considerations.................................................................................................................105

Number Considerations...........................................................................................................105

Formatted [OPTIONS 'f'] Considerations................................................................................105

Normal User [OPTIONS 'n'] Considerations...........................................................................106

Expert User [OPTIONS 'e'] Considerations..............................................................................108

Machine-Readable [OPTIONS 'm'] Considerations.................................................................110

Example of EXPLAIN Statement Using ODBC........................................................................112

Example of EXPLAIN Statement Using JDBC.........................................................................113

GRANT Statement..............................................................................................................................115

Syntax Description of GRANT......................................................................................................115

Considerations for GRANT...........................................................................................................116

Authorization and Availability Requirements.........................................................................116

Privileges on Views..................................................................................................................116

Examples of GRANT.....................................................................................................................116

GRANT EXECUTE Statement.............................................................................................................117

Syntax Description of GRANT EXECUTE.....................................................................................117

Considerations for GRANT EXECUTE.........................................................................................117

Authorization and Availability Requirements.........................................................................117

Examples of GRANT EXECUTE....................................................................................................118

GRANT SCHEMA Statement.............................................................................................................119

Syntax Description of GRANT SCHEMA.....................................................................................119

Considerations for GRANT SCHEMA..........................................................................................120

Authorization and Availability Requirements.........................................................................120

Schema Privileges and Synonyms............................................................................................120

Examples of GRANT SCHEMA....................................................................................................120

INSERT Statement...............................................................................................................................121

Syntax Description of INSERT.......................................................................................................121

Considerations for INSERT...........................................................................................................123

Authorization Requirements....................................................................................................123

Transaction Initiation and Termination....................................................................................123

Self-Referencing INSERT and BEGIN WORK or AUTOCOMMIT OFF..................................123

Isolation Levels of Transactions and Access Options of Statements........................................124

Use of a VALUES Clause for the Source Query Expression....................................................124

Requirements for Inserted Rows..............................................................................................124

Using Compatible Data Types..................................................................................................124

Examples of INSERT......................................................................................................................125

LOCK TABLE Statement.....................................................................................................................127

Syntax Description of LOCK TABLE.............................................................................................127

Considerations for LOCK TABLE..................................................................................................127

Authorization Requirements....................................................................................................127

Modifying Default Locking......................................................................................................127

Effect of AUTOCOMMIT Option.............................................................................................127

Indexes......................................................................................................................................128

Table of Contents 7

Examples of LOCK TABLE............................................................................................................128

MERGE INTO Statement....................................................................................................................129

Syntax Description of MERGE INTO............................................................................................129

Considerations for MERGE INTO.................................................................................................129

Upsert Using Single Row.........................................................................................................129

Restrictions..........................................................................................................................130

Upsert Using Rowsets..............................................................................................................130

Example...............................................................................................................................130

MERGE From One Table Into Another....................................................................................130

Example...............................................................................................................................131

Reflexive Updates...............................................................................................................131

PREPARE Statement...........................................................................................................................132

Syntax Description of PREPARE...................................................................................................132

Considerations for PREPARE........................................................................................................132

Availability of a Prepared Statement........................................................................................132

Examples of PREPARE..................................................................................................................132

REVOKE Statement.............................................................................................................................134

Syntax Description of REVOKE.....................................................................................................134

Considerations for REVOKE.........................................................................................................135

Authorization and Availability Requirements.........................................................................135

Examples of REVOKE....................................................................................................................135

REVOKE EXECUTE Statement...........................................................................................................136

Syntax Description of REVOKE EXECUTE...................................................................................136

Considerations for REVOKE EXECUTE........................................................................................137

Authorization and Availability Requirements.........................................................................137

Examples of REVOKE EXECUTE..................................................................................................137

REVOKE SCHEMA Statement............................................................................................................138

Syntax Description of REVOKE SCHEMA....................................................................................138

Considerations for REVOKE SCHEMA........................................................................................139

Authorization and Availability Requirements.........................................................................139

Examples of REVOKE SCHEMA...................................................................................................139

ROLLBACK WORK Statement...........................................................................................................140

Considerations for ROLLBACK WORK........................................................................................140

Begin and End a Transaction....................................................................................................140

Examples of ROLLBACK WORK..................................................................................................140

SELECT Statement..............................................................................................................................141

Syntax Description of SELECT......................................................................................................144

Considerations for SELECT...........................................................................................................151

Authorization Requirements....................................................................................................151

Locking Modes.........................................................................................................................151

Use of Views With SELECT......................................................................................................151

Join Limits.................................................................................................................................152

Object Names in SELECT.........................................................................................................152

AS and ORDER BY Conflicts....................................................................................................152

Stream Access Restrictions.......................................................................................................152

Restrictions on Embedded Inserts............................................................................................152

DISTINCT Aggregate Functions..............................................................................................152

Limitations of DISTINCT Aggregates......................................................................................153

Examples of Multiple Distinct Aggregates..............................................................................153

Considerations for Select List........................................................................................................153

Considerations for SEQUENCE BY...............................................................................................153

Considerations for GROUP BY......................................................................................................153

Considerations for SORT BY.........................................................................................................154

Considerations for ORDER BY......................................................................................................154

Considerations for DISTINCT.......................................................................................................154

8 Table of Contents

Considerations for UNION...........................................................................................................154

Characteristics of the UNION Columns..................................................................................154

ORDER BY Clause and the UNION Operator.........................................................................155

GROUP BY Clause, HAVING Clause, and the UNION Operator ..........................................155

UNION ALL and Associativity................................................................................................155

Access Modes and the UNION Operator.................................................................................155

Examples of SELECT.....................................................................................................................156

Examples for Embedded INSERT..................................................................................................161

SET SCHEMA Statement....................................................................................................................163

Syntax Description of SET SCHEMA............................................................................................163

Consideration for SET SCHEMA...................................................................................................163

Examples of SET SCHEMA...........................................................................................................163

SET TABLE TIMEOUT Statement.......................................................................................................164

Syntax Description of SET TABLE TIMEOUT...............................................................................164

Considerations for SET TABLE TIMEOUT....................................................................................165

Examples of SET TABLE TIMEOUT..............................................................................................165

SET TRANSACTION Statement.........................................................................................................166

Syntax Description of SET TRANSACTION.................................................................................166

Considerations for SET TRANSACTION......................................................................................167

Implicit Transactions................................................................................................................167

Explicit Transactions.................................................................................................................168

Degree of Concurrency.............................................................................................................168

Effect on Utilities......................................................................................................................168

Example of SET TRANSACTION..................................................................................................168

UPDATE Statement.............................................................................................................................169

Syntax Description of UPDATE.....................................................................................................169

Considerations for UPDATE..........................................................................................................171

Authorization Requirements....................................................................................................171

Transaction Initiation and Termination....................................................................................171

Isolation Levels of Transactions and Access Options of Statements........................................171

Conflicting Updates in Concurrent Applications....................................................................171

Requirements for Data in Row.................................................................................................172

Reporting of Updates...............................................................................................................172

Updating Character Values......................................................................................................172

SET Clause Restrictions and Error Cases.................................................................................172

SET ON ROLLBACK Considerations......................................................................................173

SET ON ROLLBACK Restrictions............................................................................................173

Examples of UPDATE....................................................................................................................173

UPDATE STATISTICS Statement........................................................................................................175

Syntax Description of UPDATE STATISTICS................................................................................175

Considerations for UPDATE STATISTICS.....................................................................................177

Physical Statistics......................................................................................................................177

Using Statistics.........................................................................................................................177

Authorization and Locking......................................................................................................177

Transactions..............................................................................................................................177

Generating and Clearing Statistics for Columns......................................................................178

Column Lists and Access Plans................................................................................................178

Examples of UPDATE STATISTICS...............................................................................................178

3 SQL Utilities................................................................................................................181

Privileges Required to Execute Utilities .............................................................................................181

MAINTAIN Command.......................................................................................................................182

Syntax Description of MAINTAIN................................................................................................182

........................................................................................................................................................182

Table of Contents 9

MAINTAIN MVGROUP................................................................................................................184

Examples of MAINTAIN...............................................................................................................184

POPULATE INDEX Utility.................................................................................................................186

Syntax Description of POPULATE INDEX....................................................................................186

Considerations for POPULATE INDEX........................................................................................186

Examples of POPULATE INDEX...................................................................................................186

PURGEDATA Utility...........................................................................................................................187

Syntax Description of PURGEDATA.............................................................................................187

Considerations for PURGEDATA..................................................................................................187

Examples of PURGEDATA............................................................................................................187

4 SQL Language Elements...........................................................................................189

Character Sets......................................................................................................................................190

Columns..............................................................................................................................................191

Column References........................................................................................................................191

Derived Column Names................................................................................................................191

Column Default Settings................................................................................................................191

Examples of Derived Column Names...........................................................................................191

Constraints..........................................................................................................................................193

Creating, Adding, and Dropping Constraints on SQL Tables.......................................................193

Constraint Names.....................................................................................................................193

Correlation Names..............................................................................................................................194

Explicit Correlation Names...........................................................................................................194

Implicit Correlation Names...........................................................................................................194

Examples of Correlation Names....................................................................................................194

Database Objects.................................................................................................................................195

Ownership.....................................................................................................................................195

Database Object Names......................................................................................................................196

Logical Names for SQL Objects.....................................................................................................196

SQL Object Namespaces................................................................................................................196

Data Types...........................................................................................................................................197

Comparable and Compatible Data Types.....................................................................................199

Character Data Types...............................................................................................................199

Datetime Data Types................................................................................................................199

Interval Data Types...................................................................................................................200

Numeric Data Types.................................................................................................................200

Character String Data Types..........................................................................................................200

Considerations for Character String Data Types.....................................................................201

Datetime Data Types......................................................................................................................202

Considerations for Datetime Data Types.................................................................................202

Interval Data Types.............................................................................................................................204

Considerations for Interval Data Types.........................................................................................204

Numeric Data Types ...........................................................................................................................205

Expressions..........................................................................................................................................208

Character Value Expressions.........................................................................................................208

Examples of Character Value Expressions...............................................................................208

Datetime Value Expressions..........................................................................................................210

Considerations for Datetime Value Expressions......................................................................211

Examples of Datetime Value Expressions................................................................................211

Interval Value Expressions............................................................................................................213

Considerations for Interval Value Expressions........................................................................214

Examples of Interval Value Expressions..................................................................................215

Numeric Value Expressions...........................................................................................................217

Considerations for Numeric Value Expressions......................................................................217

10 Table of Contents

Examples of Numeric Value Expressions.................................................................................219

Identifiers............................................................................................................................................220

Regular Identifiers.........................................................................................................................220

Delimited Identifiers......................................................................................................................220

Examples of Identifiers..................................................................................................................220

Indexes................................................................................................................................................221

SQL Indexes...................................................................................................................................221

Keys.....................................................................................................................................................222

Clustering Keys..............................................................................................................................222

Index Keys.....................................................................................................................................222

SQL Index Keys........................................................................................................................222

Primary Keys.................................................................................................................................222

Literals.................................................................................................................................................223

Character String Literals................................................................................................................223

Considerations for Character String Literals............................................................................224

Examples of Character String Literals......................................................................................224

Datetime Literals............................................................................................................................225

Examples of Datetime Literals.................................................................................................225

Interval Literals..............................................................................................................................226

Considerations for Interval Literals.........................................................................................227

Examples of Interval Literals....................................................................................................228

Numeric Literals............................................................................................................................228

Examples of Numeric Literals..................................................................................................229

Null......................................................................................................................................................230

Using Null Versus Default Values.................................................................................................230

Defining Columns That Allow or Prohibit Null............................................................................230

Null in DISTINCT, GROUP BY, and ORDER BY Clauses........................................................230

Null and Expression Evaluation Comparison .........................................................................231

SQL Tables.....................................................................................................................................232

Automatically Creating Partitions............................................................................................232

Predicates............................................................................................................................................233

BETWEEN Predicate......................................................................................................................233

Considerations for BETWEEN.................................................................................................234

Examples of BETWEEN............................................................................................................234

Comparison Predicates..................................................................................................................234

Considerations for Comparison Predicates..............................................................................235

Examples of Comparison Predicates........................................................................................236

EXISTS Predicate............................................................................................................................238

Examples of EXISTS.................................................................................................................238

IN Predicate...................................................................................................................................239

Considerations for IN...............................................................................................................239

Examples of IN.........................................................................................................................240

NULL Predicate.............................................................................................................................241

Considerations for NULL.........................................................................................................241

Examples of NULL...................................................................................................................242

Quantified Comparison Predicates...............................................................................................242

Considerations for ALL, ANY, SOME......................................................................................243

Examples of ALL, ANY, SOME................................................................................................243

Privileges.............................................................................................................................................245

Considerations...............................................................................................................................245

General Rules for object-privilege and schema-privilege........................................................245

Privilege Descriptions for object-privilege...............................................................................245

General Rules for column-list...................................................................................................246

Schemas...............................................................................................................................................247

Search Condition.................................................................................................................................248

Table of Contents 11

Considerations for Search Condition............................................................................................248

Order of Evaluation..................................................................................................................248

Column References...................................................................................................................248

Subqueries................................................................................................................................248

Examples of Search Condition.......................................................................................................249

Subquery.............................................................................................................................................250

SELECT Form of a Subquery.........................................................................................................250

Using Subqueries to Provide Comparison Values.........................................................................250

Nested Subqueries When Providing Comparison Values.............................................................250

Correlated Subqueries When Providing Comparison Values.......................................................251

Tables...................................................................................................................................................252

Base Tables and Views........................................................................................................................252

Example of a Base Table......................................................................................................................252

Triggers................................................................................................................................................253

Views...................................................................................................................................................254

SQL Views......................................................................................................................................254

Example of a View....................................................................................................................254

MVGROUPs ..................................................................................................................................254

5 SQL Clauses...............................................................................................................257

DEFAULT Clause................................................................................................................................258

Examples of DEFAULT..................................................................................................................259

SAMPLE Clause..................................................................................................................................260

Considerations for SAMPLE..........................................................................................................261

Sample Rows............................................................................................................................261

Cluster Sampling......................................................................................................................261

Examples of SAMPLE....................................................................................................................262

SEQUENCE BY Clause.......................................................................................................................268

Considerations for SEQUENCE BY...............................................................................................268

Examples of SEQUENCE BY.........................................................................................................269

TRANSPOSE Clause...........................................................................................................................271

Considerations for TRANSPOSE...................................................................................................272

Multiple TRANSPOSE Clauses and Sets..................................................................................272

Degree and Column Order of the TRANSPOSE Result...........................................................272

Data Type of the TRANSPOSE Result......................................................................................272

Cardinality of the TRANSPOSE Result....................................................................................273

Examples of TRANSPOSE.............................................................................................................273

6 SQL Functions and Expressions................................................................................279

Categories............................................................................................................................................279

Standard Normalization................................................................................................................279

Aggregate (Set) Functions..............................................................................................................279

Character String Functions............................................................................................................280

Datetime Functions........................................................................................................................281

Mathematical Functions................................................................................................................282

Sequence Functions.......................................................................................................................283

Other Functions and Expressions..................................................................................................285

ABS Function.......................................................................................................................................286

Example of ABS.............................................................................................................................286

ACOS Function...................................................................................................................................287

Examples of ACOS........................................................................................................................287

ADD_MONTHS Function...................................................................................................................288

Examples of ADD_MONTHS........................................................................................................288

12 Table of Contents

ASCII Function....................................................................................................................................289

Example of ASCII..........................................................................................................................289

ASIN Function.....................................................................................................................................290

Examples of ASIN..........................................................................................................................290

ATAN Function...................................................................................................................................291

Examples of ATAN........................................................................................................................291

ATAN2 Function.................................................................................................................................292

Example of ATAN2........................................................................................................................292

AVG Function......................................................................................................................................293

Considerations for AVG.................................................................................................................293

Data Type of the Result............................................................................................................293

Operands of the Expression.....................................................................................................293

Nulls.........................................................................................................................................293

Examples of AVG...........................................................................................................................293

CASE (Conditional) Expression..........................................................................................................295

Considerations for CASE...............................................................................................................296

Data Type of the CASE Expression..........................................................................................296

Character Data Type.................................................................................................................296

Numeric Data Type..................................................................................................................296

Datetime Data Type..................................................................................................................296

Interval Data Type....................................................................................................................296

Examples of CASE.........................................................................................................................296

CAST Expression.................................................................................................................................298

Considerations for CAST...............................................................................................................298

Valid Conversions for CAST .........................................................................................................298

Examples of CAST.........................................................................................................................299

CEILING Function..............................................................................................................................300

Example of CEILING.....................................................................................................................300

CHAR Function...................................................................................................................................301

Example of CHAR.........................................................................................................................301

CHAR_LENGTH Function.................................................................................................................302

Considerations for CHAR_LENGTH............................................................................................302

CHAR and VARCHAR Operands............................................................................................302

Examples of CHAR_LENGTH......................................................................................................302

COALESCE Function..........................................................................................................................303

Example of COALESCE.................................................................................................................303

CODE_VALUE Function.....................................................................................................................304

Example of CODE_VALUE Function............................................................................................304

CONCAT Function..............................................................................................................................305

Concatenation Operator (||).........................................................................................................305

Considerations for CONCAT.........................................................................................................305

Operands..................................................................................................................................305

SQL Parameters........................................................................................................................305

Examples of CONCAT...................................................................................................................305

CONVERTTIMESTAMP Function......................................................................................................307

Considerations for CONVERTTIMESTAMP.................................................................................307

Relationship to the JULIANTIMESTAMP Function................................................................307

Use of CONVERTTIMESTAMP...............................................................................................307

Examples of CONVERTTIMESTAMP...........................................................................................307

COS Function......................................................................................................................................308

Example of COS.............................................................................................................................308

COSH Function...................................................................................................................................309

Example of COSH..........................................................................................................................309

COUNT Function................................................................................................................................310

Considerations for COUNT...........................................................................................................310

Table of Contents 13

Operands of the Expression.....................................................................................................310

Nulls.........................................................................................................................................310

Examples of COUNT.....................................................................................................................310

CURRENT Function............................................................................................................................312

Example of CURRENT..................................................................................................................312

CURRENT_DATE Function................................................................................................................313

Examples of CURRENT_DATE.....................................................................................................313

CURRENT_ROLE Function................................................................................................................314

Example of CURRENT_ROLE.......................................................................................................314

CURRENT_TIME Function.................................................................................................................315

Example of CURRENT_TIME.......................................................................................................315

CURRENT_TIMESTAMP Function....................................................................................................316

Example of CURRENT_TIMESTAMP...........................................................................................316

CURRENT_USER Function................................................................................................................317

Example of CURRENT_USER.......................................................................................................317

DATE_ADD Function.........................................................................................................................318

Examples of DATE_ADD...............................................................................................................318

DATE_SUB Function...........................................................................................................................319

Examples of DATE_SUB................................................................................................................319

DATEADD Function...........................................................................................................................319

Examples of DATEADD................................................................................................................320

DATEDIFF Function............................................................................................................................321

Examples of DATEDIFF.................................................................................................................321

DATEFORMAT Function....................................................................................................................322

Examples of DATEFORMAT.........................................................................................................322

DATE_PART Function (of an interval)................................................................................................322

Examples of DATE_PART..............................................................................................................322

DATE_PART Function (of a timestamp).............................................................................................323

Examples of DATE_PART..............................................................................................................323

DATE_TRUNC Function.....................................................................................................................324

Examples of DATE_TRUNC..........................................................................................................324

DAY Function......................................................................................................................................325

Example of DAY.............................................................................................................................325

DAYNAME Function..........................................................................................................................326

Example of DAYNAME.................................................................................................................326

DAYOFMONTH Function..................................................................................................................327

Examples of DAYOFMONTH.......................................................................................................327

DAYOFWEEK Function......................................................................................................................328

Example of DAYOFWEEK.............................................................................................................328

DAYOFYEAR Function.......................................................................................................................329

Example of DAYOFYEAR..............................................................................................................329

DECODE Function..............................................................................................................................330

Considerations for DECODE.........................................................................................................330

Examples of DECODE...................................................................................................................331

DEGREES Function.............................................................................................................................333

Examples of DEGREES..................................................................................................................333

DIFF1 Function....................................................................................................................................334

Considerations for DIFF1...............................................................................................................334

Equivalent Result......................................................................................................................334

Datetime Arguments................................................................................................................334

Examples of DIFF1.........................................................................................................................334

DIFF2 Function....................................................................................................................................336

Considerations for DIFF2...............................................................................................................336

Equivalent Result......................................................................................................................336

Datetime Arguments................................................................................................................336

14 Table of Contents

Examples of DIFF2.........................................................................................................................336

EXP Function.......................................................................................................................................338

Examples of EXP............................................................................................................................338

EXPLAIN Function.............................................................................................................................339

Considerations for EXPLAIN Function.........................................................................................339

Using a Statement Pattern........................................................................................................339

Result of the EXPLAIN Function.............................................................................................339

Examples of EXPLAIN Function...................................................................................................341

EXTRACT Function.............................................................................................................................342

Examples of EXTRACT..................................................................................................................342

FLOOR Function.................................................................................................................................343

Examples of FLOOR......................................................................................................................343

HOUR Function..................................................................................................................................344

Example of HOUR.........................................................................................................................344

INSERT Function................................................................................................................................345

Examples of INSERT......................................................................................................................345

ISNULL Function................................................................................................................................346

Examples of ISNULL.....................................................................................................................346

JULIANTIMESTAMP Function...........................................................................................................347

Examples of JULIANTIMESTAMP................................................................................................347

LASTNOTNULL Function..................................................................................................................347

Example of LASTNOTNULL.........................................................................................................347

LCASE Function..................................................................................................................................349

Example of LCASE........................................................................................................................349

LEFT Function.....................................................................................................................................350

Examples of LEFT..........................................................................................................................350

LOCATE Function...............................................................................................................................351

Considerations for LOCATE..........................................................................................................351

Result of LOCATE....................................................................................................................351

Using UCASE...........................................................................................................................351

Examples of LOCATE....................................................................................................................351

LOG Function......................................................................................................................................352

Example of LOG............................................................................................................................352

LOG10 Function..................................................................................................................................353

Example of LOG10.........................................................................................................................353

LOWER Function................................................................................................................................354

Considerations for LOWER...........................................................................................................354

Example of LOWER.......................................................................................................................358

LPAD Function....................................................................................................................................359

Examples of LPAD.........................................................................................................................359

LTRIM Function..................................................................................................................................360

Considerations for LTRIM.............................................................................................................360

Result of LTRIM........................................................................................................................360

Example of LTRIM.........................................................................................................................360

MAX/MAXIMUM Function................................................................................................................361

Considerations for MAX................................................................................................................361

Operands of the Expression.....................................................................................................361

Example of MAX............................................................................................................................361

MIN Function......................................................................................................................................362

Considerations for MIN.................................................................................................................362

Operands of the Expression.....................................................................................................362

Example of MIN.............................................................................................................................362

MINUTE Function...............................................................................................................................363

Example of MINUTE.....................................................................................................................363

MOD Function....................................................................................................................................364

Table of Contents 15

Example of MOD...........................................................................................................................364

MONTH Function...............................................................................................................................365

Example of MONTH......................................................................................................................365

MONTHNAME Function...................................................................................................................366

Example of MONTHNAME..........................................................................................................366

MOVINGAVG Function......................................................................................................................367

Example of MOVINGAVG............................................................................................................367

MOVINGCOUNT Function................................................................................................................368

Considerations for MOVINGCOUNT...........................................................................................368

No DISTINCT Clause...............................................................................................................368

Example of MOVINGCOUNT.......................................................................................................368

MOVINGMAX Function.....................................................................................................................370

Example of MOVINGMAX...........................................................................................................370

MOVINGMIN Function......................................................................................................................371

Example of MOVINGMIN............................................................................................................371

MOVINGSTDDEV Function...............................................................................................................372

Example of MOVINGSTDDEV......................................................................................................372

MOVINGSUM Function.....................................................................................................................374

Example of MOVINGSUM............................................................................................................374

MOVINGVARIANCE Function..........................................................................................................375

Example of MOVINGVARIANCE.................................................................................................375

NULLIF Function................................................................................................................................377

Example of NULLIF.......................................................................................................................377

NULLIFZERO Function......................................................................................................................378

Examples of NULLIFZERO...........................................................................................................378

NVL Function......................................................................................................................................379

Examples of NVL...........................................................................................................................379

OCTET_LENGTH Function................................................................................................................380

Considerations for OCTET_LENGTH...........................................................................................380

CHAR and VARCHAR Operands............................................................................................380

Similarity to CHAR_LENGTH Function..................................................................................380

Example of OCTET_LENGTH.......................................................................................................380

OFFSET Function................................................................................................................................381

Example of OFFSET.......................................................................................................................381

PI Function..........................................................................................................................................382

Example of PI.................................................................................................................................382

POSITION Function............................................................................................................................383

Considerations for POSITION.......................................................................................................383

Result of POSITION.................................................................................................................383

Using the UPSHIFT Function...................................................................................................383

Examples of POSITION.................................................................................................................383

POWER Function................................................................................................................................384

Examples of POWER.....................................................................................................................384

QUARTER Function............................................................................................................................385

Example of QUARTER..................................................................................................................385

RADIANS Function............................................................................................................................386

Examples of RADIANS..................................................................................................................386

RANK/RUNNINGRANK Function....................................................................................................387

Considerations for RUNNINGRANK...........................................................................................387

Depends of the Sequence order................................................................................................387

NULL Values............................................................................................................................387

Examples of RUNNINGRANK.....................................................................................................387

RUNNINGSTDDEV Function............................................................................................................389

Considerations for RUNNINGSTDDEV.......................................................................................389

Equivalent Result......................................................................................................................389

16 Table of Contents

Examples of RUNNINGSTDDEV..................................................................................................389

REPEAT Function................................................................................................................................390

Example of REPEAT......................................................................................................................390

REPLACE Function.............................................................................................................................390

Example of REPLACE...................................................................................................................391

RIGHT Function..................................................................................................................................391

Examples of RIGHT.......................................................................................................................391

ROUND Function................................................................................................................................392

Examples of ROUND.....................................................................................................................392

ROWS SINCE Function.......................................................................................................................392

Considerations for ROWS SINCE..................................................................................................393

Counting the Rows...................................................................................................................393

Examples of ROWS SINCE............................................................................................................393

ROWS SINCE CHANGED Function..................................................................................................394

Considerations for ROWS SINCE CHANGED.............................................................................394

Counting the Rows...................................................................................................................394

Examples of ROWS SINCE CHANGED........................................................................................394

RPAD Function...................................................................................................................................394

RTRIM Function..................................................................................................................................395

Considerations for RTRIM.............................................................................................................395

Result of RTRIM.......................................................................................................................395

Example of RTRIM........................................................................................................................395

RUNNINGAVG Function...................................................................................................................395

Considerations for RUNNINGAVG..............................................................................................396

Equivalent Result......................................................................................................................396

Example of RUNNINGAVG..........................................................................................................396

RUNNINGCOUNT Function..............................................................................................................396

Considerations for RUNNINGCOUNT........................................................................................396

No DISTINCT Clause...............................................................................................................396

Example of RUNNINGCOUNT....................................................................................................396

RUNNINGMAX Function..................................................................................................................397

Example of RUNNINGMAX.........................................................................................................397

RUNNINGMIN Function...................................................................................................................397

Example of RUNNINGMIN..........................................................................................................397

RUNNINGRANK Function................................................................................................................399

RUNNINGSTDDEV Function............................................................................................................400

Considerations for RUNNINGSTDDEV.......................................................................................400

Equivalent Result......................................................................................................................400

Examples of RUNNINGSTDDEV..................................................................................................400

RUNNINGSUM Function...................................................................................................................401

Example of RUNNINGSUM..........................................................................................................401

RUNNINGVARIANCE Function........................................................................................................402

Examples of RUNNINGVARIANCE.............................................................................................402

SECOND Function..............................................................................................................................403

Example of SECOND.....................................................................................................................403

SIGN Function.....................................................................................................................................404

Examples of SIGN..........................................................................................................................404

SIN Function.......................................................................................................................................405

Example of SIN..............................................................................................................................405

SINH Function....................................................................................................................................406

Example of SINH...........................................................................................................................406

SPACE Function..................................................................................................................................407

Example of SPACE.........................................................................................................................407

SQRT Function....................................................................................................................................408

Example of SQRT...........................................................................................................................408

Table of Contents 17

STDDEV Function...............................................................................................................................409

Considerations for STDDEV..........................................................................................................409

Definition of STDDEV..............................................................................................................409

Data Type of the Result............................................................................................................409

Operands of the Expression.....................................................................................................409

Nulls.........................................................................................................................................409

FLOAT(54) and DOUBLE PRECISION Data............................................................................409

Examples of STDDEV....................................................................................................................410

SUBSTRING/SUBSTR Function..........................................................................................................411

Alternative Forms..........................................................................................................................411

Considerations for SUBSTRING/SUBSTR.....................................................................................411

Requirements for the Expression, Length, and Start Position.................................................411

Examples of SUBSTRING/SUBSTR...............................................................................................411

SUM Function.....................................................................................................................................413

Considerations for SUM................................................................................................................413

Data Type and Scale of the Result............................................................................................413

Operands of the Expression.....................................................................................................413

Example of SUM............................................................................................................................413

TAN Function......................................................................................................................................414

Example of TAN............................................................................................................................414

TANH Function...................................................................................................................................415

Example of TANH.........................................................................................................................415

THIS Function.....................................................................................................................................416

Considerations for THIS................................................................................................................416

Counting the Rows...................................................................................................................416

Example of THIS............................................................................................................................416

TRANSLATE Function........................................................................................................................417

TRIM Function....................................................................................................................................418

Considerations for TRIM...............................................................................................................418

Result of TRIM..........................................................................................................................418

Examples of TRIM.........................................................................................................................418

UCASE Function.................................................................................................................................419

Considerations for UCASE............................................................................................................419

Examples of UCASE......................................................................................................................425

UPPER Function..................................................................................................................................427

Example of UPPER........................................................................................................................427

UPSHIFT Function..............................................................................................................................428

Examples of UPSHIFT...................................................................................................................428

USER Function....................................................................................................................................429

Example of USER...........................................................................................................................429

VARIANCE Function..........................................................................................................................430

Considerations for VARIANCE.....................................................................................................430

Definition of VARIANCE.........................................................................................................430

Data Type of the Result............................................................................................................431

Operands of the Expression.....................................................................................................431

Nulls.........................................................................................................................................431

FLOAT(54) and DOUBLE PRECISION Data............................................................................431

Examples of VARIANCE...............................................................................................................431

WEEK Function...................................................................................................................................432

Example of WEEK.........................................................................................................................432

YEAR Function....................................................................................................................................433

Example of YEAR..........................................................................................................................433

ZEROIFNULL Function......................................................................................................................434

Example of ZEROIFNULL.............................................................................................................434

18 Table of Contents

A Quick Reference........................................................................................................435

B Reserved Words.........................................................................................................437

Reserved Neoview SQL Identifiers ....................................................................................................437

C Limits...........................................................................................................................441

D Standard SQL and Neoview SQL............................................................................443

ANSI SQL Standards...........................................................................................................................443

ISO Standards......................................................................................................................................443

Neoview SQL Compliance..................................................................................................................444

Neoview SQL Extensions to Standard SQL........................................................................................447

Character Set Support.........................................................................................................................448

Index...............................................................................................................................449

Table of Contents 19

List of Tables

1-1 Concurrent DDL/Utility Operation and File Access Modes.........................................................33

1-2 Concurrent DDL/Utility and DML Operations.............................................................................34

1-3 Concurrent DML and DDL Operations .......................................................................................34

1-4 Operations Effect on Table Timestamps .......................................................................................35

2-1 Maximum Row Sizes Available.....................................................................................................74

2-2 EXPLAIN Statement Options......................................................................................................104

2-3 Plan Summary Information.........................................................................................................106

2-4 Node Listing Information ...........................................................................................................106

2-5 Cost Factors of DETAIL_COST column......................................................................................111

6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings .................................................354

6-2 One-to-One UCS2 Mappings ......................................................................................................419

6-3 Two-Character UCS2 Mapping ..................................................................................................422

6-4 Three-Character UCS2 Mapping ................................................................................................425

B-1 Reserved SQL Identifiers ............................................................................................................437

About This Document

This manual describes reference information about the syntax of SQL statements, functions, and other SQL language elements supported by the Neoview database software.

The Neoview SQL statements and utilities are entered interactively or from script files using the client-based utility, Neoview Script. For information on Neoview Script, see the Neoview Script Guide.

NOTE: In this manual, SQL language elements, statements, and clauses within statements are

based on the ANSI SQL:1999 standard.

Supported Release Version Updates (RVUs)

HP Neoview Release 2.2.

Intended Audience

This manual is intended for database administrators and application programmers who are using SQL to manage a database on an HP Neoview data warehousing platform.

The reader should be familiar with structured query language (SQL) and with the American National Standard Database Language SQL:1999.

New and Changed Information in This Edition

New or Changed InformationSection

Updated considerations.“ALTER MATERIALIZED VIEW

Statement” (page 44)

Added Considerations subsection for ALTER MVGROUP.“ALTER MVGROUP Statement” (page 45)

New statement.“CALL Statement” (page 53)

Updated syntax.“CREATE MATERIALIZED VIEW

Statement” (page 60)

Added DISK POOL and ALIGNED | PACKED format to the list of attributes.

“CREATE TABLE Statement” (page 69)

New function.“CURRENT_ROLE Function” (page 314)

New function.“CURRENT_USER Function” (page 317)

The option NOLOG is changed to NOMVLOG.“DELETE Statement” (page 90)

Added Considerations subsection for DROP MVGROUP.“DROP MVGROUP Statement” (page 95)

New function.“EXPLAIN Function” (page 339)

New statement.“EXPLAIN Statement” (page 104)

Corrected syntax.“GRANT Statement” (page 115)

New statement.“GRANT EXECUTE Statement” (page 117)

New statement.“GRANT SCHEMA Statement” (page 119)

The option NOLOG is changed to NOMVLOG. Deleted some considerations.

Added a consideration on self-referencing INSERT.

“INSERT Statement” (page 121)

The keyword LOG is changed to MVLOG.“MAINTAIN MVGROUP” (page 184)

New statement.“MERGE INTO Statement” (page 129)

Updated to include information on schemas.“Privileges” (page 245)

Supported Release Version Updates (RVUs) 23

New or Changed InformationSection

Updated to include ONLINE | OFFLINE options.“POPULATE INDEX Utility” (page 186)

Updated to include schemas.“REVOKE Statement” (page 134)

New statement.“REVOKE EXECUTEStatement” (page136)

New statement.“REVOKE SCHEMAStatement” (page138)

New consideration added.“Considerations for SELECT” (page 151)

GROUP BY clause enhanced to allow expressions. joined-table syntax is extended to support full outer join.

“SELECT Statement” (page 141)

Added table that summarizes the Neoview SQL data types.“Data Types” (page 197)

Extended to allow multiple columns to be specified. The option NOLOG is changed to NOMVLOG.

“UPDATE Statement” (page 169)

New function.“USER Function” (page 429)

Document Organization

• Chapter 1 (page 29), introduces Neoview SQL and covers topics such as database security,

data consistency and integrity, transaction management, and ANSI compliance.

• Chapter 2 (page 41), describes the SQL statements supported by Neoview SQL.

• Chapter 3 (page 181), describes utilities that perform tasks such as maintenance, populating

indexes, purging data from tables and indexes, and reorganizing and reloading data in a table or index.

• Chapter 4 (page 189), describes parts of the language, such as database objects, data types,

expressions, identifiers, literals, and predicates, which occur within the syntax of Neoview SQL statements.

• Chapter 5 (page 257), describes clauses used by Neoview SQL statements.

• Chapter 6 (page 279), describes specific functions andexpressions that you can use in Neoview

SQL statements.

• Appendix A (page 435), is a quick reference to commands, statements, and utilities.

• Appendix B (page 437), lists the words that are reserved in Neoview SQL.

• Appendix C (page 441), describes limits in Neoview SQL.

• Appendix D (page 443), describes how Neoview SQL conforms to the ANSI standard.

Notation Conventions

General Syntax Notation

This list summarizes the notation conventions for syntax presentation in this manual.

UPPERCASE LETTERS

Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example:

SELECT

Italic Letters

Italic letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. For example:

file-name

Computer Type

Computer type letters within text indicate case-sensitive keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example:

24 About This Document

myfile.sh

Bold Text

Bold text in an example indicates user input typed at the terminal. For example:

ENTER RUN CODE

?123 CODE RECEIVED: 123.00

The user must press the Return key after typing the input.

[ ] Brackets

Brackets enclose optional syntax items. For example:

DATETIME [start-field TO] end-field

A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example:

DROP SCHEMA schema [CASCADE] [RESTRICT]

DROP SCHEMA schema [ CASCADE | RESTRICT ]

{ } Braces

Braces enclose required syntax items. For example:

FROM { grantee[, grantee]...}

A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. For example:

INTERVAL { start-field TO end-field } { single-field }

INTERVAL { start-field TO end-field | single-field }

| Vertical Line

A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example:

{expression | NULL}

… Ellipsis

An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example:

ATTRIBUTE[S] attribute [, attribute]...

{, sql-expression}...

An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example:

expression-n…

Notation Conventions 25

Punctuation

Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. For example:

DAY (datetime-expression)

@script-file

Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example:

"{" module-name [, module-name]... "}"

Item Spacing

Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example:

DAY (datetime-expression)

DAY(datetime-expression)

If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items:

myfile.sh

Line Spacing

If the syntax of a command is too long to fit on a singleline, each continuation line is indented three spaces and is separated from the preceding line by a blank line. This spacing distinguishes items in a continuation line from itemsin a vertical list of selections. For example:

match-value [NOT] LIKE pattern

[ESCAPE esc-char-expression]

HP Neoview SQL Reference Manual

Specifications and Main Features

Frequently Asked Questions

User Manual