HP Neoview SQL User manual

HP Neoview SQL Reference Manual
HP Part Number: 611360-001
Published: July 2010
Edition: HP Neoview Release 2.5

©Copyright 2010 Hewlett-Packard Development Company, L.P.
Legal Notice
Confidential computer software. 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 information contained herein is subject to change without notice. The only warranties for HP products and 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 FITNESS FOR 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.
© 1990, 1991, 1992, 1993 Open Software Foundation, Inc. The OSF documentation and the OSF software to which it relates are derived in part
from materials supplied by the following:© 1987, 1988, 1989 Carnegie-Mellon University. © 1989, 1990, 1991 Digital Equipment Corporation. ©
1985, 1988, 1989, 1990 Encore Computer Corporation. © 1988 Free Software Foundation, Inc. © 1987, 1988, 1989, 1990, 1991 Hewlett-Packard
Company. © 1985, 1987, 1988, 1989, 1990, 1991, 1992 International Business Machines Corporation. © 1988, 1989 Massachusetts Institute of
Technology. © 1988, 1989, 1990 Mentat Inc. © 1988 Microsoft Corporation. © 1987, 1988, 1989, 1990, 1991, 1992 SecureWare, Inc. © 1990, 1991
Siemens Nixdorf Informationssysteme AG. © 1986, 1989, 1996, 1997 Sun Microsystems, Inc. © 1989, 1990, 1991 Transarc Corporation.OSF software
and documentation are based in part on the Fourth Berkeley Software Distribution under license from The Regents of the University of California.
OSF acknowledges the following individuals and institutions for their role in its development: Kenneth C.R.C. Arnold, Gregory S. Couch, Conrad
C. Huang, Ed James, Symmetric Computer Systems, Robert Elz. © 1980, 1981, 1982, 1983, 1985, 1986, 1987, 1988, 1989 Regents of the University
of California.

Table of Contents
About This Document.......................................................................................................19
Intended Audience................................................................................................................................19
New and Changed Information in This Edition...................................................................................19
Document Organization.......................................................................................................................19
Notation Conventions...........................................................................................................................20
General Syntax Notation.................................................................................................................20
Related Documentation........................................................................................................................22
Neoview Customer Library.............................................................................................................22
Publishing History................................................................................................................................24
HP Encourages Your Comments..........................................................................................................24
1 Introduction...................................................................................................................25
SQL Language.......................................................................................................................................25
Security.................................................................................................................................................25
Data Consistency and Access Options..................................................................................................25
READ UNCOMMITTED ................................................................................................................26
READ COMMITTED ......................................................................................................................26
SERIALIZABLE or REPEATABLE READ.......................................................................................26
SKIP CONFLICT..............................................................................................................................26
Database Integrity and Locking............................................................................................................26
Lock Duration..................................................................................................................................27
Lock Granularity..............................................................................................................................27
Lock Mode.......................................................................................................................................27
Lock Holder.....................................................................................................................................27
Transaction Management......................................................................................................................27
Statement Atomicity........................................................................................................................28
User-Defined and System-Defined Transactions............................................................................28
Rules for DML Statements...............................................................................................................29
Effect of AUTOCOMMIT Option....................................................................................................29
Concurrency....................................................................................................................................29
Transaction Access Modes...............................................................................................................31
Transaction Isolation Levels............................................................................................................32
Transaction Rollback Mode.............................................................................................................33
ANSI Compliance and Neoview SQL Extensions................................................................................33
ANSI-Compliant Statements...........................................................................................................33
Statements That Are Neoview SQL Extensions..............................................................................34
ANSI-Compliant Functions.............................................................................................................34
Neoview SQL Error Messages..............................................................................................................35
2 SQL Statements.............................................................................................................37
Categories.............................................................................................................................................37
Data Definition Language (DDL) Statements.................................................................................37
Data Manipulation Language (DML) Statements...........................................................................38
Transaction Control Statements.......................................................................................................38
Resource Control and Optimization Statements.............................................................................38
Control Statements..........................................................................................................................39
Object Naming Statements..............................................................................................................39
Stored Procedure Statements...........................................................................................................39
ALTER MATERIALIZED VIEW Statement..........................................................................................40
Table of Contents 3

Syntax Description of ALTER MATERIALIZED VIEW..................................................................40
Considerations for ALTER MATERIALIZED VIEW.......................................................................40
Example of ALTER MATERIALIZED VIEW...................................................................................41
ALTER MVGROUP Statement..............................................................................................................42
Syntax Description of ALTER MVGROUP.....................................................................................42
Considerations for ALTER MVGROUP..........................................................................................42
Examples of ALTER MVGROUP.....................................................................................................42
ALTER SYNONYM Statement..............................................................................................................43
Syntax Description of ALTER SYNONYM......................................................................................43
Considerations for ALTER SYNONYM..........................................................................................43
Example of ALTER SYNONYM......................................................................................................43
ALTER TABLE Statement.....................................................................................................................44
Syntax Description of ALTER TABLE.............................................................................................45
Considerations for ALTER TABLE..................................................................................................48
Recalibrating an IDENTITY Column..............................................................................................49
Example of ALTER TABLE ALTER COLUMN...............................................................................51
Example of ALTER TABLE..............................................................................................................52
ALTER TRIGGER Statement.................................................................................................................53
Syntax Description of ALTER TRIGGER.........................................................................................53
Considerations for ALTER TRIGGER.............................................................................................53
Examples of ALTER TRIGGER........................................................................................................53
ALTER VIEW Statement.......................................................................................................................54
Syntax Description of ALTER VIEW...............................................................................................54
Considerations for ALTER VIEW....................................................................................................54
Example of ALTER VIEW................................................................................................................54
BEGIN WORK Statement.....................................................................................................................55
Considerations for BEGIN WORK..................................................................................................55
Example of BEGIN WORK..............................................................................................................55
CALL Statement....................................................................................................................................56
Syntax Description of CALL............................................................................................................56
Considerations for CALL................................................................................................................56
Examples of CALL...........................................................................................................................57
COMMIT WORK Statement.................................................................................................................59
Considerations for COMMIT WORK..............................................................................................59
Example of COMMIT WORK..........................................................................................................59
CREATE INDEX Statement...................................................................................................................60
Syntax Description of CREATE INDEX..........................................................................................60
Considerations for CREATE INDEX...............................................................................................61
Example of CREATE INDEX...........................................................................................................62
CREATE MATERIALIZED VIEW Statement........................................................................................63
Syntax Description of CREATE MATERIALIZED VIEW................................................................63
Considerations for CREATE MATERIALIZED VIEW....................................................................65
Examples of CREATE MATERIALIZED VIEW...............................................................................67
CREATE MVGROUP Statement...........................................................................................................69
Considerations for MVGROUP.......................................................................................................69
Example of CREATE MVGROUP....................................................................................................69
CREATE SCHEMA Statement..............................................................................................................70
Syntax Description of CREATE SCHEMA......................................................................................70
Considerations for CREATE SCHEMA...........................................................................................70
Example of CREATE SCHEMA.......................................................................................................70
CREATE SYNONYM Statement...........................................................................................................71
Syntax Description of CREATE SYNONYM ..................................................................................71
Considerations for CREATE SYNONYM........................................................................................71
Example of CREATE SYNONYM....................................................................................................71
CREATE TABLE Statement...................................................................................................................72
4 Table of Contents

Syntax Description of CREATE TABLE...........................................................................................73
Considerations for CREATE TABLE................................................................................................78
Considerations for CREATE SET TABLE........................................................................................78
Considerations for CREATE VOLATILE TABLE............................................................................79
Considerations for CREATE TABLE LIKE......................................................................................81
Considerations for CREATE TABLE AS..........................................................................................84
Reserved Table Names.....................................................................................................................86
Authorization and Availability Requirements................................................................................86
Limits for Tables..............................................................................................................................87
Calculating Row Size.......................................................................................................................87
Creating Partitions Automatically...................................................................................................87
Generating Values For an IDENTITY Column................................................................................88
Neoview SQL Extensions to CREATE TABLE................................................................................92
Considerations for DISK POOL.......................................................................................................92
Examples of CREATE TABLE..........................................................................................................93
Examples of CREATE SET TABLE..................................................................................................94
Example of CREATE SET VOLATILE TABLE.................................................................................94
Examples of CREATE TABLE AS....................................................................................................94
CREATE TRIGGER Statement..............................................................................................................96
Syntax Description of CREATE TRIGGER......................................................................................96
Considerations for CREATE TRIGGER...........................................................................................97
Examples of CREATE TRIGGER...................................................................................................100
CREATE VIEW Statement...................................................................................................................102
Syntax Description of CREATE VIEW...........................................................................................102
Considerations for CREATE VIEW...............................................................................................103
Examples of CREATE VIEW..........................................................................................................105
DELETE Statement..............................................................................................................................108
Syntax Description of DELETE......................................................................................................108
Considerations for DELETE..........................................................................................................110
Examples of DELETE.....................................................................................................................111
DROP INDEX Statement.....................................................................................................................113
Syntax Description of DROP INDEX.............................................................................................113
Considerations for DROP INDEX.................................................................................................113
Example of DROP INDEX.............................................................................................................113
DROP MATERIALIZED VIEW Statement..........................................................................................114
Syntax Description of DROP MATERIALIZED VIEW..................................................................114
Authorization and Availability Requirements..............................................................................114
Example of DROP MATERIALIZED VIEW..................................................................................114
DROP MVGROUP Statement.............................................................................................................115
Syntax Description of DROP MVGROUP.....................................................................................115
Considerations for DROP MVGROUP..........................................................................................115
Example of DROP MVGROUP......................................................................................................115
DROP SCHEMA Statement................................................................................................................116
Syntax Description of DROP SCHEMA........................................................................................116
Considerations for DROP SCHEMA.............................................................................................116
Example of DROP SCHEMA.........................................................................................................116
DROP SYNONYM Statement.............................................................................................................117
Syntax Description of DROP SYNONYM ....................................................................................117
Considerations for DROP SYNONYM..........................................................................................117
Example of DROP SYNONYM......................................................................................................117
DROP TABLE Statement.....................................................................................................................118
Syntax Description of DROP TABLE.............................................................................................118
Considerations for DROP TABLE..................................................................................................118
Examples of DROP TABLE............................................................................................................118
DROP TRIGGER Statement................................................................................................................119
Table of Contents 5

Syntax Description of DROP TRIGGER........................................................................................119
Considerations for DROP TRIGGER.............................................................................................119
Example of DROP TRIGGER.........................................................................................................119
DROP VIEW Statement.......................................................................................................................120
Syntax Description of DROP VIEW...............................................................................................120
Considerations for DROP VIEW...................................................................................................120
Example of DROP VIEW...............................................................................................................120
EXECUTE Statement...........................................................................................................................121
Syntax Description of EXECUTE...................................................................................................121
Considerations for EXECUTE........................................................................................................122
Examples of EXECUTE..................................................................................................................122
EXPLAIN Statement............................................................................................................................124
Syntax Description of EXPLAIN...................................................................................................124
Considerations for EXPLAIN........................................................................................................125
GET SERVICE Statement....................................................................................................................134
Syntax Description of GET SERVICE............................................................................................134
Considerations for GET SERVICE.................................................................................................134
Examples of GET SERVICE...........................................................................................................134
GRANT Statement..............................................................................................................................136
Syntax Description of GRANT......................................................................................................136
Considerations for GRANT...........................................................................................................137
Examples of GRANT.....................................................................................................................137
GRANT EXECUTE Statement.............................................................................................................138
Syntax Description of GRANT EXECUTE.....................................................................................138
Considerations for GRANT EXECUTE.........................................................................................138
Examples of GRANT EXECUTE....................................................................................................139
GRANT SCHEMA Statement.............................................................................................................140
Syntax Description of GRANT SCHEMA.....................................................................................140
Considerations for GRANT SCHEMA..........................................................................................142
Examples of GRANT SCHEMA....................................................................................................143
INSERT Statement...............................................................................................................................144
Syntax Description of INSERT.......................................................................................................144
Considerations for INSERT...........................................................................................................145
Examples of INSERT......................................................................................................................146
LOCK TABLE Statement.....................................................................................................................149
Syntax Description of LOCK TABLE.............................................................................................149
Considerations for LOCK TABLE..................................................................................................149
Examples of LOCK TABLE............................................................................................................150
MERGE INTO Statement....................................................................................................................151
Syntax Description of MERGE INTO............................................................................................151
Considerations for MERGE INTO.................................................................................................151
Example of MERGE INTO.............................................................................................................153
PREPARE Statement...........................................................................................................................154
Syntax Description of PREPARE...................................................................................................154
Considerations for PREPARE........................................................................................................154
Examples of PREPARE..................................................................................................................154
REVOKE Statement.............................................................................................................................156
Syntax Description of REVOKE.....................................................................................................156
Considerations for REVOKE.........................................................................................................157
Examples of REVOKE....................................................................................................................157
REVOKE EXECUTE Statement...........................................................................................................158
Syntax Description of REVOKE EXECUTE...................................................................................158
Considerations for REVOKE EXECUTE........................................................................................159
Examples of REVOKE EXECUTE..................................................................................................159
REVOKE SCHEMA Statement............................................................................................................160
6 Table of Contents

Syntax Description of REVOKE SCHEMA....................................................................................160
Considerations for REVOKE SCHEMA........................................................................................162
Examples of REVOKE SCHEMA...................................................................................................162
ROLLBACK WORK Statement...........................................................................................................163
Syntax Description of ROLLBACK WORK...................................................................................163
Considerations for ROLLBACK WORK........................................................................................163
Example of ROLLBACK WORK....................................................................................................163
SELECT Statement..............................................................................................................................164
Syntax Description of SELECT......................................................................................................166
Considerations for SELECT...........................................................................................................173
Considerations for Select List........................................................................................................175
Considerations for SEQUENCE BY...............................................................................................175
Considerations for GROUP BY......................................................................................................175
Considerations for ORDER BY......................................................................................................176
Considerations for UNION...........................................................................................................176
Examples of SELECT.....................................................................................................................178
SET SCHEMA Statement....................................................................................................................185
Syntax Description of SET SCHEMA............................................................................................185
Consideration for SET SCHEMA...................................................................................................185
Examples of SET SCHEMA...........................................................................................................185
SET TABLE TIMEOUT Statement.......................................................................................................186
Syntax Description of SET TABLE TIMEOUT...............................................................................186
Considerations for SET TABLE TIMEOUT....................................................................................187
Examples of SET TABLE TIMEOUT..............................................................................................187
SET TRANSACTION Statement.........................................................................................................188
Syntax Description of SET TRANSACTION.................................................................................188
Considerations for SET TRANSACTION......................................................................................190
Examples of SET TRANSACTION................................................................................................192
SHOWDDL Statement........................................................................................................................193
Syntax Description of SHOWDDL................................................................................................193
Considerations for SHOWDDL.....................................................................................................193
Examples of SHOWDDL...............................................................................................................194
TABLE Statement................................................................................................................................197
Considerations for TABLE.............................................................................................................197
Examples of TABLE.......................................................................................................................197
UPDATE Statement.............................................................................................................................198
Syntax Description of UPDATE.....................................................................................................198
Considerations for UPDATE..........................................................................................................200
Examples of UPDATE....................................................................................................................202
UPDATE STATISTICS Statement........................................................................................................204
Syntax Description of UPDATE STATISTICS................................................................................205
Considerations for UPDATE STATISTICS.....................................................................................207
Examples of UPDATE STATISTICS...............................................................................................209
VALUES Statement.............................................................................................................................210
Considerations for VALUES..........................................................................................................210
Examples of VALUES....................................................................................................................210
3 SQL Utilities................................................................................................................211
Privileges Required to Execute Utilities .............................................................................................211
MAINTAIN Command.......................................................................................................................212
Syntax Description of MAINTAIN................................................................................................213
MAINTAIN MVGROUP................................................................................................................217
Examples of MAINTAIN...............................................................................................................217
POPULATE INDEX Utility.................................................................................................................220
Table of Contents 7

Syntax Description of POPULATE INDEX....................................................................................220
Considerations for POPULATE INDEX........................................................................................220
Examples of POPULATE INDEX...................................................................................................221
PURGEDATA Utility...........................................................................................................................222
Syntax Description of PURGEDATA.............................................................................................222
Considerations for PURGEDATA..................................................................................................222
Example of PURGEDATA.............................................................................................................223
REPLICATE Command.......................................................................................................................224
Syntax Description of REPLICATE ...............................................................................................225
Considerations for REPLICATE ....................................................................................................226
Examples of REPLICATE...............................................................................................................230
4 SQL Language Elements...........................................................................................233
Character Sets......................................................................................................................................234
Columns..............................................................................................................................................235
Column References........................................................................................................................235
Derived Column Names................................................................................................................235
Column Default Settings................................................................................................................236
Constraints..........................................................................................................................................237
Creating, Adding, and Dropping Constraints on SQL Tables.......................................................237
Correlation Names..............................................................................................................................238
Explicit Correlation Names...........................................................................................................238
Implicit Correlation Names...........................................................................................................238
Examples of Correlation Names....................................................................................................238
Cursors................................................................................................................................................239
ANSI Holdable Cursors in JDBC Client Applications..................................................................239
Database Objects.................................................................................................................................241
Ownership.....................................................................................................................................241
Database Object Names......................................................................................................................242
Logical Names for SQL Objects.....................................................................................................242
SQL Object Namespaces................................................................................................................242
Data Types...........................................................................................................................................243
Comparable and Compatible Data Types.....................................................................................245
Character String Data Types..........................................................................................................248
Datetime Data Types......................................................................................................................250
Interval Data Types........................................................................................................................251
Numeric Data Types .....................................................................................................................253
Expressions..........................................................................................................................................255
Character Value Expressions.........................................................................................................255
Datetime Value Expressions..........................................................................................................257
Interval Value Expressions............................................................................................................261
Numeric Value Expressions...........................................................................................................265
Identifiers............................................................................................................................................269
Regular Identifiers.........................................................................................................................269
Delimited Identifiers......................................................................................................................269
Examples of Identifiers..................................................................................................................269
Indexes................................................................................................................................................271
SQL Indexes...................................................................................................................................271
Keys.....................................................................................................................................................272
Clustering Keys..............................................................................................................................272
SYSKEY..........................................................................................................................................272
Hash Partition Keys.......................................................................................................................273
Index Keys.....................................................................................................................................273
Primary Keys.................................................................................................................................273
8 Table of Contents

Literals.................................................................................................................................................274
Character String Literals................................................................................................................274
Datetime Literals............................................................................................................................275
Interval Literals..............................................................................................................................277
Numeric Literals............................................................................................................................279
Materialized Views.............................................................................................................................281
Types of Materialized Views..........................................................................................................281
MVGROUPs .......................................................................................................................................282
Null......................................................................................................................................................283
Using Null Versus Default Values.................................................................................................283
Defining Columns That Allow or Prohibit Null............................................................................283
Partitions.............................................................................................................................................285
Hash Partition Keys.......................................................................................................................285
Automatically Creating Partitions.................................................................................................285
Predicates............................................................................................................................................286
BETWEEN Predicate......................................................................................................................286
Comparison Predicates..................................................................................................................287
EXISTS Predicate............................................................................................................................291
IN Predicate...................................................................................................................................292
LIKE Predicate...............................................................................................................................294
NULL Predicate.............................................................................................................................296
Quantified Comparison Predicates...............................................................................................297
Privileges.............................................................................................................................................301
Considerations...............................................................................................................................301
Schemas...............................................................................................................................................304
Search Condition.................................................................................................................................305
Considerations for Search Condition............................................................................................305
Examples of Search Condition.......................................................................................................306
Subquery.............................................................................................................................................307
SELECT Form of a Subquery.........................................................................................................307
Using Subqueries to Provide Comparison Values.........................................................................307
Nested Subqueries When Providing Comparison Values.............................................................307
Correlated Subqueries When Providing Comparison Values.......................................................308
Synonyms............................................................................................................................................309
Tables...................................................................................................................................................310
Base Tables and Views...................................................................................................................310
Example of a Base Table.................................................................................................................310
Triggers................................................................................................................................................311
Views...................................................................................................................................................312
SQL Views......................................................................................................................................312
Example of a View.........................................................................................................................312
5 SQL Clauses...............................................................................................................313
DEFAULT Clause................................................................................................................................314
Examples of DEFAULT..................................................................................................................315
FORMAT Clause.................................................................................................................................316
Considerations for Date Formats...................................................................................................317
Considerations for Other Formats.................................................................................................317
Examples of FORMAT...................................................................................................................317
SAMPLE Clause..................................................................................................................................318
Considerations for SAMPLE..........................................................................................................319
Examples of SAMPLE....................................................................................................................320
SEQUENCE BY Clause.......................................................................................................................326
Considerations for SEQUENCE BY...............................................................................................326
Table of Contents 9

Examples of SEQUENCE BY.........................................................................................................327
TRANSPOSE Clause...........................................................................................................................329
Considerations for TRANSPOSE...................................................................................................330
Examples of TRANSPOSE.............................................................................................................331
6 SQL Functions and Expressions................................................................................337
Categories............................................................................................................................................337
Standard Normalization................................................................................................................337
Aggregate (Set) Functions..............................................................................................................337
Character String Functions............................................................................................................338
Datetime Functions........................................................................................................................339
Mathematical Functions................................................................................................................340
Sequence Functions.......................................................................................................................341
Other Functions and Expressions..................................................................................................343
ABS Function.......................................................................................................................................344
Example of ABS.............................................................................................................................344
ACOS Function...................................................................................................................................345
Examples of ACOS........................................................................................................................345
ADD_MONTHS Function...................................................................................................................346
Examples of ADD_MONTHS........................................................................................................346
ASCII Function....................................................................................................................................347
Considerations for ASCII...............................................................................................................347
Example of ASCII..........................................................................................................................347
ASIN Function.....................................................................................................................................348
Examples of ASIN..........................................................................................................................348
ATAN Function...................................................................................................................................349
Examples of ATAN........................................................................................................................349
ATAN2 Function.................................................................................................................................350
Example of ATAN2........................................................................................................................350
AVG Function......................................................................................................................................351
Considerations for AVG.................................................................................................................351
Examples of AVG...........................................................................................................................351
BITAND Function...............................................................................................................................353
Considerations for BITAND..........................................................................................................353
Restrictions for BITAND................................................................................................................353
Examples of BITAND.....................................................................................................................353
CASE (Conditional) Expression..........................................................................................................354
Considerations for CASE...............................................................................................................355
Examples of CASE.........................................................................................................................355
CAST Expression.................................................................................................................................357
Considerations for CAST...............................................................................................................357
Valid Conversions for CAST .........................................................................................................357
Examples of CAST.........................................................................................................................357
CEILING Function..............................................................................................................................359
Example of CEILING.....................................................................................................................359
CHAR Function...................................................................................................................................360
Considerations for CHAR..............................................................................................................360
Example of CHAR.........................................................................................................................360
CHAR_LENGTH Function.................................................................................................................361
Considerations for CHAR_LENGTH............................................................................................361
Examples of CHAR_LENGTH......................................................................................................361
COALESCE Function..........................................................................................................................362
Example of COALESCE.................................................................................................................362
CODE_VALUE Function.....................................................................................................................363
10 Table of Contents

Example of CODE_VALUE Function............................................................................................363
CONCAT Function..............................................................................................................................364
Concatenation Operator (||).........................................................................................................364
Considerations for CONCAT.........................................................................................................364
Examples of CONCAT...................................................................................................................364
CONVERTTOHEX Function...............................................................................................................366
Considerations for CONVERTTOHEX..........................................................................................366
Examples of CONVERTTOHEX....................................................................................................366
CONVERTTIMESTAMP Function......................................................................................................368
Considerations for CONVERTTIMESTAMP.................................................................................368
Examples of CONVERTTIMESTAMP...........................................................................................368
COS Function......................................................................................................................................369
Example of COS.............................................................................................................................369
COSH Function...................................................................................................................................370
Example of COSH..........................................................................................................................370
COUNT Function................................................................................................................................371
Considerations for COUNT...........................................................................................................371
Examples of COUNT.....................................................................................................................371
CURRENT Function............................................................................................................................373
Example of CURRENT..................................................................................................................373
CURRENT_DATE Function................................................................................................................374
Examples of CURRENT_DATE.....................................................................................................374
CURRENT_ROLE Function................................................................................................................375
Considerations for CURRENT_ROLE...........................................................................................375
Example of CURRENT_ROLE.......................................................................................................375
CURRENT_TIME Function.................................................................................................................376
Example of CURRENT_TIME.......................................................................................................376
CURRENT_TIMESTAMP Function....................................................................................................377
Example of CURRENT_TIMESTAMP...........................................................................................377
CURRENT_USER Function................................................................................................................378
Considerations for CURRENT_USER...........................................................................................378
Example of CURRENT_USER.......................................................................................................378
DATE_ADD Function.........................................................................................................................379
Examples of DATE_ADD...............................................................................................................379
DATE_SUB Function...........................................................................................................................380
Examples of DATE_SUB................................................................................................................380
DATEADD Function...........................................................................................................................381
Examples of DATEADD................................................................................................................381
DATEDIFF Function............................................................................................................................382
Examples of DATEDIFF.................................................................................................................382
DATEFORMAT Function....................................................................................................................383
Considerations for DATEFORMAT...............................................................................................383
Examples of DATEFORMAT.........................................................................................................383
DATE_PART Function (of an Interval)...............................................................................................384
Examples of DATE_PART..............................................................................................................384
DATE_PART Function (of a Timestamp)............................................................................................385
Examples of DATE_PART..............................................................................................................385
DATE_TRUNC Function.....................................................................................................................386
Examples of DATE_TRUNC..........................................................................................................386
DAY Function......................................................................................................................................387
Example of DAY.............................................................................................................................387
DAYNAME Function..........................................................................................................................388
Considerations for DAYNAME.....................................................................................................388
Example of DAYNAME.................................................................................................................388
DAYOFMONTH Function..................................................................................................................389
Table of Contents 11

Examples of DAYOFMONTH.......................................................................................................389
DAYOFWEEK Function......................................................................................................................390
Example of DAYOFWEEK.............................................................................................................390
DAYOFYEAR Function.......................................................................................................................391
Example of DAYOFYEAR..............................................................................................................391
DECODE Function..............................................................................................................................392
Considerations for DECODE.........................................................................................................392
Examples of DECODE...................................................................................................................393
DEGREES Function.............................................................................................................................395
Examples of DEGREES..................................................................................................................395
DIFF1 Function....................................................................................................................................396
Considerations for DIFF1...............................................................................................................396
Examples of DIFF1.........................................................................................................................396
DIFF2 Function....................................................................................................................................398
Considerations for DIFF2...............................................................................................................398
Examples of DIFF2.........................................................................................................................398
EXP Function.......................................................................................................................................400
Examples of EXP............................................................................................................................400
EXPLAIN Function.............................................................................................................................401
Considerations for EXPLAIN Function.........................................................................................401
Examples of EXPLAIN Function...................................................................................................403
EXTRACT Function.............................................................................................................................404
Examples of EXTRACT..................................................................................................................404
FLOOR Function.................................................................................................................................405
Examples of FLOOR......................................................................................................................405
HOUR Function..................................................................................................................................406
Example of HOUR.........................................................................................................................406
INSERT Function................................................................................................................................407
Examples of INSERT......................................................................................................................407
ISNULL Function................................................................................................................................408
Examples of ISNULL.....................................................................................................................408
JULIANTIMESTAMP Function...........................................................................................................409
Considerations for JULIANTIMESTAMP.....................................................................................409
Examples of JULIANTIMESTAMP................................................................................................409
LASTNOTNULL Function..................................................................................................................410
Example of LASTNOTNULL.........................................................................................................410
LCASE Function..................................................................................................................................411
Considerations for LCASE.............................................................................................................411
Example of LCASE........................................................................................................................411
LEFT Function.....................................................................................................................................412
Examples of LEFT..........................................................................................................................412
LOCATE Function...............................................................................................................................413
Considerations for LOCATE..........................................................................................................413
Examples of LOCATE....................................................................................................................413
LOG Function......................................................................................................................................414
Example of LOG............................................................................................................................414
LOG10 Function..................................................................................................................................415
Example of LOG10.........................................................................................................................415
LOWER Function................................................................................................................................416
Considerations for LOWER...........................................................................................................416
Example of LOWER.......................................................................................................................416
LPAD Function....................................................................................................................................417
Examples of LPAD.........................................................................................................................417
LTRIM Function..................................................................................................................................418
Considerations for LTRIM.............................................................................................................418
12 Table of Contents

Example of LTRIM.........................................................................................................................418
MAX/MAXIMUM Function................................................................................................................419
Considerations for MAX/MAXIMUM...........................................................................................419
Example of MAX/MAXIMUM.......................................................................................................419
MIN Function......................................................................................................................................420
Considerations for MIN.................................................................................................................420
Example of MIN.............................................................................................................................420
MINUTE Function...............................................................................................................................421
Example of MINUTE.....................................................................................................................421
MOD Function....................................................................................................................................422
Example of MOD...........................................................................................................................422
MONTH Function...............................................................................................................................423
Example of MONTH......................................................................................................................423
MONTHNAME Function...................................................................................................................424
Considerations for MONTHNAME..............................................................................................424
Example of MONTHNAME..........................................................................................................424
MOVINGAVG Function......................................................................................................................425
Example of MOVINGAVG............................................................................................................425
MOVINGCOUNT Function................................................................................................................426
Considerations for MOVINGCOUNT...........................................................................................426
Example of MOVINGCOUNT.......................................................................................................426
MOVINGMAX Function.....................................................................................................................427
Example of MOVINGMAX...........................................................................................................427
MOVINGMIN Function......................................................................................................................428
Example of MOVINGMIN............................................................................................................428
MOVINGSTDDEV Function...............................................................................................................429
Example of MOVINGSTDDEV......................................................................................................429
MOVINGSUM Function.....................................................................................................................431
Example of MOVINGSUM............................................................................................................431
MOVINGVARIANCE Function..........................................................................................................432
Example of MOVINGVARIANCE.................................................................................................432
NULLIF Function................................................................................................................................434
Example of NULLIF.......................................................................................................................434
NULLIFZERO Function......................................................................................................................435
Examples of NULLIFZERO...........................................................................................................435
NVL Function......................................................................................................................................436
Examples of NVL...........................................................................................................................436
OCTET_LENGTH Function................................................................................................................437
Considerations for OCTET_LENGTH...........................................................................................437
Example of OCTET_LENGTH.......................................................................................................437
OFFSET Function................................................................................................................................438
Example of OFFSET.......................................................................................................................438
PI Function..........................................................................................................................................439
Example of PI.................................................................................................................................439
POSITION Function............................................................................................................................440
Considerations for POSITION.......................................................................................................440
Examples of POSITION.................................................................................................................440
POWER Function................................................................................................................................441
Examples of POWER.....................................................................................................................441
QUARTER Function............................................................................................................................442
Example of QUARTER..................................................................................................................442
RADIANS Function............................................................................................................................443
Examples of RADIANS..................................................................................................................443
RANK/RUNNINGRANK Function....................................................................................................444
Considerations for RANK/RUNNINGRANK...............................................................................444
Table of Contents 13

Examples of RANK/RUNNINGRANK.........................................................................................444
REPEAT Function................................................................................................................................447
Example of REPEAT......................................................................................................................447
REPLACE Function.............................................................................................................................448
Example of REPLACE...................................................................................................................448
RIGHT Function..................................................................................................................................449
Examples of RIGHT.......................................................................................................................449
ROUND Function................................................................................................................................450
Examples of ROUND.....................................................................................................................450
ROWS SINCE Function.......................................................................................................................451
Considerations for ROWS SINCE..................................................................................................451
Examples of ROWS SINCE............................................................................................................451
ROWS SINCE CHANGED Function..................................................................................................453
Considerations for ROWS SINCE CHANGED.............................................................................453
Examples of ROWS SINCE CHANGED........................................................................................453
RPAD Function...................................................................................................................................454
Examples of RPAD Function.........................................................................................................454
RTRIM Function..................................................................................................................................455
Considerations for RTRIM.............................................................................................................455
Example of RTRIM........................................................................................................................455
RUNNINGAVG Function...................................................................................................................456
Considerations for RUNNINGAVG..............................................................................................456
Example of RUNNINGAVG..........................................................................................................456
RUNNINGCOUNT Function..............................................................................................................457
Considerations for RUNNINGCOUNT........................................................................................457
Example of RUNNINGCOUNT....................................................................................................457
RUNNINGMAX Function..................................................................................................................458
Example of RUNNINGMAX.........................................................................................................458
RUNNINGMIN Function...................................................................................................................459
Example of RUNNINGMIN..........................................................................................................459
RUNNINGRANK Function................................................................................................................459
RUNNINGSTDDEV Function............................................................................................................460
Considerations for RUNNINGSTDDEV.......................................................................................460
Examples of RUNNINGSTDDEV..................................................................................................460
RUNNINGSUM Function...................................................................................................................461
Example of RUNNINGSUM..........................................................................................................461
RUNNINGVARIANCE Function........................................................................................................462
Examples of RUNNINGVARIANCE.............................................................................................462
SECOND Function..............................................................................................................................463
Example of SECOND.....................................................................................................................463
SIGN Function.....................................................................................................................................464
Examples of SIGN..........................................................................................................................464
SIN Function.......................................................................................................................................465
Example of SIN..............................................................................................................................465
SINH Function....................................................................................................................................466
Example of SINH...........................................................................................................................466
SPACE Function..................................................................................................................................467
Example of SPACE.........................................................................................................................467
SQRT Function....................................................................................................................................468
Example of SQRT...........................................................................................................................468
STDDEV Function...............................................................................................................................469
Considerations for STDDEV..........................................................................................................469
Examples of STDDEV....................................................................................................................470
SUBSTRING/SUBSTR Function..........................................................................................................471
Alternative Forms..........................................................................................................................471
14 Table of Contents

Considerations for SUBSTRING/SUBSTR.....................................................................................471
Examples of SUBSTRING/SUBSTR...............................................................................................472
SUM Function.....................................................................................................................................473
Considerations for SUM................................................................................................................473
Example of SUM............................................................................................................................473
TAN Function......................................................................................................................................474
Example of TAN............................................................................................................................474
TANH Function...................................................................................................................................475
Example of TANH.........................................................................................................................475
THIS Function.....................................................................................................................................476
Considerations for THIS................................................................................................................476
Example of THIS............................................................................................................................476
TIMESTAMPADD Function................................................................................................................477
Examples of TIMESTAMPADD.....................................................................................................477
TIMESTAMPDIFF Function................................................................................................................478
Examples of TIMESTAMPDIFF.....................................................................................................478
TRANSLATE Function........................................................................................................................479
TRIM Function....................................................................................................................................480
Considerations for TRIM...............................................................................................................480
Examples of TRIM.........................................................................................................................480
UCASE Function.................................................................................................................................481
Considerations for UCASE............................................................................................................481
Examples of UCASE......................................................................................................................481
UPPER Function..................................................................................................................................482
Considerations for UPPER.............................................................................................................482
Example of UPPER........................................................................................................................482
UPSHIFT Function..............................................................................................................................483
Considerations for UPSHIFT.........................................................................................................483
Examples of UPSHIFT...................................................................................................................483
USER Function....................................................................................................................................484
Considerations for USER...............................................................................................................484
Example of USER...........................................................................................................................484
VARIANCE Function..........................................................................................................................485
Considerations for VARIANCE.....................................................................................................485
Examples of VARIANCE...............................................................................................................486
WEEK Function...................................................................................................................................487
Example of WEEK.........................................................................................................................487
YEAR Function....................................................................................................................................488
Example of YEAR..........................................................................................................................488
ZEROIFNULL Function......................................................................................................................489
Example of ZEROIFNULL.............................................................................................................489
7 OLAP Functions..........................................................................................................491
Considerations for Window Functions...............................................................................................491
ORDER BY Clause Supports Expressions For OLAP Functions...................................................491
Limitations for Window Functions................................................................................................492
AVG Window Function.......................................................................................................................493
Examples of AVG Window Function............................................................................................493
COUNT Window Function.................................................................................................................495
Examples of COUNT Window Function.......................................................................................495
DENSE_RANK Window Funtion.......................................................................................................497
Examples of DENSE_RANK Window Function...........................................................................497
MAX Window Function......................................................................................................................498
Examples of MAX Window Function............................................................................................498
Table of Contents 15

MIN Window Function.......................................................................................................................500
Examples of MIN Window Function.............................................................................................500
RANK Window Function....................................................................................................................502
Examples of RANK Window Function.........................................................................................502
ROW_NUMBER Window Function...................................................................................................503
Examples of ROW_NUMBER Window Function.........................................................................503
STDDEV Window Function................................................................................................................504
Examples of STDDEV....................................................................................................................504
SUM Window Function......................................................................................................................505
Examples of SUM Window Function............................................................................................505
VARIANCE Window Function...........................................................................................................507
Examples of VARIANCE Window Function.................................................................................507
A Mapping Tables........................................................................................................509
B Reserved Words.........................................................................................................519
Reserved Neoview SQL Identifiers ....................................................................................................519
C Limits...........................................................................................................................523
D Standard SQL and Neoview SQL............................................................................525
ANSI SQL Standards...........................................................................................................................525
ISO Standards......................................................................................................................................525
Neoview SQL Compliance..................................................................................................................526
Neoview SQL Extensions to Standard SQL........................................................................................529
Character Set Support.........................................................................................................................529
E Special Neoview Features—Enabled Upon Request..............................................531
Mode_Special_2..................................................................................................................................531
Features Supported in Mode_Special_2........................................................................................532
Index...............................................................................................................................535
16 Table of Contents

List of Tables
1-1 Concurrent DDL/Utility Operation and File Access Modes.........................................................29
1-2 Concurrent DDL/Utility and DML Operations.............................................................................30
1-3 Concurrent DML and DDL Operations .......................................................................................30
1-4 Operations Effect on Table Timestamps .......................................................................................31
2-1 Precedence of Data Types During Suitable Key Searches.............................................................80
2-2 Maximum Row Sizes Available.....................................................................................................87
2-3 EXPLAIN Statement Options......................................................................................................124
2-4 Plan Summary Information.........................................................................................................126
2-5 Node Listing Information ...........................................................................................................127
2-6 Cost Factors of DETAIL_COST column......................................................................................131
2-7 GET SERVICE Output.................................................................................................................134
A-1 One-to-One Uppercase and Titlecase to Lowercase Mappings .................................................509
A-2 One-to-One UCS2 Mappings ......................................................................................................512
A-3 Two-Character UCS2 Mapping ..................................................................................................515
A-4 Three-Character UCS2 Mapping ................................................................................................518
B-1 Reserved SQL Identifiers — A.....................................................................................................519
B-2 Reserved SQL Identifiers — B.....................................................................................................519
B-3 Reserved SQL Identifiers — C.....................................................................................................519
B-4 Reserved SQL Identifiers — D.....................................................................................................519
B-5 Reserved SQL Identifiers — E.....................................................................................................520
B-6 Reserved SQL Identifiers — F.....................................................................................................520
B-7 Reserved SQL Identifiers — G.....................................................................................................520
B-8 Reserved SQL Identifiers — H....................................................................................................520
B-9 Reserved SQL Identifiers — I......................................................................................................520
B-10 Reserved SQL Identifiers — J......................................................................................................520
B-11 Reserved SQL Identifiers — K.....................................................................................................520
B-12 Reserved SQL Identifiers — L.....................................................................................................520
B-13 Reserved SQL Identifiers — M....................................................................................................520
B-14 Reserved SQL Identifiers — N....................................................................................................521
B-15 Reserved SQL Identifiers — O.....................................................................................................521
B-16 Reserved SQL Identifiers — P.....................................................................................................521
B-17 Reserved SQL Identifiers — Q.....................................................................................................521
B-18 Reserved SQL Identifiers — R.....................................................................................................521
B-19 Reserved SQL Identifiers — S.....................................................................................................521
B-20 Reserved SQL Identifiers — T.....................................................................................................521
B-21 Reserved SQL Identifiers — U.....................................................................................................522
B-22 Reserved SQL Identifiers — V.....................................................................................................522
B-23 Reserved SQL Identifiers — W....................................................................................................522
B-24 Reserved SQL Identifiers — Y.....................................................................................................522
B-25 Reserved SQL Identifiers — Z.....................................................................................................522
17

18

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 one
of these client-based utilities:
•The Neoview Command Interface (NCI). For information about NCI, see the Neoview
Command Interface (NCI) Guide.
•The HP Database Manager. For information about HPDM, see the HP Database Manager
(HPDM) User Guide.
NOTE: In this manual, SQL language elements, statements, and clauses within statements are
based on the ANSI SQL:1999 standard.
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.
You 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
This version of the manual provides the following new and changed information:
•New persistent sample tables. See “UPDATE STATISTICS Statement” (page 204).
•New command for bulk replication. See “REPLICATE Command” (page 224) .
•New information regarding DISK POOL clause in the CREATE TABLE statement. See
“Considerations for DISK POOL” (page 92).
•New NO LOAD option in the CREATE TABLE statement. See “CREATE TABLE Statement”
(page 72)
•Added STORE BY clause. See “CREATE TABLE Statement” (page 72).
•Added SYSKEY information. See “SYSKEY” (page 272).
•Added new considerations for Extended Numeric Precision data type. See “Extended
Numeric Precision” (page 246).
•From R2.4 SP1:
— Added MAINTAIN command OVERRIDE option. See “MAINTAIN Command”
(page 212).
—Added extended support for delimited identifiers. See “Delimited Identifiers” (page 269).
— Added ANSI holdable cursor support. See “Cursors” (page 239).
•From R2.4 SP2:
— Added how to obtain EXPLAIN plans while queries are running. See “EXPLAIN
Statement” (page 124) and “EXPLAIN Function” (page 401).
Document Organization
•Chapter 1: “Introduction” (page 25), introduces Neoview SQL and covers topics such as
database security, data consistency and integrity, transaction management, and ANSI
compliance.
•Chapter 2: “SQL Statements” (page 37), describes the SQL statements supported by Neoview
SQL.
Intended Audience 19

•Chapter 3: “SQL Utilities” (page 211), describes utilities that perform tasks such as
maintenance, populating indexes, purging data from tables and indexes, and replicating
schemas, data, and statistics.
•Chapter 4: “SQL Language Elements” (page 233), 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: “SQL Clauses” (page 313), describes clauses used by Neoview SQL statements.
•Chapter 6: “SQL Functions and Expressions” (page 337), describes specific functions and
expressions that you can use in Neoview SQL statements.
•Chapter 7: “OLAP Functions” (page 491), describes specific on line analytical processing
functions.
•Appendix A: “Mapping Tables” (page 509), lists mappings for the UCS2 character set.
•Appendix B: “Reserved Words” (page 519), lists the words that are reserved in Neoview
SQL.
•Appendix C: “Limits” (page 523), describes limits in Neoview SQL.
•Appendix D: “Standard SQL and Neoview SQL” (page 525), describes how Neoview SQL
conforms to the ANSI standard.
•Appendix E: “ Special Neoview Features—Enabled Upon Request” (page 531), provides
information about special Neoview features including Mode_special_2 and its use.
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:
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
20