Neoview SQL Reference Manual (R2.4)

ManualsBrandsHP ManualsSoftwareHP Neoview Release 2.4 Software

HP Neoview SQL Reference Manual

HP Part Number: 546189-002

Published: August 2009

Edition: HP Neoview Release 2.4

Summary of content (530 pages)

PAGE 1
HP Neoview SQL Reference Manual HP Part Number: 546189-002 Published: August 2009 Edition: HP Neoview Release 2.
PAGE 2
© Copyright 2009 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.
PAGE 3
Table of Contents About This Document.......................................................................................................19 Intended Audience................................................................................................................................19 New and Changed Information in This Edition...................................................................................19 Document Organization.......................................................................
PAGE 4
Trigger Statements...........................................................................................................................41 Stored Procedure Statements...........................................................................................................41 ALTER MATERIALIZED VIEW Statement..........................................................................................42 Syntax Description of ALTER MATERIALIZED VIEW............................................................
PAGE 5
Syntax Description of CREATE TABLE...........................................................................................72 Considerations for CREATE TABLE................................................................................................76 Considerations for CREATE SET TABLE........................................................................................76 Considerations for CREATE VOLATILE TABLE............................................................................
PAGE 6
Considerations for DROP TRIGGER.............................................................................................115 Example of DROP TRIGGER.........................................................................................................115 DROP VIEW Statement.......................................................................................................................116 Syntax Description of DROP VIEW...............................................................................
PAGE 7
Examples of REVOKE SCHEMA...................................................................................................160 ROLLBACK WORK Statement...........................................................................................................161 Syntax Description of ROLLBACK WORK...................................................................................161 Considerations for ROLLBACK WORK........................................................................................
PAGE 8
4 SQL Language Elements...........................................................................................221 Character Sets......................................................................................................................................222 Columns..............................................................................................................................................223 Column References........................................................................
PAGE 9
Comparison Predicates..................................................................................................................268 EXISTS Predicate............................................................................................................................272 IN Predicate...................................................................................................................................273 LIKE Predicate....................................................................
PAGE 10
Example of ABS.............................................................................................................................324 ACOS Function...................................................................................................................................325 Examples of ACOS........................................................................................................................325 ADD_MONTHS Function..............................................................
PAGE 11
Considerations for COUNT...........................................................................................................350 Examples of COUNT.....................................................................................................................350 CURRENT Function............................................................................................................................352 Example of CURRENT.............................................................................
PAGE 12
EXP Function.......................................................................................................................................379 Examples of EXP............................................................................................................................379 EXPLAIN Function.............................................................................................................................380 Considerations for EXPLAIN Function......................................
PAGE 13
MOVINGAVG Function......................................................................................................................408 Example of MOVINGAVG............................................................................................................408 MOVINGCOUNT Function................................................................................................................409 Considerations for MOVINGCOUNT..........................................................................
PAGE 14
Examples of RPAD Function.........................................................................................................437 RTRIM Function..................................................................................................................................438 Considerations for RTRIM.............................................................................................................438 Example of RTRIM.............................................................................
PAGE 15
TRANSLATE Function........................................................................................................................462 TRIM Function....................................................................................................................................463 Considerations for TRIM...............................................................................................................463 Examples of TRIM...................................................................
PAGE 16
A Quick Reference........................................................................................................499 B Reserved Words.........................................................................................................501 Reserved Neoview SQL Identifiers ....................................................................................................501 C Limits....................................................................................................................
PAGE 17
List of Tables 1-1 1-2 1-3 1-4 2-1 2-2 2-3 2-4 2-5 2-6 2-7 6-1 6-2 6-3 6-4 B-1 Concurrent DDL/Utility Operation and File Access Modes.........................................................32 Concurrent DDL/Utility and DML Operations.............................................................................32 Concurrent DML and DDL Operations .......................................................................................32 Operations Effect on Table Timestamps ...................................
PAGE 18
PAGE 19
About This Document This manual describes reference information about the syntax of SQL statements, functions, and other SQL language elements supported by the Neoview database software. The Neoview SQL statements and utilities are entered interactively or from script files using the client-based utility, Neoview Command Interface (NCI). For information on NCI, see the Neoview Command Interface (NCI) Guide.
PAGE 20
Chapter New or Changed Information Chapter 4 (page 221) • “Data Types” (page 229) — “Numeric Data Types” (page 232) corrections and added extended numeric precision support — “Comparable and Compatible Data Types” (page 231) corrections to CHAR and VARCHAR maximum values, removed LONG VARCHAR • “Delimited Identifiers” (page 252) corrections to limitations • Added entry for “LIKE Predicate” (page 275).
PAGE 21
Document Organization • • • • • • • • • • • • Chapter 1 (page 27), introduces Neoview SQL and covers topics such as database security, data consistency and integrity, transaction management, and ANSI compliance. Chapter 2 (page 39), describes the SQL statements supported by Neoview SQL. Chapter 3 (page 209), describes utilities that perform tasks such as maintenance, populating indexes, purging data from tables and indexes, and reorganizing and reloading data in a table or index.
PAGE 22
DATETIME [start-field TO] end-field A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: DROP SCHEMA schema [CASCADE] [RESTRICT] DROP SCHEMA schema [ CASCADE | RESTRICT ] { } Braces Braces enclose required syntax items. For example: FROM { grantee[, grantee]...
PAGE 23
Item Spacing Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: DAY (datetime-expression) DAY(datetime-expression) If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: myfile.
PAGE 24
Neoview Command Interface (NCI) Online Help Command-line help that describes the commands supported in the current operating mode of Neoview Command Interface. Neoview DB Admin Online Help Context-sensitive help topics that describe how to use the HP Neoview DB Admin management interface.
PAGE 25
Publishing History Part Number Product Version Publication Date 543533-002 HP Neoview Release 1.1 November 2006 543651-002 HP Neoview Release 2.0 April 2007 544505-001 HP Neoview Release 2.1 May 2007 544710-001 HP Neoview Release 2.3 April 2008 546189-001 HP Neoview Release 2.4 May 2009 546189-002 HP Neoview Release 2.4 August 2009 HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs.
PAGE 26
PAGE 27
1 Introduction The Neoview SQL database software allows you to use SQL statements, which comply closely to ANSI SQL:1999, to access SQL databases. This introduction describes: • • • • • • • “SQL Language” “Security” “Data Consistency and Access Options” “Database Integrity and Locking” “Transaction Management” “ANSI Compliance and Neoview SQL Extensions” “Neoview SQL Error Messages” Other sections of this manual describe the syntax and semantics of individual statements, commands, and language elements.
PAGE 28
For any DML statement, you specify access options by using the FOR option ACCESS clause and, for a SELECT statement, by using this same clause, you can also specify access options for individual tables referenced in the FROM clause. The possible settings for option in a DML statement are: “READ COMMITTED ” Specifies that the data accessed by the DML statement must be from committed rows. “READ UNCOMMITTED ” Specifies that the data accessed by the SELECT statement need not be from committed rows.
PAGE 29
SERIALIZABLE (or REPEATABLE READ) provides the highest level of data consistency. A statement executing with this access option does not allow dirty reads, nonrepeatable reads, or phantoms. SKIP CONFLICT This option allows transactions to skip rows locked in a conflicting mode by another transaction. SKIP CONFLICT is not supported at the transaction level. It can only be specified at the table or statement level.
PAGE 30
Lock mode is sometimes determined by Neoview SQL. SQL ensures that an exclusive lock is in effect for write operations and usually acquires a shared lock for operations that access data without modifying it. You choose lock mode in these instances: • • On the LOCK TABLE statement, you can choose EXCLUSIVE or SHARE. On the SELECT statement, you can specify IN EXCLUSIVE MODE or IN SHARE MODE.
PAGE 31
— — — — • • A holdable cursor A SELECT statement with an embedded UPDATE or DELETE A DDL statement An UPDATE STATISTICS statement The query plan does not choose VSBB inserts or Executor Server Process (ESP) parallelism. The AUTOCOMMIT option must be set to ON. If these conditions are not met, the transaction is aborted by Neoview SQL if a failure occurs. This behavior occurs for all INSERT, UPDATE, or DELETE statements in Neoview SQL.
PAGE 32
Table 1-1 Concurrent DDL/Utility Operation and File Access Modes Access Mode DDL Operations You Can Start READ UNCOMMITTED READ COMMITTED SERIALIZABLE ALTER TABLE attributes Allowed1 Allowed1 Waits 1 DDL operation aborts the DML operation.
PAGE 33
Table 1-3 Concurrent DML and DDL Operations (continued) DML Operations You Can Start DDL Operations in Progress SELECT UNCOMMITTED SELECT SHARE SELECT EXCLUSIVE UPDATE/ INSERT DELETE CREATE TRIGGER subject table Allowed Allowed Waits Waits CREATE TRIGGER reference table Allowed Allowed Allowed Allowed CREATE VIEW Allowed Allowed Allowed Allowed 1 GRANT Allowed Waits Waits Waits POPULATE INDEX Allowed1 Allowed2 Allowed2 Waits REVOKE Allowed1 Allowed Waits Waits UPDATE STAT
PAGE 34
If the transaction isolation level is READ UNCOMMITTED, the default access mode is READ ONLY. For READ COMMITTED, the default access mode is not READ ONLY, but you can specify READ ONLY by using the SET TRANSACTION statement.
PAGE 35
SERIALIZABLE or REPEATABLE READ This option locks all data accessed through the transaction and holds the locks on data until the end of the transaction. SERIALIZABLE provides the highest level of data consistency. A transaction executing with this isolation level does not allow dirty reads, nonrepeatable reads, or phantoms. Transaction Rollback Mode The Rollback Mode for a transaction can be set to either ON or OFF.
PAGE 36
• • • • • • • • • • • GRANT EXECUTE statement INSERT statement MERGE INTO statement PREPARE statement REVOKE statement REVOKE EXECUTE statement ROLLBACK WORK statement SELECT statement SET SCHEMA statement SET TRANSACTION statement UPDATE statement Statements That Are Neoview SQL Extensions These statements are Neoview SQL extensions to the ANSI standard.
PAGE 37
• • • • • • • • • • • • CURRENT_TIMESTAMP EXTRACT LOWER MAX MIN NULLIF OCTET_LENGTH POSITION SUBSTRING SUM TRIM UPPER All other functions are Neoview SQL extensions. Neoview SQL Error Messages Neoview SQL reports error messages and exception conditions When an error condition occurs, Neoview SQL returns a message number and a brief description of the condition. For example, Neoview SQL might display this error message: *** ERROR[1000] A syntax error occurred.
PAGE 38
PAGE 39
2 SQL Statements This section describes the syntax and semantics of Neoview SQL statements. The Neoview SQL statements and utilities are entered interactively or from script files using the command-line interface, Neoview Command Interface (NCI). For information on Neoview Command Interface, see the Neoview Command Interface (NCI) Guide.
PAGE 40
“DROP VIEW Statement” (page 116) Drops a view. “GRANT Statement” (page 133) Grants access privileges for a table or view to specified roles. “GRANT EXECUTE Statement” (page 135) Grants privileges for executing a stored procedure in Java (SPJ) to specified roles. “GRANT SCHEMA Statement” (page 137) Grants access privileges for a schema to specified roles. “REVOKE Statement” (page 154) Revokes access privileges for a table or view from specified roles.
PAGE 41
Control Statements Use these statements to control the execution default options, plans, and performance of DML statements: “SET TABLE TIMEOUT Statement” (page 185) Specifies a dynamic timeout value in the runtime environment of the current session. Object Naming Statements Use these statements to specify default ANSI names for the schema: “SET SCHEMA Statement” (page 184) Sets the default ANSI schema for unqualified object names for the current session.
PAGE 42
ALTER MATERIALIZED VIEW Statement • • “Syntax Description of ALTER MATERIALIZED VIEW” (page 42) “Considerations for ALTER MATERIALIZED VIEW” (page 42) The ALTER MATERIALIZED VIEW statement changes a materialized view. See “Database Object Names” (page 228). ALTER {MATERIALIZED VIEW | MV} name mv-alter-action mv-alter-action is: MVATTRIBUTES[S] mv-attribute | {ADD | REMOVE} IGNORE CHANGES ON simple-table [,simple-table]...
PAGE 43
ALTER MVGROUP Statement • • “Syntax Description of ALTER MVGROUP” (page 43) “Considerations for ALTER MVGROUP” (page 43) The ALTER MVGROUP statement allows you to add or remove a member from the materialized view group (MVGROUP). The ADD clause should be used when adding one or more MVs to the MVGROUP. The REMOVE clause should be used when removing one or more MVs from an MVGROUP. For information on MVGROUPS, see “MVGROUPs ” (page 264). ALTER MVGROUP mv-group-name {ADD mv-name [, mv-name] ...
PAGE 44
ALTER SYNONYM Statement • • • “Syntax Description of ALTER SYNONYM” “Considerations for ALTER SYNONYM” “Example of ALTER SYNONYM” The ALTER SYNONYM statement allows you to change the table, view, or materialized view the synonym is referencing to a different object of the same structure. You can write queries that reference the synonym and use the ALTER SYNONYM command to point to a different object with the same structure.
PAGE 45
ALTER TABLE Statement • • • “Syntax Description of ALTER TABLE” “Considerations for ALTER TABLE” “Example of ALTER TABLE” ALTER TABLE Statement 45
PAGE 46
The ALTER TABLE statement changes a Neoview SQL table. See “Database Object Names” (page 228).
PAGE 47
Syntax Description of ALTER TABLE name specifies the current name of the object. See “Database Object Names” (page 228). NO INSERTLOG | INSERTLOG specifies whether update and delete operations are recorded in the table’s IUD log. When a table is defined with the INSERTLOG attribute, logging operations ignore delete and update operations and log inserts only. This logging is needed to maintain ON REQUEST materialized views.
PAGE 48
the schema portions of the name you specify in constraint, Neoview SQL expands the constraint name by using the schema for table. See “Database Object Names” (page 228). If you do not specify a constraint name, Neoview SQL constructs an SQL identifier as the name for the constraint in the schema for table. The identifier consists of the fully qualified table name concatenated with a system-generated unique identifier. For example, a constraint on table A.B.C might be assigned a name such as A.B.C_123..._01.
PAGE 49
Considerations for ALTER TABLE Effect of Adding a Column on View Definitions The addition of a column to a table has no effect on existing view definitions. Implicit column references specified by SELECT * in view definitions are replaced by explicit column references when the definition clauses are originally evaluated. Authorization and Availability Requirements To alter a table, you must own the table, be the schema owner, or be granted the ALTER or ALTER_TABLE privilege.
PAGE 50
• • • • • • • • • The INCREMENT BY option shall not be 0 (zero) or less than 0 (zero). The INCREMENT BY option shall not be greater than the maximum value of the data type of the IDENTITY column. The INCREMENT BY or MAXVALUE options can be used only on an IDENTITY column. Only the INCREMENT BY or MAXVALUE options can be altered for an IDENTITY column. The MAXVALUE option must be greater than the current value of the internal sequence generator current value for the IDENTITY column.
PAGE 51
select * from T1; SURROGATE_KEY -------------------------------99 100 102 B ---1 2 3 ALTER TABLE Statement 51
PAGE 52
ALTER TRIGGER Statement • • “Syntax Description of ALTER TRIGGER” “Considerations for ALTER TRIGGER” The ALTER TRIGGER statement is used to enable or disable triggers, individually or by SQL tables. ALTER TRIGGER { ENABLE trigger-name | ENABLE ALL OF table-name | DISABLE trigger-name | DISABLE ALL OF table-name}; Syntax Description of ALTER TRIGGER trigger-name specifies the name of the trigger to alter. See “Database Object Names” (page 228).
PAGE 53
ALTER VIEW Statement • • “Syntax Description of ALTER VIEW” “Example of ALTER VIEW” The ALTER VIEW statement renames an object within the same schema. See “Database Object Names” (page 228). ALTER VIEW name alter-action alter-action is: RENAME TO new name Syntax Description of ALTER VIEW name specifies the current name of the object. See “Database Object Names” (page 228). RENAME TO new-name changes the logical name of the object within the same schema.
PAGE 54
BEGIN WORK Statement The BEGIN WORK statement enables you to start a transaction explicitly—where the transaction consists of the set of operations defined by the sequence of SQL statements that begins immediately after BEGIN WORK and ends with the next COMMIT or ROLLBACK statement. See “Transaction Management” (page 30). BEGIN WORK is a Neoview SQL extension.
PAGE 55
CALL Statement • • • “Syntax Description of CALL” “Considerations for CALL” “Examples of CALL” The CALL statement invokes a stored procedure in Java (SPJ) in a Neoview database. To develop, deploy, and manage SPJs, see the Neoview Guide to Stored Procedures in Java. CALL procedure-ref ([argument-list]) procedure-ref is: [schema-name.]procedure-name argument-list is: SQL-expression[{, SQL-expression}...] Syntax Description of CALL procedure-ref specifies an ANSI logical name of the form: [schema-name.
PAGE 56
Input Parameter Arguments You pass data to an SPJ by using IN or INOUT parameters.
PAGE 57
ORDERNUM ---------- NUM_PARTS -------------- AMOUNT --------------- ORDER_DATE ---------- LAST_NAME ------------------ 100210 100250 101220 200300 200320 200490 . . . 4 4 4 3 4 2 19020.00 22625.00 45525.00 52000.00 9195.00 1065.00 2006-04-10 2006-01-23 2006-07-21 2006-02-06 2006-02-17 2006-03-19 HUGHES HUGHES SCHNABL SCHAEFFER KARAJAN WEIGL --- 13 row(s) selected. ORDERNUM ---------- PARTNUM -------- UNIT_PRICE ------------ 100210 100210 100210 100210 100250 100250 . . .
PAGE 58
COMMIT WORK Statement • • “Considerations for COMMIT WORK” “Example of COMMIT WORK” The COMMIT WORK statement commits any changes to objects made during the current transaction, releases all locks on objects held by the transaction, and ends the transaction. See “Transaction Management” (page 30). COMMIT [WORK] WORK is an optional keyword that has no effect. COMMIT WORK has no effect outside of an active transaction.
PAGE 59
CREATE INDEX Statement • • • “Syntax Description of CREATE INDEX” “Considerations for CREATE INDEX” “Example of CREATE INDEX” The CREATE INDEX statement creates a Neoview SQL index based on one or more columns of a table. The CREATE VOLATILE INDEX statement creates a Neoview SQL index with a lifespan that is limited to the SQL session that the index is created. Volatile indexes are dropped automatically when the session ends. See “Database Object Names” (page 228). CREATE INDEX is a Neoview SQL extension.
PAGE 60
populate-option When you create an index and do not specify POPULATE or NO POPULATE, POPULATE is assumed. If you create an index using NO POPULATE, you must later request a POPULATE INDEX command to actually load the data. See “POPULATE INDEX Utility” (page 218). NO POPULATE specifies that the index is not to be populated when it is created. The indexes are created, but no data is written to the index, and it is marked offline. You can drop an offline index with the DROP INDEX statement.
PAGE 61
CREATE MATERIALIZED VIEW Statement • • • “Syntax Description of CREATE MATERIALIZED VIEW” “Considerations for CREATE MATERIALIZED VIEW” “Examples of CREATE MATERIALIZED VIEW” The CREATE MATERIALIZED VIEW statement creates a materialized view. See “Materialized Views” (page 263). CREATE {MATERIALIZED VIEW | MV} mv-name [column-name-list] { RECOMPUTE | REFRESH refresh-type } { INITIALIZE ON REFRESH | INITIALIZE ON CREATE } file-options AS query-expr column-name-list is: (column-name [, column-name]...
PAGE 62
Syntax Description of CREATE MATERIALIZED VIEW mv-name specifies the ANSI logical name for the materialized view to create. column-name-list specifies names for the columns in the materialized view, as well as headings for the columns. Column names in the list correspond directly to columns in the query-expr. If you omit this clause, columns in the materialized view will have the same names as the corresponding columns in the query-expr.
PAGE 63
The STORE BY clause specifies the order of rows within the physical file that holds the table, determines the physical organization of the table, and the ways you can partition the table. The storage key is referred to as the clustering index. You define the clustering index for the materialized view table using the column list. The key columns in the key-column-list must be NOT NULL columns from the materialized view query expression.
PAGE 64
STDDEV, MIN, MAX, COUNT(*), and COUNT(x). The aggregate function can include expression on the columns, such as SUM(a+b). Aggregate MVs can be of two types: — MAV on Single Table: the MAV is defined on a single base table or MV. — MAV on Explicit Join (MAJV): the MAV is defined on an inner equi-join of several base tables, MVs, or both. • RECOMPUTE materialized views: A materialized view that is initialized every time the materialized view needs to be updated with changes to its base tables.
PAGE 65
Indexes and Materialized Views Secondary indexes may be created automatically by the system for incremental MVs. These indexes are designed to enhance the performance of the REFRESH operation. For MJVs, the system-added secondary indexes are based on the underlying base tables’ clustering index columns. These indexes are not created for underlying tables with INSERTLOG attribute or those tables that are included in the MV's IGNORE CHANGES clause.
PAGE 66
• • Must be a single block SELECT-FROM-WHERE-GROUPBY query (for example, ORDER BY is not allowed). There is one exception to this rule; nested blocks are allowed when the inner block’s purpose is to extract a group by column (using a built-in function like substring or extract). Non-repeatable expressions (for example, current_time) are not supported for all types of aggregate materialized views.
PAGE 67
CREATE MVGROUP Statement The CREATE MVGROUP statement groups together materialized views that are defined on a common table to preserve database consistency. For information on MVGROUPS, see “MVGROUPs ” (page 264) and “MAINTAIN MVGROUP” (page 214). CREATE MVGROUP mv-group-name mv-group-name specifies the materialized view group name to create. Authorization and Availability Requirements The schema owner or anyone having the CREATE privilege can create an MV group.
PAGE 68
CREATE SCHEMA Statement • • • “Syntax Description of CREATE SCHEMA” “Considerations for CREATE SCHEMA” “Example of CREATE SCHEMA” The CREATE SCHEMA statement creates a Neoview SQL schema. See “Schemas” (page 285). CREATE SCHEMA schema-clause [schema-element [, schema-element] ...]] schema-clause is: schema schema-element is: table-definition | view-definition | grant-statement | index-definition Syntax Description of CREATE SCHEMA schema is a name for the new schema.
PAGE 69
CREATE SYNONYM Statement • • • “Syntax Description of CREATE SYNONYM ” “Considerations for CREATE SYNONYM” “Example of CREATE SYNONYM” The CREATE SYNONYM statement allows you to create synonyms (use an alternate name) for tables, views, or materialized views that can be used in queries. You can write queries that reference the synonym and later alter the synonym to point to a different object with the same structure.
PAGE 70
CREATE TABLE Statement • • • “Syntax Description of CREATE TABLE” “Considerations for CREATE TABLE” “Examples of CREATE TABLE” The CREATE TABLE statement creates a Neoview SQL table. The CREATE VOLATILE TABLE statement creates a Neoview SQL table in a SQL session. Volatile tables are dropped automatically when the session ends. The CREATE TABLE AS statement creates a table based on the data attributes of a SELECT query and populates the table using the data returned by the SELECT query.
PAGE 71
CREATE [SET][VOLATILE] TABLE table (table-element [,table-element]...) | table-spec | like-spec } [NO PARTITION | HASH PARTITION BY (partitioning-column, partitioning-column...)] [MAX TABLE SIZE megabytes] [DISK POOL pool_number] [ATTRIBUTE {NO INSERTLOG | INSERTLOG}] AS select-query table-spec is: (table-element [,table-element]...
PAGE 72
identity-column-specification is: identity-type [(internal-sequence-generator-options)] identity-type is: GENERATED BY DEFAULT AS IDENTITY | GENERATED ALWAYS AS IDENTITY internal-sequence-generator-options is: internal-sequence-generator-option ...
PAGE 73
NO PARTITION creates a non-partitioned table. To create a non-partitioned table, specify the NO PARTITION option in the CREATE TABLE statement. If the NO PARTITION option is not specified, a partitioned table is created. When a partitioned table is created, the table is automatically partitioned across all the disk volumes within a disk pool. HASH PARTITION BY (partitioning-column, partitioning-column...)} specifies the partitioning columns.
PAGE 74
the number of pools or if pool_number is an invalid number, an error is generated. See “DISK POOL” (page 89). NO INSERTLOG | INSERTLOG specifies whether update and delete operations are recorded in the table’s IUD log, if one exists. When a table is defined with the INSERTLOG attribute, logging operations ignore delete and update operations and log inserts only. This logging is needed to maintain ON REQUEST materialized views.
PAGE 75
MAXVALUE is specified, maximum value is the maximum value of the data type of the IDENTITY column. MINVALUE signed-numeric-literal | NO MINVALUE Positive value (not 0 (zero) or less than 0 (zero). If NO MINVALUE is specified, minimum value is the minimum value of the data type of the IDENTITY column. If the data type is LARGEINT, the minimum default value is zero, not -9223372036854775808.
PAGE 76
predicate, and POSITION/REPLACE string function searches. See “Examples of CREATE TABLE” (page 89). LIKE source-table [include-option]... directs Neoview SQL to create a table like the existing table, source-table, omitting constraints (with the exception of the NOT NULL and PRIMARY KEY constraints), and partitions unless include-option clauses are specified. source-table is the ANSI logical name for the existing table and must be unique among names of tables, views, and procedures within its schema.
PAGE 77
Example for CREATE SET TABLE In this example, the duplicate row is discarded. >> CREATE SET TABLE t1(a LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, PRIMARY KEY, b INT); --- SQL operation complete. >>insert into t1 values (1,2); --- 1 row(s) inserted. >>insert into t1 values (1,2); --- 0 row(s) inserted.
PAGE 78
How Neoview SQL Selects Suitable Keys for Volatile Tables Neoview SQL searches for the first suitable column in the list of columns of the table being created. Once the column is located, the table is partitioned on it. The searched columns in the table might be explicitly specified (as in a CREATE TABLE statement) or implicitly created (as in a CREATE TABLE AS SELECT statement).
PAGE 79
create volatile table t (a int) store by (a) partition by (a); create volatile table t (a int unique); Creating a Volatile Table With a Nullable Primary Key This example creates a volatile table with a nullable primary key: >>create volatile table t (a int, primary key(a)); --- SQL operation complete. Only one unique null value is allowed: >>insert into t values (null); --- 1 row(s) inserted. >>insert into t values (null); *** ERROR[8102] The operation is prevented by a unique constraint.
PAGE 80
Neoview SQL's CREATE TABLE LIKE support nor its behavior with respect to the IDENTITY column is ANSI compliant. CREATE TABLE LIKE Example CREATE TABLE tbl1_src( Id_col INTEGER UNSIGNED GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 2) NOT NULL, Col2 INTEGER NOT NULL, PRIMARY KEY(Id_col) ); CREATE TABLE tbl_tgt LIKE tbl1_src; These statements will result in tbl_tgt having the same column attributes and IDENTITY column attributes as table tbl1_src.
PAGE 81
CREATE TABLE t2 LIKE t1 AS SELECT a,b FROM t1; SELECT MAX(a) from t2; (EXPR) -------2 Table t2 is created with an internal sequence generator table START WITH value of 0. The current value in the internal sequence generator is 0. To recalibrate the current value of the internal sequence generator table, use the ALTER TABLE ALTER COLUMN command. CREATE TABLE LIKE AS SELECT will fail with an error if the IDENTITY column from the target table is a GENERATED ALWAYS AS IDENTITY column type.
PAGE 82
ALTER TABLE t2 ALTER COLUMN a SET INCREMENT BY 1; INSERT INTO t2 VALUES(DEFAULT,4); SELECT * FROM t2; A B ----- -----0 1 1 2 2 3 3 4 The recalibration of the internal sequence generator current value is now complete. Unique values will be generated for the IDENTITY column. Considerations for CREATE TABLE AS The CREATE TABLE AS statement is supported for the GENERATED BY DEFAULT AS IDENTITY column. The values for the GENERATED BY DEFAULT AS IDENTITY can be user-supplied or system-generated.
PAGE 83
A B 0 15 2 (Value is generated by the internal sequence generator) 1 This may cause duplicates to be experienced because the current value of the internal sequence generator is incremented to values that are already present in the IDENTITY column of table t2. To avoid this problem, HP recommends that you determine a START WITH value that is greater than the largest value of the IDENTITY column of the source table.
PAGE 84
Authorization and Availability Requirements To create a table, you must be the schema owner or have the CREATE or CREATE_TABLE privilege. Limits for Tables You can create tables and indexes with rows that are 32,708 bytes in size and blocks that are 32K bytes in size. The default block size for all create table and index operations is 32768 (32K) byte block size (in all cases). There are no tools provided for explicitly migrating an existing 4KB block size table to a 32KB block size table.
PAGE 85
If the table is partitioned then the table is automatically partitioned across all the disk volumes if the system has less than or equal to 256 disks. If the system has more than 256 disks, then the table is partitioned across half the disks on the system. Generating Values For an IDENTITY Column You can use IDENTITY columns to automatically generate unique values. The values are unique across all partitions of the table for the IDENTITY column. IDENTITY columns are declared in the CREATE TABLE statement.
PAGE 86
• • • • • • • • The IDENTITY column can be the partitioning key or can be part of a compound partitioning key. INSERT...SELECT operations are supported. CREATE SET TABLE with an IDENTITY column is supported. Duplicate rows are discarded without raising error 8102. CREATE TABLE AS with GENERATED BY DEFAULT AS IDENTITY column is supported. Inserting values for a GENERATED ALWAYS AS IDENTITY column is not allowed. CREATE TABLE LIKE with an IDENTITY column is supported.
PAGE 87
• experienced. TMF Error 73 can be experienced because the DDL and DML operations share the same user transaction on a table lock on the internal sequence generator table. For a table with only one column, which is an IDENTITY column, the tuple list cannot have only DEFAULT values. Error 3431 will be raised. You must specify the input values.
PAGE 88
NOTE: In Neoview SQL, the partitioning key must be a subset of the clustering key. In the case of a table with a single column clustering key, the partitioning key must be the same as the clustering key.
PAGE 89
MAXVALUE 1000 INCREMENT BY 2 MINVALUE 50) NOT NULL, Col2 INTEGER NOT NULL, PRIMARY KEY(Id_col) ); • This statement fails with an error stating that a table can have only one IDENTITY column.
PAGE 90
• This is an example of one-part name usage: CREATE VOLATILE TABLE vtable(a int); INSERT INTO vtable values(1); SELECT * from vtable; CREATE VOLATILE INDEX vindex on vtable(a); DROP VOLATILE INDEX vindex; DROP VOLATILE TABLE vtable; • This is an example of two-part name usage: CREATE VOLATILE TABLE “mysch”.vtable(a int); INSERT INTO mysch.vtable values(1); SELECT * from mysch.vtable; CREATE VOLATILE INDEX vindex on mysch.vtable(a); DROP VOLATILE INDEX vindex; DROP VOLATILE TABLE mysch.
PAGE 91
>>Create SET table T (a int not null, b int, primary key (a)); --- SQL operation complete. >>insert into T values(1,2); --- 1 row(s) inserted. >>insert into T values(1,2); --- 0 row(s) inserted. >>insert into T values(1, 4); *** ERROR[8102] The operation is prevented by a unique constraint. --- 0 row(s) inserted. >>select * from T; A B ----------- ----------1 2 --- 1 row(s) selected.
PAGE 92
An error is returned since the data attribute of column “a”, a char, does not match the data attribute of the select list item “b” a numeric. • If column-attributes are specified and they only contain column-name, then the specified column-name override any name that was derived from the select query. create table t(c,d) as select a,b from t1 Table t has 2 columns, c and d, which has the data attributes of columns a and b from table t1.
PAGE 93
CREATE TRIGGER Statement • • • “Syntax Description of CREATE TRIGGER” “Considerations for CREATE TRIGGER” “Examples of CREATE TRIGGER” The CREATE TRIGGER statement is used to create triggers on SQL tables. A trigger is a mechanism that sets up the database system to perform certain actions automatically in response to the occurrence of specified events. See “Triggers” (page 291).
PAGE 94
old-new-alias is the list of correlation name of table aliases used by a trigger. correlation-name is the name of the old or new row acted upon by the trigger. table-alias is the name of the old or new table acted upon by the trigger. search-condition is the condition that, when true, activates this trigger. triggered-SQL-statement is the SQL statement to be performed when this trigger is activated. searched-update-statement is an update statement to be performed when this trigger is activated.
PAGE 95
Trigger Types You can configure triggers as BEFORE or AFTER types. When a triggering statement occurs, this is the order of execution: 1. 2. 3. BEFORE triggered statements Triggering statement AFTER triggered statements Execution of a statement is considered to be complete only when all cascaded triggers are complete.
PAGE 96
triggers are used to condition input data, while AFTER-type triggers encode actual application logic. Restrictions on Triggers • The trigger feature does not allow the use of: — Positioned deletes and updates as triggered statements. — Subqueries in search-condition for AFTER triggers (but they are allowed in search-condition for BEFORE triggers.
PAGE 97
Examples of CREATE TRIGGER Before and After Triggers Suppose that you have a database to record patients’ vital signs and drugs prescribed for them.
PAGE 98
record.vital_id), (SELECT record.id FROM record WHERE sample.id = record.
PAGE 99
CREATE VIEW Statement • • • “Syntax Description of CREATE VIEW” “Considerations for CREATE VIEW” “Examples of CREATE VIEW” The CREATE VIEW statement creates a Neoview SQL view. See “Views” (page 292). CREATE VIEW view [(column-name ] [,column-name ...
PAGE 100
(column-name [,column-name ]...) specifies names for the columns of the view and, optionally, headings for the columns. Column names in the list must match one-for-one with columns in the table specified by query-expr. If you omit this clause, columns in the view have the same names as the corresponding columns in query-expr. You must specify this clause if any two columns in the table specified by query-expr have the same name or if any column of that table does not have a name.
PAGE 101
Authorization and Availability Requirements To create a view, you must have select privileges for the objects underlying the view and be the owner of the schema or have CREATE or CREATE_VIEW privilege on the schema. When you create a view on a single table, the owner of the view is automatically given all privileges WITH GRANT OPTION on the view. However, when you create a view that spans multiple tables, the owner of the view is given only SELECT privileges WITH GRANT OPTION.
PAGE 102
Because the isolation level closest to table t1 is READ UNCOMMITTED, READ UNCOMMITTED is used when the rows are read. Nested View Definitions The semantics used with nested view definitions are the same as those described in “Explicit User-Specified Isolation Level on SELECT From a View” (page 101). After nested view expansion, the isolation level closest to the table is used.
PAGE 103
The SELECT query becomes equivalent to: Select * from t order by a; ORDER BY in a View Definition With User Override If a SELECT query contains an explicit ORDER BY clause, it overrides the ORDER BY clause specified in the view definition. For example: Create view v as select a,b from t order by a; Select * from v order by b; In this example, order by b overrides the order by a specified in the view definition.
PAGE 104
Neoview SQL has the ability to eliminate redundant joins in a query. Redundant joins occur when • Output of join contains expressions from only one of its two children • Every row from this child will match one and only one row from the other child Suppose tables A and B denote generic tables.
PAGE 105
DELETE Statement • • • “Syntax Description of DELETE” “Considerations for DELETE” “Examples of DELETE” The DELETE statement is a DML statement that deletes a row or rows from a table or an updatable view. Deleting rows from a view deletes the rows from the table on which the view is based. DELETE does not remove a table or view, even if you delete the last row in the table or view.
PAGE 106
TRANSACTION statement to enable the NO ROLLBACK option for the transaction. See “SET TRANSACTION Statement” (page 187). An error message is generated if one of the following is true: • • WITH NO ROLLBACK used with STREAM clause WITH NO ROLLBACK used with SET ON ROLLBACK clause table names the user table or view from which to delete rows. table must be a base table or an updatable view. To refer to a table or view, use the ANSI logical name: See “Database Object Names” (page 228).
PAGE 107
SERIALIZABLE | REPEATABLE READ specifies that the DELETE statement and any concurrent process (accessing the same data) execute as if the statement and the other process had run serially rather than concurrently. SKIP CONFLICT enables transactions to skip rows locked in a conflicting mode by another transaction. The rows under consideration are the result of evaluating the search condition for the DELETE statement. You cannot use the SKIP CONFLICT in a SET TRANSACTION statement.
PAGE 108
DELETE WITH MULTI COMMIT FROM sales.orders WHERE salesrep = 220 AND custnum <> 1234; SET ON ROLLBACK Considerations The SET ON ROLLBACK expression is evaluated when each row is processed during execution of the DELETE statement. The results of the evaluation are applied when and if the transaction is rolled back.
PAGE 109
DROP INDEX Statement • • • “Syntax Description of DROP INDEX” “Considerations for DROP INDEX” “Example of DROP INDEX” The DROP INDEX statement drops a Neoview SQL index. See “Database Object Names” (page 228). DROP INDEX is a Neoview SQL extension. DROP [VOLATILE] INDEX index Syntax Description of DROP INDEX index is the index to drop. For information, see “Identifiers” (page 252).
PAGE 110
DROP MATERIALIZED VIEW Statement • • “Syntax Description of DROP MATERIALIZED VIEW” “Example of DROP MATERIALIZED VIEW” The DROP MATERIALIZED VIEW statement drops a materialized view. You cannot drop a materialized view that is used by other materialized views or by regular views unless the CASCADE option is used. This command removes the materialized view from all the MVGROUPs it belongs to. It could cause recompilation of SQL statements that used the materialized view, as well as any of its base tables.
PAGE 111
DROP MVGROUP Statement • • “Considerations for DROP MVGROUP” “Example of DROP MVGROUP” The DROP MVGROUP statement allows you to drop materialized view groups. For information on MVGROUPS, see “MVGROUPs ” (page 264). DROP MVGROUP mv-group-name Syntax Description of DROP MVGROUP mv-group-name specifies the materialized view group name to drop. Considerations for DROP MVGROUP • • Dropping an MVGROUP does not affect the MVs that were part of the group.
PAGE 112
DROP SCHEMA Statement • • • “Syntax Description of DROP SCHEMA” “Considerations for DROP SCHEMA” “Example of DROP SCHEMA” The DROP SCHEMA statement deletes a Neoview SQL schema. See“Schemas” (page 285). DROP SCHEMA schema [RESTRICT|CASCADE] Syntax Description of DROP SCHEMA schema is the name of the schema to delete. RESTRICT If you specify RESTRICT, an error is reported if the specified schema is not empty. The default is RESTRICT.
PAGE 113
DROP SYNONYM Statement • • • “Syntax Description of DROP SYNONYM ” “Considerations for DROP SYNONYM” “Example of DROP SYNONYM” The DROP SYNONYM statement allows you to drop synonyms for tables, views, or materialized views that can be used in queries. See “Synonyms” (page 289). DROP SYNONYM alternate-name Syntax Description of DROP SYNONYM alternate-name specifies the name of the synonym. See “Database Object Names” (page 228).
PAGE 114
DROP TABLE Statement • • • “Syntax Description of DROP TABLE” “Considerations for DROP TABLE” “Examples of DROP TABLE” The DROP TABLE statement deletes a Neoview SQL table and its dependent objects such as indexes and constraints. See “Database Object Names” (page 228). DROP [VOLATILE] TABLE table [RESTRICT|CASCADE] Syntax Description of DROP TABLE table is the name of the table to delete.
PAGE 115
DROP TRIGGER Statement • • • “Syntax Description of DROP TRIGGER” “Considerations for DROP TRIGGER” “Example of DROP TRIGGER” The DROP TRIGGER statement is used to drop a trigger on an SQL table. See “Triggers” (page 291). DROP TRIGGER trigger-name; Syntax Description of DROP TRIGGER trigger-name specifies the name of the trigger to be dropped. For naming convention, see “Database Objects” (page 227).
PAGE 116
DROP VIEW Statement • • • “Syntax Description of DROP VIEW” “Considerations for DROP VIEW” “Example of DROP VIEW” The DROP VIEW statement deletes a Neoview SQL view. See “Views” (page 292). DROP VIEW view [RESTRICT|CASCADE] Syntax Description of DROP VIEW view is the name of the view to delete. RESTRICT If you specify RESTRICT, you cannot drop the specified view if it is referenced in the query expression of any other view or in the search condition of another object's constraint.
PAGE 117
EXECUTE Statement • • • “Syntax Description of EXECUTE” “Considerations for EXECUTE” “Examples of EXECUTE” The EXECUTE statement executes an SQL statement previously compiled by a PREPARE statement. EXECUTE statement-name [ USING param [,param]...] ] param is: ?param-name | literal-value Syntax Description of EXECUTE statement-name is the name of a prepared SQL statement—that is, the statement name used in the PREPARE statement. statement-name is an SQL identifier. See “Identifiers” (page 252).
PAGE 118
Considerations for EXECUTE Scope of EXECUTE A statement must be compiled by PREPARE before you EXECUTE it, but after it is compiled, you can execute the statement multiple times without recompiling it. The statement must have been compiled during the same Neoview Command Interface session as its execution. Examples of EXECUTE • Use PREPARE to compile a statement once, and then execute the statement multiple times with different parameter values.
PAGE 119
--- 1 row(s) selected. SQL>execute findemp using 20000.00, 300; EMPNUM -----75 89 ... FIRST_NAME --------------TIM PETER LAST_NAME DEPTNUM JOBCODE SALARY -------------- ------- ------- -------WALKER 3000 300 32000.00 SMITH 3300 300 37000.40 --- 13 row(s) selected. • Use SET PARAM to assign a value to a parameter name and specify both the parameter name and a literal value in the EXECUTE USING clause: SQL>prepare findemp from +>select * from persnl.
PAGE 120
EXPLAIN Statement The EXPLAIN statement helps you to review query execution plans. You can use the EXPLAIN statement anywhere you can execute other Neoview database statements (for example, SELECT). For information about the Neoview Command Interface (NCI), see the Neoview Command Interface (NCI) Guide For more information on the EXPLAIN function, see “EXPLAIN Function” (page 380). NOTE: Formatting changes to the EXPLAIN output data might occur in the future.
PAGE 121
with a letter. When you refer to the prepared query in a SELECT statement, you must use uppercase.
PAGE 122
PREPARE q FROM SELECT * FROM REGION; EXPLAIN options 'f' q; The FILE_SCAN operator is the only operator in this example that shows additional information in the OPT and DESCRIPTION fields. 'fs' indicates that fast-scan optimization was used. 'fr' indicates that fast-reply data-move optimization was used. The table name is shown in the DESCRIPTION field. For information about these special optimization techniques, see the listing of each operator in the Neoview Query Guide.
PAGE 123
Table 2-5 Node Listing Information (continued) Field Description EST_TOTAL_COST Estimated cost associated with execution of the current operator and all children. DESCRIPTION Additional information about the operation. For example, in the case of a scan, the description field shows scan_type, scan_direction, lock_mode, access_mode, columns_retrieved, optimization information, and so on. For details about all operators and their description fields, see the Neoview Query Guide.
PAGE 124
buffer_size ....... 31,000 record_length ........ 184 space_usage ............ 8:32:100:144 bottom_partitioning_fun logphys partitioned(grouping, PAPA with 64 PA(s), log=exactly 1 partition, phys=hash2 partitioned 64 ways on (TPCH2X_CAT.TPCH2X_SCH.REGION.R_REGIONKEY)) bottom_node_map ........ (\SQA0101:0-15, \SQA0102:0-15, \SQA0103:0-15, \SQA0104:0-15) begin_part_no_expr ..... \:_sys_hostVarPAPartNo_1539575856 end_part_no_expr .......
PAGE 125
------------------------------------------------------------------ NODE LISTING ROOT ====================================== SEQ_NO 4 ONLY CHILD 3 REQUESTS_IN .............. 1 ROWS_OUT ................. 5 EST_OPER_COST ............ 0.0001 EST_TOTAL_COST ........... 0.0092 cpu_cost ............... 0.0003 io_cost ................ 0.0089 msg_cost ............... 0 idle_cost .............. 1.2005 DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type ..........
PAGE 126
FILE_SCAN ================================= SEQ_NO 1 TABLE_NAME ............... TPCH2X_CAT.TPCH2X_SCH.REGION REQUESTS_IN .............. 1 ROWS_OUT ................. 5 EST_OPER_COST ............ 0.0092 EST_TOTAL_COST ........... 0.0092 cpu_cost ............... 0.0003 io_cost ................ 0.0089 msg_cost ............... 0 idle_cost .............. 1.1025 DESCRIPTION fragment_id ............ parent_frag ............ fragment_type .......... scan_type .............. scan_direction ......... lock_mode .......
PAGE 127
Column Name Data Type Description TNAME CHAR(60) For operators in scan group, full name of base table, truncated on the right if too long for column. If correlation name differs from table name, simple correlation name first and then table name in parentheses. CARDINALITY REAL Estimated number of rows that are returned by the current operator. Cardinality appears as ROWS/REQUEST in some forms of EXPLAIN output.
PAGE 128
Example of EXPLAIN Statement Using ODBC Suppose an application prepares a SELECT statement: “SELECT * FROM ODBC_PERSNL.EMPLOYEE”. Use SQLGetCursorName to find the statement name. In this example, the returned statement name is "SQL_CUR_21". To get the plan for “SELECT * FROM ODBC_PERSNL.EMPLOYEE”, the application must allocate another statement handle and issue SQLExecDirect on “EXPLAIN options ‘f’ SQL_CUR_21”. The plan is returned as a result-set. Use SQLFetch to retrieve the result.
PAGE 129
Get Data All: "EXPLAIN OUTPUT(FORMATTED)" " " "LC RC OP OPERATOR "--- --- --- -------------------" " "3 . 4 root "2 . 3 split_top "1 . 2 partition_access ". . 1 file_scan 8 rows fetched from 1 column. OPT -------- DESCRIPTION -------------------- 1.76E+003" 1.76E+003" 1.76E+003" 1.76E+003" 1:64(hash2) fs fr CARD " ---------" EMPLOYEE Example of EXPLAIN Statement Using JDBC import common.*; import java.sql.
PAGE 130
catch (SQLException e) { SQLException nextException; nextException = e; do { System.out.println(nextException.getMessage()); System.out.println("SQLState " + nextException.getSQLState()); System.out.println("Error Code " + nextException.getErrorCode()); } while ((nextException = nextException.
PAGE 131
GET SERVICE Statement The GET SERVICE statement returns the WMS service that is in effect for queries in the current session and provides some information about the service and the client session. Neoview Workload Management Services (WMS) manages queries associated with a service according to the priority, threshold limits, and rules of the service. For information about WMS, see the Neoview Workload Management Services Guide.
PAGE 132
By default, the query plan and SQL text are turned off (represented by 0 in the output) for HP_DEFAULT_SERVICE, and there is no connection rule in effect. • This GET SERVICE statement shows that SALESQUERIES is the current service for the NCI session: SQL>get service; CUR_SERVICE PLAN TEXT CUR_SCHEMA RULE_NAME APPL_NAME SESSION_NAME DSN_NAME ROLE_NAME ------------ ---- ---- ---------- --------- --------- ------------ ---------------------- --------SALESQUERIES 1 1 NEO.
PAGE 133
GRANT Statement • • • “Syntax Description of GRANT” “Considerations for GRANT” “Examples of GRANT” The GRANT statement grants access privileges for a Neoview SQL table, view, or materialized view to specified roles or through a synonym of a table, view, or materialized view. GRANT { privilege [,privilege]... | ALL [PRIVILEGES] } ON [TABLE] object TO {grantee [,grantee ]... } [WITH GRANT OPTION] grantee is: authid privilege is: DELETE | INSERT | SELECT | UPDATE [(column [,column]...
PAGE 134
You cannot specify SYSTEM as an authid in a GRANT statement. WITH GRANT OPTION specifies that roles to whom privileges are granted have the right to grant the same privilege to other roles. Considerations for GRANT Authorization and Availability Requirements To grant a privilege on an object, you must have both that privilege and the right to grant that privilege.
PAGE 135
GRANT EXECUTE Statement • • • “Syntax Description of GRANT EXECUTE” “Considerations for GRANT EXECUTE” “Examples of GRANT EXECUTE” The GRANT EXECUTE statement grants privileges for executing a stored procedure in Java (SPJ) to one or more specified roles. GRANT EXECUTE ON [PROCEDURE] procedure-ref TO {grantee [,grantee ]... } [WITH GRANT OPTION] procedure-ref is: [schema-name.
PAGE 136
Examples of GRANT EXECUTE • The SPJ owner (or creator) grants the EXECUTE and WITH GRANT OPTION privileges on the ADJUSTSALARY procedure to the role ROLE.HR: GRANT EXECUTE ON PROCEDURE persnl.adjustsalary TO "ROLE.HR" WITH GRANT OPTION; • The role ROLE.HR grants the EXECUTE privilege on the ADJUSTSALARY procedure to other roles: GRANT EXECUTE ON PROCEDURE persnl.adjustsalary TO "ROLE.PAYROLL", "ROLE.
PAGE 137
GRANT SCHEMA Statement • • • “Syntax Description of GRANT SCHEMA” “Considerations for GRANT SCHEMA” “Examples of GRANT SCHEMA” The GRANT SCHEMA statement grants access privileges for a schema to specified roles. GRANT {privilege [,privilege]... | all [PRIVILEGES] } ON SCHEMA schema-name TO {PUBLIC | grantee [,grantee ]...
PAGE 138
ALTER_MV Can rename, change attributes, and create, populate, and drop indexes for any materialized view in the schema. ALTER_MV_GROUP Can change the materialized views that are part of the group to be refreshed together. ALTER_SYNONYM Can alter synonyms in the schema. However, ability to alter a synonym does not give the grantee privileges on the referenced object. In order to perform operations using the synonym, privileges must exist for the grantee on the referenced object.
PAGE 139
TO {grantee [,grantee]... } specifies one or more roles to whom you grant privileges. grantee specifies an authorization ID to whom you grant privileges. Authorization IDs identify roles during the processing of SQL statements. The authorization ID must be a valid role name, enclosed in double quotes. grantee is not case-sensitive. SQL:1999 specifies two special authorization IDs: PUBLIC and SYSTEM. • • PUBLIC specifies all present and future authorization IDs.
PAGE 140
Examples of GRANT SCHEMA • This example grants SELECT and DELETE privileges on the sales schema to the two specified users: GRANT SELECT, DELETE ON SCHEMA sales TO "role.role1", "role.
PAGE 141
INSERT Statement • • • “Syntax Description of INSERT” “Considerations for INSERT” “Examples of INSERT” The INSERT statement is a DML statement that inserts rows in a table or view. INSERT [NOMVLOG] [WITH NO ROLLBACK] INTO table [(target-col-list)] insert-source target-col-list is: colname [,colname]...
PAGE 142
position is determined by the columns in the table derived from the evaluation of the query expression (query-expr). If you do not specify all of the columns in table in the target-col-list, column default values are inserted into the columns that do not appear in the list. See “Column Default Settings” (page 223). If you do not specify target-col-list, row values from the source table are inserted into all columns in table.
PAGE 143
colnum specifies a column by its position in the select list of the query expression. Use colnum to refer to unnamed columns, such as columns in the derived table of a query expression other than a table or view. ASC | DESC specifies the sort order. The default is ASC. For ordering the source table on a column that can contain null, nulls are considered equal to one another but greater than nonnulls. DEFAULT VALUES specifies a query expression of the form VALUES (DEFAULT, ... ).
PAGE 144
or AUTOCOMMIT OFF is used unless the compiler's plan sorts the rows before they are inserted. To prevent this problem, Neoview generates error 8107. If you want to use a self-referencing INSERT statement, you should avoid the use of BEGIN WORK or AUTOCOMMIT OFF. Isolation Levels of Transactions and Access Options of Statements The isolation level of a Neoview SQL transaction defines the degree to which the operations on data within that transaction are affected by operations of concurrent transactions.
PAGE 145
Date, time, and timestamp are the three Neoview SQL datetime data types. A value with a datetime data type is compatible with another value with a datetime data type only if the values have the same datetime fields. Inserting Nulls In addition to inserting values with specific data types, you might want to insert nulls. To insert null, use the keyword NULL. Examples of INSERT • Insert a row into the CUSTOMER table and supply the value 'A2' for the CREDIT column: INSERT INTO sales.
PAGE 146
(600,'ADMINISTRATOR'), (900,'SECRETARY'); --- 10 row(s) inserted. • The PROJECT table consists of five columns using the data types numeric, varchar, date, timestamp, and interval. Insert values by using these types: INSERT INTO persnl.project VALUES (1000, 'SALT LAKE CITY', DATE '2007-10-02', TIMESTAMP '2007-12-21:08:15:00.00', INTERVAL '30' DAY); --- 1 row(s) inserted. • Suppose that CUSTLIST is a view of all columns of the CUSTOMER table except the credit rating.
PAGE 147
LOCK TABLE Statement • • • “Syntax Description of LOCK TABLE” “Considerations for LOCK TABLE” “Examples of LOCK TABLE” The LOCK TABLE statement locks a table (or the underlying tables of a view) and its indexes, limiting other access to the table and its indexes while your process executes DML statements. See “Database Integrity and Locking” (page 29). LOCK TABLE is a Neoview SQL extension.
PAGE 148
AUTOCOMMIT by using the SET TRANSACTION statement. See “SET TRANSACTION Statement” (page 187). Indexes LOCK TABLE attempts to lock all indexes of any table it locks. If an index is not available or if the lock request times out, LOCK TABLE displays a warning and continues to request locks on other indexes. Examples of LOCK TABLE • Lock a table with an exclusive lock (at a time when few users need access to the database) to perform a series of updates: BEGIN WORK; LOCK TABLE persnl.
PAGE 149
MERGE INTO Statement • • “Syntax Description of MERGE INTO” “Considerations for MERGE INTO” The MERGE INTO statement:. • Updates a table if the row exists and inserts if it does not. This is upsert functionality. • Performs reflexive updates where columns are updated incrementally. • Updates (merges) matching rows from one table to another.
PAGE 150
MERGE INTO t ON a = 10 WHEN MATCHED THEN UPDATE SET b = 20 This example inserts values if a is not found. If a is found, nothing is done. MERGE INTO t ON a = 10 WHEN NOT MATCHED THEN INSERT VALUES (10, 30) Restrictions • • • A merged table cannot be a view. Merge is not allowed with SET ON ROLLBACK. The key value specified in the on-clause and the VALUE clause must be the same.
PAGE 151
Example This query extracts derived columns 'a' and 'b' from the USING query as derived table 'z' and use each row to join to the merged table 't' based on the ON clause. For each matched row, column 'b' in table 't' is updated using column 'b' in derived table 'z'. For rows that are not matched, values z.a and z.b are inserted. MERGE INTO t USING a = z.a ON (SELECT * FROM t1) z(a,b) WHEN MATCHED THEN UPDATE SET b = z.b WHEN NOT MATCHED THEN INSERT VALUES (z.a, z.
PAGE 152
PREPARE Statement • • • “Syntax Description of PREPARE” “Considerations for PREPARE” “Examples of PREPARE” The PREPARE statement compiles an SQL statement for later use with the EXECUTE statement in a Neoview Command Interface (NCI) session. You can also use PREPARE to check the syntax of a statement without executing the statement in a Neoview Command Interface (NCI) session.
PAGE 153
SALARY ---------32000.00 33000.50 40000.00 32000.00 45000.00 --- 5 row(s) selected. SQL> • Prepare a SELECT statement with a named parameter (?param-name) and later run EXECUTE on it: SQL>prepare findsal from +>select salary from persnl.employee +>where jobcode = ?job; --- SQL command prepared. SQL>set param ?job 450 SQL>execute findsal; SALARY ---------32000.00 33000.50 40000.00 32000.00 45000.00 --- 5 row(s) selected. SQL> For more information, see the “EXECUTE Statement” (page 117).
PAGE 154
REVOKE Statement • • • “Syntax Description of REVOKE” “Considerations for REVOKE” “Examples of REVOKE” The REVOKE statement revokes access privileges for a Neoview SQL table, view, or materialized view from specified roles or through a synonym of a table, view, or materialized view. REVOKE [GRANT OPTION FOR] {privilege [,privilege ]...| ALL [PRIVILEGES]} ON [TABLE] object FROM {grantee [,grantee ]...
PAGE 155
grantee is authid authid specifies an authorization ID to whom you revoke privileges. Authorization IDs identify roles during the processing of SQL statements. The authorization ID must be a valid role name, enclosed in double quotes. authid is not case-sensitive. SQL:1999 specifies two special authorization IDs: PUBLIC and SYSTEM. • • PUBLIC specifies all present and future authorization IDs. SYSTEM specifies the implicit grantor of privileges to the creators of objects.
PAGE 156
REVOKE EXECUTE Statement • • • “Syntax Description of REVOKE EXECUTE” “Considerations for REVOKE EXECUTE” “Examples of REVOKE EXECUTE” The REVOKE EXECUTE statement removes privileges for executing a stored procedure in Java (SPJ) from one or more specified roles. REVOKE [GRANT OPTION FOR] EXECUTE ON [PROCEDURE] procedure-ref FROM {grantee [,grantee ]... } [RESTRICT | CASCADE] procedure-ref is: [schema-name.
PAGE 157
Considerations for REVOKE EXECUTE Authorization and Availability Requirements You can revoke the EXECUTE privilege from a role only if you have previously granted it to the role. If the privilege does not exist, the system returns a warning. To revoke privileges by using the CASCADE option, you must be the SPJ owner (that is, the schema owner or the creator of the stored procedure). You cannot revoke the EXECUTE privilege from a specific role if the EXECUTE privilege is granted to PUBLIC.
PAGE 158
REVOKE SCHEMA Statement • • • “Syntax Description of REVOKE SCHEMA” “Considerations for REVOKE SCHEMA” “Examples of REVOKE SCHEMA” The REVOKE SCHEMA statement revokes access privileges for a Neoview SQL schema from specified roles. See also“ROLLBACK WORK Statement” (page 161). REVOKE [GRANT OPTION FOR] {privilege [,privilege ] ...| all [PRIVILEGES ]} ON SCHEMA schema-name FROM {PUBLIC | grantee [,grantee]...
PAGE 159
privilege [,privilege]... | all [PRIVILEGES] specifies the privileges to revoke. You can specify each of these privileges for a schema. ALL_DDL Any table, view, materialized view, or procedure created in the schema after this privilege is granted will be able to perform any DDL privilege including CREATE, DROP, ALTER, and the REFRESH option of MAINTAIN.
PAGE 160
FROM {authid [,authid]... | PUBLIC} specifies one or more roles from whom you revoke privileges. grantee is authid | PUBLIC authid specifies an authorization ID from which you revoke privileges. Authorization IDs identify roles during the processing of SQL statements. The authorization ID must be a valid role name, enclosed in double quotes. authid is not case-sensitive. SQL:1999 specifies two special authorization IDs: PUBLIC and SYSTEM. • • PUBLIC specifies all present and future authorization IDs.
PAGE 161
ROLLBACK WORK Statement • • “Considerations for ROLLBACK WORK” “Examples of ROLLBACK WORK” The ROLLBACK WORK statement undoes all database modifications to objects made during the current transaction, releases all locks on objects held by the transaction, and ends the transaction. See “Transaction Management” (page 30). Syntax Description of ROLLBACK WORK ROLLBACK [WORK] WORK is an optional keyword that has no effect. ROLLBACK WORK issued outside of an active transaction generates error 8609.
PAGE 162
SELECT Statement • • • • • • • • 162 “Syntax Description of SELECT” “Considerations for SELECT” “Considerations for Select List” “Considerations for SEQUENCE BY” “Considerations for GROUP BY” “Considerations for ORDER BY” “Considerations for UNION” “Examples of SELECT” SQL Statements
PAGE 163
The SELECT statement is a DML statement that retrieves values from tables, views, derived tables determined by the evaluation of query expressions, or joined tables. SELECT [col-expr] [[ANY N] | [FIRST N]] [ALL | DISTINCT] select-list FROM table-ref [,table-ref]... [WHERE search-condition] [SAMPLE sampling-method] [TRANSPOSE transpose-set [transpose-set]... [KEY BY key-colname]]... [SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] | DESC[ENDING]]]...
PAGE 164
select-list is: * | select-sublist [,select-sublist]... select-sublist is: corr.*) | [corr.
PAGE 165
THEN percent-result PERCENT [ROWS] [WHEN condition THEN percent-result PERCENT [ROWS]]... [ELSE percent-result PERCENT [ROWS]] END rows-size is: number-rows ROWS | BALANCE WHEN condition THEN number-rows ROWS [WHEN condition THEN number-rows ROWS]... [ELSE number-rows ROWS] END transpose-set is: transpose-item-list AS transpose-col-list transpose-item-list is: expression-list | (expression-list) [,(expression-list)]... expression-list is: expression [,expression]...
PAGE 166
col-expr [[AS]name] specifies a derived column determined by the evaluation of an SQL value expression in the list. By using the AS clause, you can associate a derived column with a name. See the discussion of limitations in “Considerations for Select List” (page 174). FROM table-ref [,table-ref]... specifies a list of tables, views, derived tables, or joined tables that determine the contents of an intermediate result table from which Neoview SQL returns the columns you specify in select-list.
PAGE 167
RETURN select-list specifies the columns or column expressions returned from the deleted row. The items in the select-list can be of these forms: [OLD.]* specifies the row from the old table exposed by the embedded delete. The old table refers to column values before the delete operation. NEW is not allowed. An implicit OLD.* return list is assumed for a delete operation that does not specify a return list.
PAGE 168
(insert-statement [AS] corr [(col-expr-list)] For the syntax of insert-statement, see the “INSERT Statement” (page 141). [AS] corr [(col-expr-list)] specifies an optional correlation name and an optional column list. table-ref [NATURAL] [join-type] JOIN table-ref [join-spec] join-type is: CROSS |INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] is a joined table. You specify the join-type by using the CROSS, INNER, OUTER, LEFT, RIGHT, and FULL keywords.
PAGE 169
| TABLE table | SELECT [ALL | DISTINCT] select-list FROM table-ref [,table-ref]... [WHERE search-condition | rowset-search-condition] [SAMPLE sampling-method] [TRANSPOSE transpose-set [transpose-set]... [KEY BY key-colname]]... [SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] | DESC[ENDING]]]...] [GROUP BY [col-expr]{colname | colnum} [,{colname | colnum}]...
PAGE 170
PERIODIC rows-size EVERY number-rows ROWS [SORT BY colname [,colname]...] directs Neoview SQL to choose the first rows from each block (period) of contiguous sorted rows. The sampling size is determined by using the specified number of rows chosen from each block. SAMPLE is a Neoview SQL extension. TRANSPOSE transpose-set[transpose-set]... [KEY BY key-colname] specifies the transpose-sets and an optional key clause within a TRANSPOSE clause. You can use multiple TRANSPOSE clauses in a SELECT statement.
PAGE 171
result table contains one group for each age and, within each age group, subgroups for each job code. You can specify GROUP BY using ordinals to refer to the relative position within the SELECT list. For example, GROUP BY 3, 2, 1. For grouping purposes, all nulls are considered equal to one another. The result table of a GROUP BY clause can have only one null group. See “Considerations for GROUP BY” (page 174).
PAGE 172
The select lists in the two SELECT statements of a union operation must have the same number of columns, and columns in corresponding positions within the lists must have compatible data types. The select lists must not be preceded by [ANY N] or [FIRST N]. The number of columns in the result table of the union operation is the same as the number of columns in each select list.
PAGE 173
Locking modes are relevant only to select operations that use a cursor. In a standalone SELECT statement, locks are maintained only for the duration of the select. Use of Views With SELECT When a view is referenced in a SELECT statement, the specification that defines the view is combined with the statement. The combination can cause the SELECT statement to be invalid. If you receive an error message that indicates a problem but the SELECT statement seems to be valid, check the view definition.
PAGE 174
• • A union between embedded INSERT expressions is not supported. Declaring a cursor on an embedded INSERT statement is not supported. DISTINCT Aggregate Functions An aggregate function can accept an argument specified as DISTINCT, which eliminates duplicate values before the aggregate function is applied. For a given grouping, multiple DISTINCT aggregates are allowed and can be used with non distinct aggregates.
PAGE 175
• • • • AGE is not a grouping column, you can refer to AGE only as the argument of a function, such as AVG (AGE). The expression in the GROUP BY clause must be exactly the same as the expression in the select list. An error will be returned if it is not. It cannot contain aggregate functions or subqueries. If the value of col-expr is a numeric constant, it refers to the position of the select list item and is treated as the current GROUP BY using the ordinal feature.
PAGE 176
(The year-month fields are YEAR and MONTH. The day-time fields are DAY, HOUR, MINUTE, and SECOND.) For example, suppose that the column in TABLE1 has the data type INTERVAL HOUR TO MINUTE, and the column in TABLE2 has the data type INTERVAL DAY TO HOUR. The data type of the column resulting from the union operation is INTERVAL DAY TO MINUTE. • If both columns are described with NOT NULL, the corresponding column of RESULT cannot be null. Otherwise, the column can be null.
PAGE 177
SELECT common.isma_no FROM sdcommon common WHERE common.sec_status='L' UNION SELECT main.isma_no FROM sdmain main WHERE main.iss_eligible='Y' FOR READ UNCOMMITTED ACCESS ORDER BY 1 ASCENDING; This statement will receive a warning: *** WARNING[3192] Union operands sdcommon common and sdmain main have different transaction access/lock modes.
PAGE 178
600 600 900 900 . . . 1500 1500 2500 1000 JONATHAN JIMMY MIRIAM SUE MITCHELL SCHNEIDER KING CRAMER 32000.00 26000.00 18000.00 19000.00 In this example, because of READ UNCOMMITTED access, the query does not wait for other concurrent processes to commit rows. • Display selected rows grouped by job code in ascending order: SELECT jobcode, AVG(salary) FROM persnl.employee WHERE jobcode > 500 AND deptnum <= 3000 GROUP BY jobcode ORDER BY jobcode; JOBCODE ------600 900 EXPR ---------------------29000.
PAGE 179
2. Drop rows with unequal job codes. EMPLOYEE Table 3. JOB Table EMPNUM ... JOBCODE ... SALARY JOBCODE JOBDESC 1 100 175500 100 MANAGER ... ... ... ... ... 75 300 32000 300 SALESREP ... ... ... ... ... 178 900 28000 900 SECRETARY ... ... ... ... ... 207 420 33000 420 ENGINEER ... ... ... ... ... 568 300 39500 300 SALESREP Drop rows with job codes not equal to 900, 300, or 420. EMPLOYEE Table 4. JOB Table EMPNUM ... JOBCODE ...
PAGE 180
SECRETARY ... • JOHN CHOU 28000.00 Select from three tables, group the rows by job code and (within job code) by department number, and order the groups by the maximum salary of each group: SELECT E.jobcode, E.deptnum, MIN (salary), MAX (salary) FROM persnl.employee E, persnl.dept D, persnl.job J WHERE E.deptnum = D.deptnum AND E.jobcode = J.jobcode AND E.jobcode IN (900, 300, 420) GROUP BY E.jobcode, E.deptnum ORDER BY 4; JOBCODE ------900 900 ... 300 900 ... 300 420 ...
PAGE 181
JOBCODE ------100 200 250 300 400 420 450 500 600 900 100 300 400 500 600 900 JOBDESC -----------------MANAGER PRODUCTION SUPV ASSEMBLER SALESREP SYSTEM ANALYST ENGINEER PROGRAMMER ACCOUNTANT ADMINISTRATOR SECRETARY CORP MANAGER CORP SALESREP CORP SYSTEM ANALYS CORP ACCOUNTANT CORP ADMINISTRATOR CORP SECRETARY --- 16 row(s) selected. • A FULL OUTER JOIN combines the results of both left and right outer joins.
PAGE 182
(SELECT O.ordernum FROM sales.orders O WHERE custnum IN (SELECT custnum FROM sales.customer WHERE state = 'CALIFORNIA')) GROUP BY OD.ordernum; ORDERNUM (EXPR) ---------- --------------------200490 1030.00 300350 71025.00 300380 28560.00 --- 3 row(s) selected. The price for the total quantity ordered is computed for each order number. • Show employees, their salaries, and the percentage of the total payroll that their salaries represent.
PAGE 183
from ( insert into identity_table values (DEFAULT,100,100), (DEFAULT,200,200) )X; A --------------216944652091640 216944652091641 • B C ------ -----100 200 100 200 INSERT...SELECT statements are supported with embedded INSERT statements: insert into another_table values (300,300,300), (400,400,400), (500,500,500); select P.a, P.b, P.c from ( insert into identity_table select * from another_table where y < 500 ) as P where P.
PAGE 184
SET SCHEMA Statement • • • “Syntax Description of SET SCHEMA” “Consideration for SET SCHEMA” “Examples of SET SCHEMA” The SET SCHEMA statement sets the default logical schema for unqualified object names for the current SQL session. SET SCHEMA default-schema-name Syntax Description of SET SCHEMA default-schema-name specifies the name of the schema. See “Schemas” (page 285). default-schema-name is an SQL identifier. For example, you can use MYSCHEMA or myschema or a delimited identifier "my schema".
PAGE 185
SET TABLE TIMEOUT Statement • • • “Syntax Description of SET TABLE TIMEOUT” “Considerations for SET TABLE TIMEOUT” “Examples of SET TABLE TIMEOUT” The SET TABLE TIMEOUT statement sets a dynamic timeout value for a lock timeout or a stream timeout in the environment of the current session. The dynamic timeout value overrides the compiled static timeout value in the execution of subsequent DML statements. SET TABLE TIMEOUT is a Neoview SQL extension.
PAGE 186
NOTE: Because of overhead processing by Neoview SQL after a timeout occurs on a locked table, the actual time is usually a few seconds longer than value. RESET removes the dynamic timeout value (if set) for the specified table, resetting the timeout value to the static values set during explicit Neoview SQL compilations. The RESET option with an asterisk resets the dynamic timeout value (lock or stream timeout, as specified) for all tables.
PAGE 187
SET TRANSACTION Statement • • • “Syntax Description of SET TRANSACTION” “Considerations for SET TRANSACTION” “Example of SET TRANSACTION” The SET TRANSACTION statement sets attributes for transactions. It stays in effect until the end of the session or until the next SET TRANSACTION statement, whichever comes first. Therefore, the SET TRANSACTION statement can set the attributes of all subsequent transactions in the session.
PAGE 188
Syntax Description of SET TRANSACTION transaction mode specifies the attributes that you can set. You cannot specify any of the options—isolation level, access mode, size of the diagnostics area, or autocommit—more than once within one SET TRANSACTION statement. You cannot use the AUTOCOMMIT option with any other option. isolation-level specifies the level of data consistency defined for the transaction and the degree of concurrency the transaction has with other transactions that use the same data.
PAGE 189
RESET Reverts to the segment-wide autoabort interval configured. autobegin-option specifies whether transactions should occur automatically. The default setting is ON. If this option is set to OFF and Neoview SQL needs a transaction and one is not already running, an error is returned. The AUTOBEGIN option in a SET TRANSACTION can only be specified on one SET TRANSACTION command. This means that you can only issue the SET TRANSACTION AUTOBEGIN OFF statement by itself.
PAGE 190
SET TRANSACTION MULTI COMMIT ON; DELETE FROM persnl.employee WHERE empnum = 23; --- 1 row(s) deleted. SET TRANSACTION MULTI COMMIT OFF; These restrictions apply to the MULTI COMMIT option: • Can be specified in the SET TRANSACTION statement along with other transaction-mode. For example, SET TRANSACTION ISOLATION LEVEL READ COMMITTED, MULTI COMMIT ON, READ WRITE; • • • • Not compatible with the READ UNCOMMITTED access option. Not compatible with READ ONLY access mode.
PAGE 191
Degree of Concurrency The SET TRANSACTION statement affects the degree of concurrency available to the transaction. Concurrent processes take place within the same interval of time and share resources.
PAGE 192
SHOWDDL Statement • • • • • “Syntax Description of SHOWDDL” (page 192) “Considerations for SHOWDDL” (page 192) “Differences Between SHOWDDL Output and Original DDL” (page 192) “PRIVILEGES Option” (page 193) “Examples of SHOWDDL” (page 193) The SHOWDDL statement describes details about objects in its display. SHOWDDL [TABLE] object-name} [, PRIVILEGES] object-name is: [schema-name.]object-name Syntax Description of SHOWDDL object-name specifies the ANSI name of a table, materialized view, or view.
PAGE 193
PRIVILEGES Option The PRIVILEGES option includes the GRANT statements as they apply to the option. Each privilege is specified in a separate GRANT even if they were granted in a single statement. For example: GRANT ALL PRIVILEGES ON TABLE t1 TO “role_user1”, “role_user2”: will be displayed as: GRANT SELECT ON TABLE sch.t1 TO "role_user1"; GRANT UPDATE ON TABLE sch.t1 TO "role_user1"; GRANT DELETE ON TABLE sch.t1 TO "role_user1"; GRANT INSERT ON TABLE sch.t1 TO "role_user1"; GRANT REFERENCES ON TABLE sch.
PAGE 194
CREATE VIEW SCH.T1_VIEW1 AS SELECT SCH.T1.C1, SCH.T1.C2 FROM SCH.T1 WHERE SCH.
PAGE 195
-- showddl on materialized view SHOWDDL EMPL_MV, PRIVILEGES -- showddl output CREATE MATERIALIZED VIEW SCH.EMPL_MV AS SELECT max(SCH.EMPL.SALARY) AS SALARY, SCH.EMPL.DEPT_NO AS DEPT_NO FROM SCH.EMPL GROUP BY SCH.EMPL.DEPT_NO; -- The system added the following columns to the select list: -- count(*) AS SYS_COUNTSTAR1 -- count(SCH.EMPL.SALARY) AS SYS_COUNT2 -- showddl on dept table SHOWDDL DEPT, PRIVILEGES -- showddl output -- Schema level privileges -- GRANT SELECT ON SCHEMA SCH TO “ROLE.
PAGE 196
UPDATE Statement • • • “Syntax Description of UPDATE” “Considerations for UPDATE” “Examples of UPDATE” The UPDATE statement is a DML statement that updates data in a row or rows in a table or updatable view. Updating rows in a view updates the rows in the table on which the view is based. Searched UPDATE is: UPDATE [NOMVLOG] [WITH NO ROLLBACK] table | STREAM (table) [AFTER LAST ROW] SET (column1, ..., columnN) = {(value1, ..., valueN) | (select-subquery)} set-clause [,set-clause ]...
PAGE 197
STREAM (table) updates a continuous data stream from the specified table. You cannot specify STREAM access for the UPDATE statement if it is not embedded as a table reference in a SELECT statement. See “SELECT Statement” (page 162). [AFTER LAST ROW] causes the stream to skip all existing rows in the table and update only rows that are published after the stream’s cursor is opened. set-clause associates a value with a specific column in the table being updated.
PAGE 198
WHERE search-condition specifies a search-condition that selects rows to update. Within the search-condition, columns being compared are also being updated in the table or view. See “Search Condition” (page 286). If you do not specify a search-condition, all rows in the table or view are updated. Do not use an UPDATE statement with a WHERE clause that contains a SELECT for the same table. Reading from and inserting into, updating in, or deleting from the same table generates an error.
PAGE 199
specify access options for the DML statements within a transaction, you override the isolation level of the containing transaction. Each statement then executes with its individual access option. You can explicitly set the isolation level of a transaction with the SET TRANSACTION statement. See “SET TRANSACTION Statement” (page 187).
PAGE 200
SET Clause Restrictions and Error Cases The SET clause has the following restrictions: • The number of columns on the left side of each assignment operator should match the number of values or SELECT list elements on the right side. The following examples are not allowed: UPDATE t SET (a,b)=(10,20,30) UPDATE t set (b,c)=(SELECT r,t,s FROM x) • If multi-column update syntax is specified and the right side contains a subquery, only one element, the subquery, is not allowed.
PAGE 201
(SELECT deptnum FROM persnl.dept WHERE location = 'CHICAGO'); The subquery is evaluated for each row of the DEPT table and returns department numbers for departments located in Chicago. • Suppose that you want to change the employee number of a manager of a department. Because EMPNUM is a primary key of the EMPLOYEE table, you must delete the employee's record and insert a record with the new number. You must also update the DEPT table to change the MANAGER column to the employee's new number.
PAGE 202
UPDATE STATISTICS Statement • • • “Syntax Description of UPDATE STATISTICS” “Considerations for UPDATE STATISTICS” “Examples of UPDATE STATISTICS” The UPDATE STATISTICS statement updates the histogram statistics for one or more groups of columns within a table. These statistics are used to devise optimized access plans. In addition to histogram statistics, UPDATE STATISTICS generates physical statistics (index level, nonempty block count, and EOF) for Neoview SQL tables.
PAGE 203
stored in histogram tables. If you omit it, physical statistics are generated for Neoview SQL tables, and SQL returns a warning message. See “Using Statistics” (page 205). column-list | EVERY COLUMN [, column-list] | EVERY KEY [, column-list] | EXISTING COLUMN[S] [, column-list], NECESSARY COLUMN[S] [, column-list] specifies the ways in which column-group-list can be defined. The column list represents both a single-column group and a multi-column group.
PAGE 204
NECESSARY COLUMN[S] The NECESSARY COLUMN[S] keyword causes UPDATE STATISTICS to determine what existing histograms, if any, need to be updated, then updates them. If no histograms are found to need regeneration, UPDATE STATISTICS issues a warning. The NECESSARY COLUMN[S] keyword ignores the SAMPLE clause and internally determines the sample percent to use. histogram-option GENERATE n INTERVALS The GENERATE n INTERVALS option for UPDATE STATISTICS now accepts values between 1 and 10,000.
PAGE 205
defined by the number of rows specified for period. The value period must be an integer that is greater than zero (period > 0). SET ROWCOUNT c specifies the number of rows in the table. The value c must be an integer that is greater than or equal to zero (c >= 0). If the ROWCOUNT clause is not specified, Neoview SQL will determine the actual table row count. See “SAMPLE Clause” (page 298).
PAGE 206
ON CITY, STATE TO ZIP ON (CITY), (STATE) TO (ZIP) ON CITY TO STATE, ZIP ON (CITY) TO (STATE), (ZIP) The TO specification is useful when a table has many columns, and you want histograms on a subset of columns. Do not confuse (CITY) TO (ZIP) with (CITY, STATE, ZIP), which refers to a multi-column histogram. You can clear statistics in any combination of columns you specify, not necessarily with the column-group-list you used to create statistics.
PAGE 207
UPDATE STATISTICS FOR TABLE demolition_sites ON (zip, type); • This example removes all histograms for table DEMOLITION_SITES: UPDATE STATISTICS FOR TABLE demolition_sites CLEAR; • This example selectively removes histograms for column STREET in table ADDRESS: UPDATE STATISTICS FOR TABLE address ON street CLEAR; UPDATE STATISTICS Statement 207
PAGE 208
PAGE 209
3 SQL Utilities A utility is a tool that runs within Neoview SQL and performs such tasks as maintenance, loading indexes, purging data from tables, indexes, materialized views and reorganizing and reloading data in a table or index. This section describes the Neoview SQL utilities: “MAINTAIN Command” (page 210) Performs one or more maintenance tasks, such as REORG, UPDATE STATISTICS, and REFRESH, on a specified database object. “POPULATE INDEX Utility” (page 218) Loads indexes.
PAGE 210
MAINTAIN Command • • “Syntax Description of MAINTAIN” “Examples of MAINTAIN” The MAINTAIN command is a syntax-based utility that can be executed in the Neoview Command Interface or in the SQL Whiteboard of the HP Database Manager (HPDM). The MAINTAIN command performs one or more table maintenance tasks, such as REORG, UPDATE STATISTICS, and REFRESH, on a specified database object. In this release, the MAINTAIN command provides support to specify multiple tables in one MAINTAIN command.
PAGE 211
Not all tasks and options are available for each object type. The syntax for each object type is presented here. For single tables: MAINTAIN object-type object-name [,maintain-task ['task-options']...] [, other-options...
PAGE 212
• • • • mvlog statistics, refresh of all MVs on that table, reorg of all MVs on the table, reorg of all indexes on the MVs, and update of statistics for all MVs on the table. INDEX — The object-name specified is the index that needs to be maintained. The task performed is the reorg of that index. MVGROUP — The object-name specified is the MVGROUP that needs to be maintained. Tasks done are to refresh all MVs in that group. MV — The object-name specified is the MV that needs to be maintained.
PAGE 213
STOP ON ERROR If any of the maintain tasks return an error, that error is returned, and execution stops. RETURN DETAIL OUTPUT displays a detailed message from each of the individual tasks. The default is OFF. DISPLAY displays the tasks that are scheduled to be executed without actually executing them. DISPLAY DETAIL displays the tasks, and the queries that will be used to execute them, without actually executing the tasks. control-options NOTE: Control options are not supported for multiple tables.
PAGE 214
SQLNumResultCols is greater than zero (ODBC) or while rs.next() is true (JDBC) to retrieve the status and complete the query. Use the same fetch calls as you would for a SELECT statement that returns multiple rows of data. If you do not make fetch calls for MAINTAIN, the MAINTAIN operation will not complete, and you will not see any error information.
PAGE 215
penalty is incurred. In an environment where updates happen continuously, the second refresh affects all the MVs. Refreshing individual MVs out of group context might lead to a state where different MVs in the group are not in sync with each other. RECOMPUTE when used, the MVs in the MVGROUP are cleared and repopulated from scratch. Contrast this with incremental refresh when the RECOMPUTE option is not used. In this case, only changes since the last refresh are applied.
PAGE 216
• Enables update statistics on the specified table: maintain table t1_090, update statistics, enable; • Examples of MAINTAIN invoked with optional task options: maintain table t1, reorg ', rate 100'; Task: REORG_TABLE STATUS: Started Object: CAT.SCH.T1 Task: REORG_TABLE STATUS: Ended ET: 00:00:00.031 --SQL operation complete. maintain table t1, reorg ', rate 100', display detail; Task: REORG_TABLE STATUS: scheduled Command: REORG.TABLE.CAT.SCH.
PAGE 217
Reorg Task-Options Example >>maintain table t1, reorg ',rate 40'; Task: REORG_TABLE Status: Started Task: REORG_TABLE Status: Ended Object: CAT.SCH.T1 ET: 00:00:00.016 --- SQL operation complete. Reorg and Update Statistics Task-Options Example >>maintain table t1, update statistics 'on every column, (A,B) sample 2000000 rows', reorg ',rate 40', display detail; Task: REORG_TABLE Status: Scheduled Command: REORG TABLE CAT.SCH.
PAGE 218
POPULATE INDEX Utility • • • “Syntax Description of POPULATE INDEX” “Considerations for POPULATE INDEX” “Examples of POPULATE INDEX” POPULATE INDEX is a syntax-based utility that can be executed in the Neoview Command Interface. The POPULATE INDEX utility loads Neoview SQL indexes. POPULATE INDEX index ON table [index-option] index-option is ONLINE | OFFLINE Syntax Description of POPULATE INDEX index is an SQL identifier that specifies the simple name for the index.
PAGE 219
PURGEDATA Utility • • • “Syntax Description of PURGEDATA” “Considerations for PURGEDATA” “Examples of PURGEDATA” The PURGEDATA utility deletes all data from a Neoview SQL table and its related indexes. The PURGEDATA utility is a syntax-based utility that can be executed in the Neoview Command Interface. PURGEDATA table [IGNORE_TRIGGER] [NOLOG] Syntax Description of PURGEDATA table is the name of the table from which to purge the data. See “Database Object Names” (page 228).
PAGE 220
PAGE 221
4 SQL Language Elements Neoview SQL language elements, which include data types, expressions, functions, identifiers, literals, and predicates, occur within the syntax of SQL statements. The statement and command topics support the syntactical and semantic descriptions of the language elements in this section.
PAGE 222
Character Sets Each Neoview platform has a character set configuration that you choose when you order a new Neoview platform. The Neoview character set configuration determines how character data is sent back and forth between client applications and the Neoview database. Although you can specify only ISO88591 or UCS2 for a character column definition, the Neoview character set configuration permits you to store character string data encoded in other character sets.
PAGE 223
Columns A column is a vertical component of a table and is the relational representation of a field in a record. A column contains one data value for each row of the table. A column value is the smallest unit of data that can be selected from or updated in a table. Each column has a name that is an SQL identifier and is unique within the table or view that contains the column.
PAGE 224
Examples of Derived Column Names • These two examples show how to use names for derived columns. The first example shows (EXPR) as the column heading of the SELECT result table: SELECT AVG (salary) FROM persnl.employee; (EXPR) ---------------49441.52 --- 1 row(s) selected. The second example shows AVERAGE SALARY as the column heading: SELECT AVG (salary) AS "AVERAGE SALARY" FROM persnl.employee; "AVERAGE SALARY" ---------------49441.52 --- 1 row(s) selected.
PAGE 225
Constraints An SQL constraint is an object that protects the integrity of data in a table by specifying a condition that all the values in a particular column or set of columns of the table must satisfy. Neoview SQL enforces these constraints on SQL tables: CHECK Column or table constraint specifying a condition must be satisfied for each row in the table. NOT NULL Column constraint specifying the column cannot contain nulls.
PAGE 226
Correlation Names A correlation name is a name you can associate with a table reference that is a table, view, materialized view, or subquery in a SELECT statement to: • • • Distinguish a table or view from another table or view referred to in a statement Distinguish different uses of the same table Make the query shorter A correlation name can be explicit or implicit.
PAGE 227
Database Objects A database object is an SQL entity that exists in a namespace. SQL statements can access Neoview SQL objects. The subsections listed below describe these Neoview SQL objects. “Constraints” “Indexes” “Materialized Views” (page 263) “Tables” “Views” Ownership In Neoview SQL, the creator of a schema owns all the objects defined in the schema. In addition, you can use the GRANT and REVOKE statements to grant access privileges for a table or view to specified users.
PAGE 228
Database Object Names • • “Logical Names for SQL Objects” “SQL Object Namespaces” DML statements can refer to Neoview SQL database objects. To refer to a database object in a statement, use an appropriate database object name. For information on the types of database objects see “Database Objects” (page 227). Logical Names for SQL Objects You must refer to an SQL table or view by using a two-part logical name, also called an ANSI name: schema-name.
PAGE 229
Data Types Neoview SQL data types are character, datetime, interval, or numeric (exact or approximate): “Character String Data Types” (page 233) Fixed-length and variable-length character data types. “Datetime Data Types” (page 235) DATE, TIME, and TIMESTAMP data types. “Interval Data Types” (page 237) Year-month intervals (years and months) and day-time intervals (days, hours, minutes, seconds, and fractions of a second). “Numeric Data Types ” (page 238) Exact and approximate numeric data types.
PAGE 230
Type SQL Designation Description Size or Range (1) REAL Floating point number (32 bits) +/- 1.17549435e-38 through +/ 3.40282347e+38; stored in 4 bytes DOUBLE PRECISION Floating-point numbers (64 bits) with 1 through 52 bits of precision (52 bits of binary precision and 11 bits of exponent) +/- 2.2250738585072014e-308 through +/-1.
PAGE 231
Type SQL Designation Description Size or Range (1) Interval INTERVAL Duration of time; value is in YEAR no constraint(6) the YEAR/MONTH range or MONTH 0-11 the DAY/HOUR/MINUTE/ SECOND/FRACTION range DAY no constraint HOUR 0-23 MINUTE 0-59 SECOND 0-59 FRACTION(n) 0-999999 in which n is the number of significant digits (default is 6; minimum is 1; maximum is 6); stored in 2, 4, or 8 bytes depending on number of digits scale is the number of digits to the right of the decimal.
PAGE 232
Datetime Data Types Values of type datetime are mutually comparable and mutually assignable only if the types have the same datetime fields. A DATE, TIME, or TIMESTAMP value can be compared with another value only if the other value has the same data type. All comparisons are chronological.
PAGE 233
— — • • UNSIGNED is supported as extended NUMERIC precision data type SIGNED is supported as 64-bit integer CAST function allows conversion between regular NUMERIC and extended NUMERIC precision data type. Parameters in SQL queries support extended NUMERIC precision data type. Example for Extended NUMERIC Precision Data Type >>CREATE TABLE t( n NUMERIC(128,30)) NO PARTITION; --- SQL operation complete. >>SHOWDDL TABLE t; CREATE TABLE SCH.
PAGE 234
CHAR[ACTER] VARYING (length) [CHARACTERS] [char-set] [UPSHIFT] specifies a column with varying-length character data. VARYING specifies that the number of characters stored in the column can be fewer than the length. Note that values in a column declared as VARYING can be logically and physically shorter than the maximum length, but the maximum internal size of a VARYING column is actually four bytes larger than the size required for an equivalent column that is not VARYING.
PAGE 235
In Neoview SQL, the NCHAR type specification is equivalent to: • • • NATIONAL CHARACTER NATIONAL CHAR CHAR ... CHARACTER SET ..., where the character set is the character set for NCHAR Similarly, you can use NCHAR VARYING, NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, and VARCHAR ... CHARACTER SET ... , where the character set is the character set for NCHAR. The character set for NCHAR is determined when Neoview SQL is installed on the Neoview platform.
PAGE 236
Considerations for Datetime Data Types Datetime Ranges The range of values for the individual fields in a DATE, TIME, or TIMESTAMP column is specified as: yyyy Year, from 0001 to 9999 mm Month, from 01 to 12 dd Day, from 01 to 31 hh Hour, from 00 to 23 mm Minute, from 00 to 59 ss Second, from 00 to 59 msssss Microsecond, from 000000 to 999999 When you specify datetime_value (FORMAT ‘string’) in the DML statement and the specified format is ‘mm/dd/yyyy’,’MM/DD/YYYY’, or ‘yyyy/mm/dd’ or ‘yyyy-mm
PAGE 237
Interval Data Types Values of interval data type represent durations of time in year-month units (years and months) or in day-time units (days, hours, minutes, seconds, and fractions of a second).
PAGE 238
Within the definition of an interval range (other than a single field), the start-field and end-field can be any of the specified fields with these restrictions: • • An interval range is either year-month or day-time—that is, if the start-field is YEAR, the end-field is MONTH; if the start-field is DAY, HOUR, or MINUTE, the end-field is also a time field. The start-field must precede the end-field within the hierarchy: YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
PAGE 239
SMALLINT [SIGNED|UNSIGNED] specifies an exact numeric column—a two-byte binary integer, SIGNED or UNSIGNED. The column stores integers in the range unsigned 0 to 65535 or signed -32768 to +32767. The default is SIGNED. INT[EGER] [SIGNED|UNSIGNED] specifies an exact numeric column—a 4-byte binary integer, SIGNED or UNSIGNED. The column stores integers in the range unsigned 0 to 4294967295 or signed -2147483648 to +2147483647. The default is SIGNED.
PAGE 240
Expressions An SQL value expression, referred to as an expression, can evaluate to a value with one of these: “Character Value Expressions” (page 240) Operands can be combined with the concatenation operator (||). Example: 'HOUSTON,' ||' TEXAS' “Datetime Value Expressions” (page 242) Operands can be combined in specific ways with arithmetic operators.
PAGE 241
Examples of Character Value Expressions These are examples of character value expressions: Expression Description 'ABILENE' Character string literal. 'ABILENE ' ||' TEXAS' The concatenation of two string literals. 'ABILENE ' ||' TEXAS ' || x’55 53 41' The concatenation of three string literals to form the literal: 'ABILENE TEXAS USA' 'Customer ' || custname The concatenation of a string literal with the value in column CUSTNAME. CAST (order_date AS CHAR(10)) CAST function applied to a DATE value.
PAGE 242
Datetime Value Expressions • • “Considerations for Datetime Value Expressions” “Examples of Datetime Value Expressions” The operands of a datetime value expression can be combined in specific ways with arithmetic operators. In this syntax diagram, the data type of a datetime primary is DATE, TIME, or TIMESTAMP. The data type of an interval term is INTERVAL.
PAGE 243
• • Any aggregate functions, sequence functions, scalar subqueries, CASE expressions, or CAST expressions that return datetime or interval values OLAP window functions Considerations for Datetime Value Expressions Data Type of Result In general, the data type of the result is the data type of the datetime-primary part of the datetime expression. For example, datetime value expressions include: CURRENT_DATE + INTERVAL '1' DAY The sum of the current date and an interval value of one day.
PAGE 244
Examples of Datetime Value Expressions The PROJECT table consists of five columns that use the data types NUMERIC, VARCHAR, DATE, TIMESTAMP, and INTERVAL DAY. Suppose that you have inserted values into the PROJECT table. For example: INSERT INTO persnl.project VALUES (1000,'SALT LAKE CITY',DATE '2007-04-10', TIMESTAMP '2007-04-21:08:15:00.
PAGE 245
-------------------------2008-04-20 16:45:00.000000 The result of subtracting 15 hours and 30 minutes from 2007-04-21 08:15:00.00 is 2007-04-20 16:45:00.00.
PAGE 246
Interval Value Expressions • • “Considerations for Interval Value Expressions” “Examples of Interval Value Expressions” The operands of an interval value expression can be combined in specific ways with addition and subtraction operators. In this syntax diagram, the data type of a datetime expression is DATE, TIME, or TIMESTAMP; the data type of an interval term or expression is INTERVAL.
PAGE 247
the result of adding or subtracting two interval qualifiers, the interval qualifier of the result depends on the interval qualifier of the target column. Considerations for Interval Value Expressions Start and End Fields Within the definition of an interval range, the start-field and end-field can be any of the specified fields with these restrictions: • • An interval is either year-month or day-time.
PAGE 248
Operands Result type Time – Interval Time Timestamp + Interval or Interval + Timestamp Timestamp Timestamp + Numeric or Numeric + Timestamp Timestamp Timestamp - Numeric Timestamp Timestamp – Interval Timestamp year-month Interval + year-month Interval year-month Interval day-time Interval + day-time Interval day-time Interval year-month Interval – year-month Interval year-month Interval day-time Interval – day-time Interval day-time Interval Time – Time Interval Timestamp – Timestamp
PAGE 249
2500 2007-10-10 2007-12-21:09:00:00.0000 60 3000 2007-08-21 2007-10-21:08:10:00.0000 60 4000 2007-09-21 2007-10-21:10:15:00.0000 30 5000 2007-09-28 2007-10-28:09:25:01.1111 30 • Suppose that the CURRENT_TIMESTAMP is 2000-01-06 11:14:41.748703. Find the number of days, hours, minutes, seconds, and fractional seconds in the difference of the current timestamp and the SHIP_TIMESTAMP in the PROJECT table: SELECT projcode, (CURRENT_TIMESTAMP - ship_timestamp) DAY(4) TO SECOND(6) FROM samdbcat.
PAGE 250
Numeric Value Expressions • • “Considerations for Numeric Value Expressions” “Examples of Numeric Value Expressions” The operands of a numeric value expression can be combined in specific ways with arithmetic operators. In this syntax diagram, the data type of a term, factor, or numeric primary is numeric.
PAGE 251
Numeric expressions are evaluated according to these additional rules: • • • • An expression with a numeric operator evaluates to null if any of the operands is null. Dividing by 0 causes an error. Exponentiation is allowed only with numeric data types. If the first operand is 0 (zero), the second operand must be greater than 0, and the result is 0. If the second operand is 0, the first operand cannot be 0, and the result is 1.
PAGE 252
Identifiers SQL identifiers are names used to identify tables, views, columns, and other SQL entities. The two types of identifiers are regular and delimited. A delimited identifier is enclosed in double quotes ("). An identifier of either type can contain up to 128 characters. Regular Identifiers Regular identifiers begin with a letter (A through Z or a through z), but can also contain digits (0 through 9), or underscore characters (_). Regular identifiers are not case-sensitive.
PAGE 253
You can use reserved words as delimited identifiers. For example, table is not allowed as a regular identifier, but "table" is allowed as a delimited identifier.
PAGE 254
Indexes An index is an ordered set of pointers to rows of a table. Each index is based on the values in one or more columns. There is always a one-to-one correspondence between index rows and base table rows. SQL Indexes Each row in a Neoview SQL index contains: • • The columns specified in the CREATE INDEX statement The clustering key of the underlying table (the user-defined clustering key) An index name is an SQL identifier.
PAGE 255
Keys Neoview SQL supports these types of keys: • • • “Clustering Keys” “Index Keys” “Primary Keys” Clustering Keys Neoview SQL organizes records of a table or index by using a b-tree based on the “clustering key”. Values of the clustering key act as logical row-ids. The primary key is the clustering key and it must be unique. Index Keys There is always a one-to-one correspondence between index rows and base table rows.
PAGE 256
Literals A literal is a constant you can use in an expression, in a statement, or as a parameter value. An SQL literal can be one of these data types: “Character String Literals” (page 256) A series of characters enclosed in single quotes. Example: 'Planning' “Datetime Literals” (page 257) Begins with keyword DATE, TIME, or TIMESTAMP and followed by a character string.
PAGE 257
space is space sequences that can be added before or after hex-code-value for readability. The encoding for space must be the TERMINAL_CHARSET for an interactive interface and the SQL module character set for the programmatic interface. Considerations for Character String Literals Using String Literals A string literal can be as long as a character column. See “Character String Data Types” (page 233).
PAGE 258
the corresponding datetime data types. You cannot use leading or trailing spaces within a datetime string (within the single quotes). A datetime literal begins with the DATE, TIME, or TIMESTAMP keyword and can appear in default, USA, or European format. DATE 'date' | TIME 'time' | TIMESTAMP 'timestamp' date is: yyyy-mm-dd | mm/dd/yyyy | dd.mm.yyyy Default USA European time is: hh:mm:ss.msssss | hh:mm:ss.msssss [am | pm] | hh.mm.ss.msssss Default USA European timestamp is: yyyy-mm-dd hh:mm:ss.
PAGE 259
An interval literal is a constant of data type INTERVAL that represents a positive or negative duration of time as a year-month or day-time interval; it begins with the keyword INTERVAL optionally preceded or followed by a minus sign (for negative duration). You cannot include leading or trailing spaces within an interval string (within single quotes). [-]INTERVAL [-]{'year-month' | 'day:time'} interval-qualifier year-month is: years [-months] | months day:time is: days [[:]hours [:minutes [:seconds [.
PAGE 260
'year-month' | 'day:time' specifies the date and time components of an interval literal. The day and hour fields can be separated by a space or a colon. The interval literal strings are: years Unsigned integer that specifies a number of years. years can be up to 18 digits, or 16 digits if months is the end-field. The maximum for the leading-precision is specified within the interval qualifier by either YEAR(18) or YEAR(16) TO MONTH. months Unsigned integer that specifies a number of months.
PAGE 261
Examples of Interval Literals INTERVAL '1' MONTH Interval of 1 month INTERVAL '7' DAY Interval of 7 days INTERVAL '2-7' YEAR TO MONTH Interval of 2 years, 7 months INTERVAL '5:2:15:36.33' DAY TO SECOND(2) Interval of 5 days, 2 hours, 15 minutes, and 36.33 seconds INTERVAL - '5' DAY Interval that subtracts 5 days INTERVAL '100' DAY(3) Interval of 100 days. This example requires an explicit leading precision of 3 because the default is 2.
PAGE 262
Examples of Numeric Literals • 262 These are all numeric literals, along with their display format: Literal Display Format 477 477 580.45 580.45 +005 5 -.3175 -.3175 1300000000 1300000000 99. 99 -0.123456789012345678 -.123456789012345678 99E-2 9.9000000E-001 12.3e+5 1.
PAGE 263
Materialized Views A materialized view (MV) is a view that is materialized by storing its tuples as a regular table. As a table, a materialized view can be queried directly, indexes can be created for performance, partitioning can promote scalability, and so on. A materialized view provides fast access to data. This is especially important in applications where the query rate is high and the MVs are complex, for example, in aggregate queries over large volumes of data.
PAGE 264
MVGROUPs An MVGROUP is a group of materialized views that are refreshed together. One way to group MVs is by the frequency of the REFRESH (for example, each day, each week, each month). Another way is to create groups to preserve consistency, that is, to group all the MVs on common tables.
PAGE 265
Null Null is a special symbol, independent of data type, that represents an unknown. The Neoview SQL keyword NULL represents null. Null indicates that an item has no value. For sorting purposes, null is greater than all other values. You cannot store null in a column by using INSERT or UPDATE, unless the column allows null. A column that allows null can be null at any row position. A nullable column has extra bytes associated with it in each row.
PAGE 266
Null and Expression Evaluation Comparison Expression Type Condition Result Boolean operators (AND, OR, NOT) Either operand is null. For AND, the result is null. For OR, the result is true if the other operand is true, or null if the other operand is null or false. For NOT, the result is null. Arithmetic operators Either or both operands are null. The result is null. NULL predicate The operand is null. The result is true. Aggregate (or set) functions (except COUNT) Some rows have null columns.
PAGE 267
Predicates A predicate determines an answer to a question about a value or group of values. A predicate returns true, false, or, if the question cannot be answered, unknown. Use predicates within search conditions to choose rows from tables or views. “BETWEEN Predicate” (page 267) Determines whether a sequence of values is within a range of sequences of values.
PAGE 268
Considerations for BETWEEN Logical Equivalents Using AND and OR The predicate expr1 BETWEEN expr2 AND expr3 is true if and only if this condition is true: expr2 <= expr1 AND expr1 <= expr3 The predicate expr1 NOT BETWEEN expr2 AND expr3 is true if and only if this condition is true: expr2 > expr1 OR expr1 > expr3 Descending Columns in Keys If a clause specifies a column in a key BETWEEN expr2 and expr3, expr3 must be greater than expr2 even if the column is specified as DESCENDING within its table definit
PAGE 269
row-value-constructor comparison-op row-value-constructor comparison-op is: = Equal | <> Not equal | < Less than | > Greater than | <= Less than or equal to | >= Greater than or equal to row-value-constructor is: (expression [,expression]...) | row-subquery row-value-constructor specifies an operand of a comparison predicate. The two operands can be either of these: (expression [,expression ]...) is a sequence of SQL value expressions, separated by commas and enclosed in parentheses.
PAGE 270
Predicate X>=Y is true if X>Y is true or X=Y is true. In this scenario, Neoview SQL might look through all components and return true if they are all equal. It stops at the first nonequal components, Xm<>Ym. If Xm>Ym, the predicate is true. Otherwise, it is false. Predicate XYm. If Xm
PAGE 271
(last_name > 'MOSS') OR (last_name = 'MOSS' AND first_name > 'DUNCAN') • Compare two datetime values START_DATE and the result of the CURRENT_DATE function: START_DATE < CURRENT_DATE • Compare two datetime values START_DATE and SHIP_TIMESTAMP: CAST (start_date AS TIMESTAMP) < ship_timestamp • Compare two INTERVAL values: JOB1_TIME < JOB2_TIME Suppose that JOB1_TIME, defined as INTERVAL DAY TO MINUTE, is 2 days 3 hours, and JOB2_TIME, defined as INTERVAL DAY TO HOUR, is 3 days.
PAGE 272
... --- 35 row(s) selected. The first subquery of this query determines the minimum salary of employees from other departments whose salary is greater than the average salary for department 1500. The main query then finds the names of employees who are not in department 1500 and whose salary is less than the minimum salary determined by the first subquery. EXISTS Predicate The EXISTS predicate determines whether any rows are selected by a subquery.
PAGE 273
WHERE EXISTS (SELECT partnum FROM invent.partloc PL WHERE PS.partnum = PL.partnum AND qty_on_hand < 20); PARTNUM ------212 212 2001 2003 ... SUPPNUM ------1 3 1 2 --- 18 row(s) selected. IN Predicate • • “Considerations for IN” “Examples of IN” The IN predicate determines if a sequence of values is equal to any of the sequences of values in a list of sequences. The NOT operator reverses its truth value. For example, if IN is true, NOT IN is false.
PAGE 274
Considerations for IN Logical Equivalent Using ANY (or SOME) The predicate expr IN (expr1, expr2, ... ) is true if and only if the following predicate is true: expr = ANY (expr1, expr2, ... ) IN Predicate Results The IN predicate is true if and only if either of these is true: • The result of the row-value-constructor (a row or sequence of values) is equal to any row of column values specified by table-subquery.
PAGE 275
FROM invent.partloc); PARTNUM ------186 PARTDESC -----------------186 MegaByte Disk --- 1 row(s) selected. • Find those items (and their suppliers) in PARTS that have a supplier in the PARTSUPP table: SELECT P.partnum, P.partdesc, S.suppnum, S.suppname FROM sales.parts P, invent.supplier S WHERE P.partnum, S.suppnum IN (SELECT partnum, suppnum FROM invent.
PAGE 276
Using NOT If you specify NOT, the predicate is true if the pattern does not match any string in the match-value or is not the same length as any string in the match-value. For example, NAME NOT LIKE '_Z' is true if the string is not two characters long or the last character is not Z. In a search condition, the predicate NAME NOT LIKE '_Z' is equivalent to NOT (NAME LIKE '_Z').
PAGE 277
into a CHAR(4) column becomes 'JOE ' (three characters plus one blank). The LIKE predicate is true only if the column value and the comparison value are the same length. The column value 'JOE ' does not match 'JOE' but does match 'JOE%'. Comparing the Pattern to VARCHAR Columns Columns of variable-length character data types do not include trailing blanks unless blanks are specified when data is entered. For example, the value 'JOE' inserted in a VARCHAR(4) column is 'JOE' with no trailing blanks.
PAGE 278
If all of the expressions in the row-value-constructor are null, the IS NULL predicate is true. Otherwise, it is false. If none of the expressions in the row-value-constructor are null, the IS NOT NULL predicate is true. Otherwise, it is false. Considerations for NULL Summary of NULL Results Let rvc be the value of the row-value-constructor. This table summarizes the results of NULL predicates. The degree of a rvc is the number of values in the rvc.
PAGE 279
row-value-constructor specifies the first operand of a quantified comparison predicate. The first operand can be either of: (expression [,expression ]...) is a sequence of SQL value expressions, separated by commas and enclosed in parentheses. expression cannot include an aggregate function unless expression is in a HAVING clause. expression can be a scalar subquery (a subquery that returns a single row consisting of a single column). See “Expressions” (page 240).
PAGE 280
FROM persnl.employee WHERE jobcode = 420); The inner query providing the comparison values yields these results: SELECT salary FROM persnl.employee WHERE jobcode = 420; SALARY ----------33000.00 36000.00 18000.10 --- 3 row(s) selected. The SELECT statement using this inner query yields these results. The salaries listed are greater than the salary of every employees with jobcode equal to 420—that is, greater than $33,000.00, $36,000.00, and $18,000.
PAGE 281
The SELECT statement using this inner query yields these results. All of the order numbers listed have part number equal to any part number with more than five total units in stock—that is, equal to 2403, 5100, 5103, 6301, 6500, and so on: SELECT ordernum, partnum, qty_ordered FROM sales.odetail WHERE partnum = ANY (SELECT partnum FROM sales.odetail WHERE qty_ordered > 5); Order/Num ---------100210 100210 100210 100210 100250 100250 100250 100250 ......
PAGE 282
Privileges These privileges are used by the GRANT and REVOKE statements. For further information on GRANT, see “GRANT Statement” (page 133), “GRANT EXECUTE Statement” (page 135), or “GRANT SCHEMA Statement” (page 137). For further information on REVOKE, see “REVOKE Statement” (page 154), “REVOKE EXECUTE Statement” (page 156), or “REVOKE SCHEMA Statement” (page 158).
PAGE 283
• • • UPDATE - Can use UPDATE statement. REFERENCES - Can create constraints that reference the object. ALL PRIVILEGES - Can have all privileges (SELECT, DELETE, INSERT, UPDATE, EXECUTE, and REFERENCES) that apply to the object type. For schemas: • • • • • • • • • • • • • • • • • • • • • • • • • • • • SELECT - Can use SELECT statement. DELETE - Can use DELETE statement. INSERT - Can use INSERT statement. UPDATE - Can use UPDATE statement. REFERENCES - Can create constraints that reference the object.
PAGE 284
• • ALL_DDL - Any table, view, materialized view, or procedure created in the schema after this privilege is granted will be able to perform any DDL privilege, including all variants of CREATE, DROP, ALTER, and MAINTAIN. ALL_DML - Any table, view, materialized view, or procedure created in the schema after this privilege is granted will be able to perform any DML privilege, including DELETE, EXECUTE, INSERT, REFERENCES, SELECT, or UPDATE.
PAGE 285
Schemas The ANSI SQL:1999 schema name is an SQL identifier that is unique for a given ANSI catalog name. Neoview SQL automatically qualifies a schema name with the current default catalog name, which is always Neo. The logical name of the form schema.object is an ANSI name. The part schema denotes the ANSI-defined schema. To be compliant with ANSI SQL:1999, Neoview SQL provides support for ANSI object names. By using these names, you can develop ANSI-compliant applications that access all SQL objects.
PAGE 286
Search Condition A search condition is used to choose rows from tables or views, depending on the result of applying the condition to rows. The condition is a Boolean expression consisting of predicates combined together with OR, AND, and NOT operators.
PAGE 287
Examples of Search Condition • Select rows by using a search condition composed of three comparison predicates joined by AND operators: select O.ordernum, O.deliv_date, OD.qty_ordered FROM sales.orders O, sales.odetail OD WHERE qty_ordered < 9 AND deliv_date <= DATE '2008-11-01' AND O.ordernum = OD.ordernum; ORDERNUM DELIV_DATE QTY_ORDERED ---------- ---------- ----------100210 2008-04-10 3 100210 2008-04-10 3 100210 2008-04-10 6 100250 2008-06-15 4 101220 2008-12-15 3 ... --- 28 row(s) selected.
PAGE 288
Subquery A subquery is a query expression enclosed in parentheses. Its syntactic form is specified in the syntax of a SELECT statement. For further information about query expressions, see “SELECT Statement” (page 162). A subquery is used to provide values for a BETWEEN, comparison, EXISTS, IN, or quantified comparison predicate in a search condition. It is also used to specify a derived table in the FROM clause of a SELECT statement. A subquery can be a table, row, or scalar subquery.
PAGE 289
Correlated Subqueries When Providing Comparison Values In the search condition of a subquery, when you refer to columns of any table or view defined in an outer query, the reference is called an outer reference. A subquery containing an outer reference is called a correlated subquery. If you refer to a column name that occurs in more than one outer query, you must qualify the column name with the correlation name of the table or view to which it belongs.
PAGE 290
Tables A table is a logical representation of data in which a set of records is represented as a sequence of rows, and the set of fields common to all rows is represented by columns. A column is a set of values of the same data type with the same definition. The intersection of a row and column represents the data value of a particular field in a particular record. Every table must have one or more columns, but the number of rows can be zero. There is no inherent order of rows within a table.
PAGE 291
Triggers A trigger is a mechanism that resides in the database and specifies that when a particular action—an insert, delete, or update—occurs on a particular table, Neoview SQL should automatically perform one or more additional actions. For a complete description of triggers and their use, see the “CREATE TRIGGER Statement” (page 93), “ALTER TRIGGER Statement” (page 52), “DROP TRIGGER Statement” (page 115).
PAGE 292
Views A view provides an alternate way of looking at data in one or more tables. A view is a named specification of a result table, which is a set of rows selected or generated from one or more base tables or other views. The specification is a SELECT statement that is executed whenever the view is referenced. A view is a logical table created with the CREATE VIEW statement and derived by projecting a subset of columns, restricting a subset of rows, or both, from one or more base tables or other views.
PAGE 293
5 SQL Clauses Clauses are used by Neoview SQL statements to specify default values, ways to sample or sort data, how to store physical data, and other details. This section describes: • • • • • “DEFAULT Clause”. Specifies a default value for a column being created. “FORMAT Clause”. Specifies the format to use. “SAMPLE Clause”. Specifies the sampling method used to select a subset of the intermediate result table of a SELECT statement. “SEQUENCE BY Clause”.
PAGE 294
DEFAULT Clause “Examples of DEFAULT” The DEFAULT option of the CREATE TABLE or ALTER TABLE table-name ADD COLUMN statement specifies a default value for a column being created. The default value is used when a row is inserted in the table without a value for the column.
PAGE 295
identity-column-specification indicates that a particular column is a sequence-generating IDENTITY column. GENERATED BY DEFAULT AS IDENTITY indicates that the system will generate values for this column by default or values can be supplied by the user. GENERATED ALWAYS AS IDENTITY indicates that the system will always generate values for this column by default. User-supplied values are not allowed with GENERATED ALWAYS AS IDENTITY.
PAGE 296
FORMAT Clause • • • “Considerations for Date Formats” “Considerations for Other Formats” (page 297) “Examples of FORMAT” The FORMAT clause specifies the output format for DATE values. It can also be used to specify the length of character output or to specify separating the digits of integer output with colons. Date Formats: (FORMAT 'format-string') | (DATE, FORMAT 'format-string') format-string for Date Formats is: YYYY-MM-DD MM/DD/YYYY YY/MM/DD YYYY/MM/DD YYYYMMDD DD.MM.
PAGE 297
-99:99:99:99 specifies that the FORMAT clause output format is a timestamp. The input must be a numeric value. Considerations for Date Formats The expression preceding the (FORMAT ”format-string') clause must be a DATE value. The expression preceding the (DATE, FORMAT 'format-string') clause must be a quoted string in the USA, EUROPEAN, or DEFAULT date format. Considerations for Other Formats For XXX, the expression preceding the (FORMAT 'format-string') clause must be a numeric value or a string value.
PAGE 298
SAMPLE Clause • • “Considerations for SAMPLE” “Examples of SAMPLE” The SAMPLE clause of the SELECT statement specifies the sampling method used to select a subset of the intermediate result table of a SELECT statement. The intermediate result table consists of the rows returned by a WHERE clause or, if there is no WHERE clause, the FROM clause. See “SELECT Statement” (page 162). SAMPLE is a Neoview SQL extension.
PAGE 299
CLUSTERS OF number-blocks BLOCKS specifies cluster sampling. You can use the CLUSTERS clause for a base table only if there is no WHERE clause in the SELECT statement. First, a cluster is chosen randomly, and then all rows in the selected cluster are added to the sample. The size of the cluster is determined by number-blocks. This process is repeated until the sample size is generated. See “Cluster Sampling” (page 300). BALANCE If you specify a BALANCE expression, Neoview SQL performs stratified sampling.
PAGE 300
Cluster Sampling Cluster sampling is an option supported by the SAMPLE RANDOM clause in a SELECT statement. A cluster, in this sense, is a logically contiguous set of disk blocks in the file in which a table is stored. The number of blocks in a cluster is specified in the CLUSTERS subclause of the SAMPLE RANDOM clause.
PAGE 301
136000.00 37000.40 ... --- 50 row(s) selected. • Return the SALARY of 50 sales people. In this case, the table is clustered on EMPID. If the optimizer chooses a plan to access rows using the primary access path, the result consists of salaries of the 50 sales people with the smallest employee identifiers. SELECT salary FROM salesperson SAMPLE FIRST 50 ROWS; SALARY ----------175500.00 137000.10 136000.00 138000.40 75000.00 90000.00 ... --- 50 row(s) selected.
PAGE 302
• Compute the average salary of a random 10 percent of the sales people using cluster sampling where each cluster is 4 blocks. You will get a different result each time you run this query because it is based on a random sample. SELECT AVG(salary) FROM salesperson SAMPLE RANDOM 10 PERCENT CLUSTERS OF 4 BLOCKS; (EXPR) --------------------50219.524 --- 1 row(s) selected. For this query execution, the number of rows returned is limited by the total number of rows in the SALESPERSON table.
PAGE 303
(EXPR) -------------------37000.000 --- 1 row(s) selected. Note that the results of this query and some of the results of previous queries might return null: SELECT AVG(salary) FROM ( SELECT salary, dnum FROM salesperson SAMPLE RANDOM 10 PERCENT ) AS S, department D WHERE S.DNUM = D.DNUM AND D.NAME = 'CORPORATE'; (EXPR) -------------------? --- 1 row(s) selected. For this query execution, the number of rows returned by the embedded query is limited by the total number of rows in the SALESPERSON table.
PAGE 304
--- 88 row(s) selected. • The BALANCE option enables stratified sampling. Retrieve the age and salary of 1000 sales people such that 50 percent of the result are male and 50 percent female. SELECT age, sex, salary FROM salesperson SAMPLE FIRST BALANCE WHEN sex = 'male' THEN 15 ROWS WHEN sex = 'female' THEN 15 ROWS END ORDER BY age; AGE SEX ----------- -----22 male 22 male 22 female 22 male ... ... SALARY ----------28000.00 90000.00 136000.00 37000.40 ... --- 30 row(s) selected.
PAGE 305
39 75 ... GRAPHICPRINTER, M3 LASERPRINTER, X1 ... S W ... 20000.00 42000.00 ... --- 30 row(s) selected.
PAGE 306
SEQUENCE BY Clause • • “Considerations for SEQUENCE BY” “Examples of SEQUENCE BY” The SEQUENCE BY clause of the SELECT statement specifies the order in which to sort the rows of the intermediate result table for calculating sequence functions. This option is used for processing time-sequenced rows in data mining applications. See “SELECT Statement” (page 162). SEQUENCE BY is a Neoview SQL extension. SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] | DESC[ENDING]]]...
PAGE 307
AS tab2 (ordernum, avg_price) SEQUENCE BY ordernum; • Like aggregate functions, sequence functions generate an intermediate result. If the query has a WHERE clause, its search condition is applied during the generation of the intermediate result. Therefore, you cannot use sequence functions in the WHERE clause of a SELECT statement.
PAGE 308
FROM orders o, odetail d WHERE o.ordernum=d.ordernum SEQUENCE BY o.order_date, o.ordernum, d.partnum ORDER BY o.order_date, o.ordernum, d.partnum; Order/Num AMOUNT ------------------- MCOUNT Part/Num Order/Date ORDER_TOTAL TOTAL_SALES ----------- -------- ----------------------- -------------- 100250 14000.00 100250 4000.00 100250 950.00 1 14000.00 2 18000.00 3 18950.00 200300 28000.00 200300 10000.00 200300 14000.00 ... 800660 1650.00 800660 5100.00 1 28000.00 2 38000.00 3 52000.00 ... 18 187360.
PAGE 309
TRANSPOSE Clause • • “Considerations for TRANSPOSE” “Examples of TRANSPOSE” The TRANSPOSE clause of the SELECT statement generates for each row of the SELECT source table a row for each item in the transpose item list. The result table of the TRANSPOSE clause has all the columns of the source table plus, for each transpose item list, a value column or columns and an optional key column. TRANSPOSE is a Neoview SQL extension. TRANSPOSE transpose-set [transpose-set]...
PAGE 310
parentheses must be equal for each list. The expressions in the same ordinal position within the parentheses must have compatible data types. For example, in the transpose set TRANSPOSE (A,X),(B,Y),(C,Z) AS (V1,V2), the expressions A,B, and C have compatible data types, and the expressions X,Y, and Z have compatible data types. transpose-col-list specifies the columns that consist of the evaluation of expressions in the item list as the expressions are applied to rows of the source table.
PAGE 311
Data Type of the TRANSPOSE Result The data type of each of the value columns is the union compatible data type of the corresponding expressions in the transpose-item-list. You cannot have expressions with data types that are not compatible in a transpose-item-list. For example, in TRANSPOSE (A,X),(B,Y),(C,Z) AS (V1,V2), the data type of V1 is the union compatible type for A, B, and C, and the data type of V2 is the union compatible type for X, Y, and Z. See “Comparable and Compatible Data Types” (page 231).
PAGE 312
The table MYTABLE has columns A, B, C, D, E, and F with related data. The columns A, B, and C are type INTEGER, and columns D, E, and F are type CHAR. A B C D E F 1 10 100 d1 e1 f1 2 20 200 d2 e2 f2 • Suppose that MYTABLE has only the first three columns: A, B, and C.
PAGE 313
The result table of the TRANSPOSE query is: • KEYCOL VALCOL D COUNT(*) 1 1 d1 1 2 10 d1 1 3 100 d1 1 1 2 d2 1 2 20 d2 1 3 200 d2 1 This query shows how to use COUNT applied to VALCOL. The result table of the TRANSPOSE query shows the number of distinct values in VALCOL. SELECT COUNT(DISTINCT VALCOL) FROM mytable TRANSPOSE A, B, C AS VALCOL KEY BY KEYCOL GROUP BY KEYCOL; (EXPR) -------------------2 2 2 --- 3 row(s) selected.
PAGE 314
• KEYCOL1 VALCOL1 KEYCOL2 VALCOL2 2 20 3 f2 3 200 1 d2 3 200 2 e2 3 200 3 f2 This query shows how a TRANSPOSE clause can contain multiple transpose sets—that is, multiple transpose-item-list AS transpose-col-list. The expressions A, B, and C are of type integer, and expressions D, E, and F are of type character.
PAGE 315
• KEYCOL VALCOL1 VALCOL2 (EXPR) 5 ? e2 1 6 ? f2 1 4 ? d1 1 5 ? e1 1 6 ? f1 1 This query shows how an item in the transpose item list can contain a list of expressions and that the KEY BY clause is optional: SELECT * FROM mytable TRANSPOSE (1, A, 'abc'), (2, B, 'xyz') AS (VALCOL1, VALCOL2, VALCOL3); The result table of the TRANSPOSE query is: A B C D E F VALCOL1 VALCOL2 VALCOL3 1 10 100 d1 e1 f1 1 1 abc 1 10 100 d1 e1 f1 2 10 xyz 2 20 200 d2 e2 f2 1 2
PAGE 316
PAGE 317
6 SQL Functions and Expressions This section describes the syntax and semantics of specific functions and expressions that you can use in Neoview SQL statements. The functions and expressions are categorized according to their functionality.
PAGE 318
include a GROUP BY clause but you specify an aggregate function in the select list, all rows of the SELECT result table form the one and only group. See the individual entry for the function. Character String Functions These functions manipulate character strings and use a character value expression as an argument or return a result of a character data type.
PAGE 319
“SUBSTRING/SUBSTR Function” (page 454) Extracts a substring from a character string. “TRANSLATE Function” (page 462) Translates a character string from a source character set to a target character set. “TRIM Function” (page 463) Removes leading or trailing characters from a character string. “UCASE Function” (page 464) Upshifts alphanumeric characters. You can also use UPSHIFT or UPPER. “UPPER Function” (page 472) Upshifts alphanumeric characters. You can also use UPSHIFT or UCASE.
PAGE 320
“DAYOFMONTH Function” (page 368) Returns an integer value in the range 1 through 31 that represents the corresponding day of the month. You can also use DAY. “DAYOFWEEK Function” (page 369) Returns an integer value in the range 1 through 7 that represents the corresponding day of the week. “DAYOFYEAR Function” (page 370) Returns an integer value in the range 1 through 366 that represents the corresponding day of the year.
PAGE 321
“DEGREES Function” (page 374) Converts a numeric value expression expressed in radians to the number of degrees. “EXP Function” (page 379) Returns the exponential value (to the base e) of a numeric value expression. “FLOOR Function” (page 384) Returns the largest integer less than or equal to a numeric value expression. “LOG Function” (page 393) Returns the natural logarithm of a numeric value expression. “LOG10 Function” (page 394) Returns the base 10 logarithm of a numeric value expression.
PAGE 322
such operations may not be parallelized because the entire sorted result set must be available in order to compute the result of the sequence function. Difference sequence functions: “DIFF1 Function” (page 375) Calculates differences between values of a column expression in the current row and previous rows. “DIFF2 Function” (page 377) Calculates differences between values of the result of DIFF1 of the current row and DIFF1 of previous rows.
PAGE 323
“ROWS SINCE CHANGED Function” (page 436) Returns the number of rows counted since the specified set of values last changed. “THIS Function” (page 459) Used in ROWS SINCE to distinguish between the value of the column in the current row and the value of the column in previous rows. See “SEQUENCE BY Clause” (page 306) and the individual entry for each function.
PAGE 324
ABS Function The ABS function returns the absolute value of a numeric value expression. ABS is a Neoview SQL extension. ABS (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the ABS function. The result is returned as an unsigned numeric value if the precision of the argument is less than 10 or as a LARGEINT if the precision of the argument is greater than or equal to 10. See “Numeric Value Expressions” (page 250).
PAGE 325
ACOS Function The ACOS function returns the arccosine of a numeric value expression as an angle expressed in radians. ACOS is a Neoview SQL extension. ACOS (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the ACOS function. The range for the value of the argument is from -1 to +1. See “Numeric Value Expressions” (page 250). Examples of ACOS • The ACOS function returns the value 3.49044274380724416E-001 or approximately 0.
PAGE 326
ADD_MONTHS Function The ADD_MONTHS function adds the integer number of months specified by int_expr to datetime_expr and normalizes the result. ADD_MONTHS is a Neoview extension. ADD_MONTHS (datetime_expr, int_expr [, int2 ]) datetime_expr is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. The return value is the same type as the datetime_expr. See “Datetime Value Expressions” (page 242).
PAGE 327
ASCII Function The ASCII function returns the integer that is the ASCII code of the first character in a character string expression associated with the ISO8891 character set. ASCII is a Neoview SQL extension. ASCII (character-expression) character-expression is an SQL character value expression that specifies a string of characters. See “Character Value Expressions” (page 240).
PAGE 328
ASIN Function The ASIN function returns the arcsine of a numeric value expression as an angle expressed in radians. ASIN is a Neoview SQL extension. ASIN (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the ASIN function. The range for the value of the argument is from -1 to +1. See “Numeric Value Expressions” (page 250). Examples of ASIN • This function returns the value 3.49044414403046400E-001 or approximately 0.
PAGE 329
ATAN Function The ATAN function returns the arctangent of a numeric value expression as an angle expressed in radians. ATAN is a Neoview SQL extension. ATAN (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the ATAN function. See “Numeric Value Expressions” (page 250). Examples of ATAN • This function returns the value 8.72766423249958272E-001 or approximately 0.8727 in radians (which is 50 degrees): ATAN (1.
PAGE 330
ATAN2 Function The ATAN2 function returns the arctangent of the x and y coordinates, specified by two numeric value expressions, as an angle expressed in radians. ATAN2 is a Neoview SQL extension. ATAN2 (numeric-expression-x,numeric-expression-y) numeric-expression-x, numeric-expression-y are SQL numeric value expressions that specify the value for the x and y coordinate arguments of the ATAN2 function. See “Numeric Value Expressions” (page 250). Example of ATAN2 This function returns the value 2.
PAGE 331
AVG Function AVG is an aggregate function that returns the average of a set of numbers. AVG ([ALL | DISTINCT] expression) ALL | DISTINCT specifies whether duplicate values are included in the computation of the AVG of the expression. The default option is ALL, which causes duplicate values to be included. If you specify DISTINCT, duplicate values are eliminated before the AVG function is applied. expression specifies a numeric or interval value expression that determines the values to average.
PAGE 332
--- 1 row(s) selected. • Return the average salary by department: SELECT deptnum, AVG (salary) AS "AVERAGE SALARY" FROM persnl.employee WHERE deptnum < 3000 GROUP BY deptnum; Dept/Num -------1000 2000 1500 2500 "AVERAGE SALARY" --------------------52000.17 50000.10 41250.00 37000.00 --- 4 row(s) selected.
PAGE 333
BITAND Function The BITAND function performs an AND operation on corresponding bits of the two operands. If both bits are 1, the result bit is 1. Otherwise the result bit is 0. BITAND (expression, expression) expression The result data type is a binary number. Depending on the precision of the operands, the data type of the result can either be an INT (32-bit integer) or a LARGEINT (64-bit integer).
PAGE 334
CASE (Conditional) Expression • • “Considerations for CASE” “Examples of CASE” The CASE expression is a conditional expression with two forms: simple and searched. In a simple CASE expression, Neoview SQL compares a value to a sequence of values and sets the CASE expression to the value associated with the first match—if there is a match. If there is no match, Neoview SQL returns the value specified in the ELSE clause (which can be null).
PAGE 335
CASE expression returns the value expression specified in the ELSE clause, or NULL if the ELSE value is not specified. Considerations for CASE Data Type of the CASE Expression The data type of the result of the CASE expression depends on the data types of the result expressions. If the results all have the same data type, the CASE expression adopts that data type. If the results have comparable but not identical data types, the CASE expression adopts the data type of the union of the result expressions.
PAGE 336
WHEN 500 THEN 'ACCOUNTANT' WHEN 600 THEN 'ADMINISTRATOR ANALYST' WHEN 900 THEN 'SECRETARY' ELSE NULL END FROM persnl.employee; LAST_NAME -------------------- FIRST_NAME --------------- (EXPR) ----------------- GREEN HOWARD RAYMOND ... CHOU CONRAD HERMAN CLARK HALL ... ROGER JERRY JANE MANAGER MANAGER MANAGER JOHN MANFRED JIM LARRY KATHRYN SECRETARY PROGRAMMER SALESREP ACCOUNTANT SYSTEM ANALYST --- 62 row(s) selected.
PAGE 337
CAST Expression • • • “Considerations for CAST” “Valid Conversions for CAST ” “Examples of CAST” The CAST expression converts data to the data type you specify. CAST ({expression | NULL} AS data-type) expression | NULL specifies the operand to convert to the data type data-type. If the operand is an expression, then data-type depends on the data type of expression and follows the rules outlined in “Valid Conversions for CAST ”.
PAGE 338
CAST (123.956 as INTERVAL DAY(18)) • This example returns the difference of two timestamps in minutes: CAST((d.step_end - d.step_start) AS INTERVAL MINUTE) • Suppose that your database includes a log file of user information. This example converts the current timestamp to a character string and concatenates the result to a character literal. Note the length must be specified. INSERT INTO stats.
PAGE 339
CEILING Function The CEILING function returns the smallest integer, represented as a FLOAT data type, greater than or equal to a numeric value expression. CEILING is a Neoview SQL extension. CEILING (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the CEILING function. See “Numeric Value Expressions” (page 250). Example of CEILING This function returns the integer value 3.
PAGE 340
CHAR Function The CHAR function returns the character that has the specified code value, which must be of exact numeric with scale 0. CHAR is a Neoview SQL extension. CHAR(code-value, [,char-set-name]) code-value is a valid code value in the character set in use. char-set-name can be ISO88591, UCS2, SJIS (for the SJIS configuration only), or UTF8 (for the Unicode configuration only). The returned character will be associated with the character set specified by char-set-name with the DEFAULT collation.
PAGE 341
CHAR_LENGTH Function The CHAR_LENGTH function returns the number of characters in a string. You can also use CHARACTER_LENGTH. Every character, including multibyte characters, counts as one character. CHAR[ACTER]_LENGTH (string-value-expression) string-value-expression specifies the string value expression for which to return the length in characters. Neoview SQL returns the result as a two-byte signed integer with a scale of zero. If string-value-expression is null, Neoview SQL returns a length of null.
PAGE 342
COALESCE Function The COALESCE function returns the value of the first expression in the list that does not have a NULL value or if all the expressions have NULL values, the function returns a NULL value. COALESCE (expr1, expr2, ...) expr1 an expression to be compared. expr2 an expression to be compared.
PAGE 343
CODE_VALUE Function The CODE_VALUE function returns an unsigned integer (INTEGER UNSIGNED) that is the code point of the first character in a character value expression that can be associated with one of the supported character sets. See . CODE_VALUE is a Neoview SQL extension. CODE_VALUE(character-value-expression) _character-set character-value-expression is a character string.
PAGE 344
CONCAT Function The CONCAT function returns the concatenation of two character value expressions as a character string value. You can also use the concatenation operator (||). CONCAT is a Neoview SQL extension. CONCAT (character-expr-1, character-expr-2) character-expr-1, character-expr-2 are SQL character value expressions (of data type CHAR or VARCHAR) that specify two strings of characters. Both character value expressions must be either ISO8859-1 character expressions or UCS2 character expressions.
PAGE 345
INSERT INTO stats.logfile (user_key, user_info) VALUES (002, 'Executed at ' || CAST (CURRENT_TIMESTAMP AS CHAR(26))); CONVERTTOHEX Function The CONVERTTOHEX function converts the specified value expression to hexadecimal for display purposes. CONVERTTOHEX is a Neoview SQL extension. CONVERTTOHEX (expression) expression is any numeric, character, datetime, or interval expression. The primary purpose of the CONVERTTOHEX function is to eliminate any doubt as to the exact value in a column.
PAGE 346
----------07D8040F • --------0E201E ----------------------------07D8040F0E201E00000035 --------------------------07D8040F0E201E00081ABB Display the internal values for an INTERVAL YEAR column, an INTERVAL YEAR(2) TO MONTH column, and an INTERVAL DAY TO SECOND column: CREATE TABLE IVT ( IV1 interval year, IV2 interval year(2) to month, IV3 interval day to second) no partition; INSERT INTO IVT values( interval '1' year, interval '3-2' year(2) to month, interval '31:14:59:58' day to second); SELECT CONVER
PAGE 347
CONVERTTIMESTAMP Function The CONVERTTIMESTAMP function converts a Julian timestamp to a value with data type TIMESTAMP. CONVERTTIMESTAMP is a Neoview SQL extension. CONVERTTIMESTAMP (julian-timestamp) julian-timestamp is an expression that evaluates to a Julian timestamp, which is a LARGEINT value. Considerations for CONVERTTIMESTAMP The julian-timestamp value must be in the range from 148731163200000000 to 274927348799999999.
PAGE 348
COS Function The COS function returns the cosine of a numeric value expression, where the expression is an angle expressed in radians. COS is a Neoview SQL extension. COS (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the COS function. See “Numeric Value Expressions” (page 250). Example of COS This function returns the value 9.39680940386503680E-001, or approximately 0.9397, the cosine of 0.3491 (which is 20 degrees): COS (0.
PAGE 349
COSH Function The COSH function returns the hyperbolic cosine of a numeric value expression, where the expression is an angle expressed in radians. COSH is a Neoview SQL extension. COSH (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the COSH function. See “Numeric Value Expressions” (page 250). Example of COSH This function returns the value 1.88842387716101568E+000, or approximately 1.8884, the hyperbolic cosine of 1.
PAGE 350
COUNT Function The COUNT function counts the number of rows that result from a query or the number of rows that contain a distinct value in a specific column. The result of COUNT is data type LARGEINT. The result can never be NULL. COUNT {(*) | ([ALL | DISTINCT] expression)} COUNT (*) returns the number of rows in the table specified in the FROM clause of the SELECT statement that contains COUNT (*). If the result table is empty (that is, no rows are returned by the query) COUNT (*) returns zero.
PAGE 351
----------56 --- 1 row(s) selected. SELECT COUNT(*) FROM persnl.employee WHERE jobcode IS NOT NULL; (EXPR) ----------56 --- 1 row(s) selected. • Count the number of distinct departments in the EMPLOYEE table: SELECT COUNT (DISTINCT deptnum) FROM persnl.employee; (EXPR) ----------11 --- 1 row(s) selected.
PAGE 352
CURRENT Function The CURRENT function returns a value of type TIMESTAMP based on the current local date and time. The function is evaluated once when the query starts execution and is not reevaluated (even if it is a long running query). You can also use “CURRENT_TIMESTAMP Function” (page 356). CURRENT [(precision)] precision is an integer value in the range 0 to 6 that specifies the precision of (the number of decimal places in) the fractional seconds in the returned value. The default is 6.
PAGE 353
CURRENT_DATE Function The CURRENT_DATE function returns the local current date as a value of type DATE. The function is evaluated once when the query starts execution and is not reevaluated (even if it is a long running query). CURRENT_DATE The CURRENT_DATE function returns the current date, such as 2008-09-28. The value returned is a value of type DATE, not a string value. Examples of CURRENT_DATE • Select rows from the ORDERS table based on the current date: SELECT * FROM sales.
PAGE 354
CURRENT_ROLE Function The CURRENT_ROLE function returns the role (ROLE.name) that you logged in as. This function can be used anywhere in the query. The values returned are string datatype VARCHAR(64). CURRENT_ROLE Considerations for CURRENT_ROLE The CURRENT_ROLE function returns the role name in ISO8859-1 encoding on a Neoview platform that has the ISO88591 or SJIS configuration. It returns the role name in UCS2 encoding on a Neoview platform that has the Unicode configuration.
PAGE 355
CURRENT_TIME Function The CURRENT_TIME function returns the current local time as a value of type TIME. The function is evaluated once when the query starts execution and is not reevaluated (even if it is a long running query). CURRENT_TIME [(precision)] precision is an integer value in the range 0 to 6 that specifies the precision of (the number of decimal places in) the fractional seconds in the returned value. The default is 0.
PAGE 356
CURRENT_TIMESTAMP Function The CURRENT_TIMESTAMP function returns a value of type TIMESTAMP based on the current local date and time. The function is evaluated once when the query starts execution and is not reevaluated (even if it is a long running query). You can also use the “CURRENT Function” (page 352). CURRENT_TIMESTAMP [(precision)] precision is an integer value in the range 0 to 6 that specifies the precision of (the number of decimal places in) the fractional seconds in the returned value.
PAGE 357
CURRENT_USER Function The CURRENT_USER function returns the user name that you logged in as. If you are logged in as the role, the role is returned. This function can be used anywhere in the query. The values returned are string datatype VARCHAR(64). CURRENT_USER The CURRENT_USER function is equivalent to the “USER Function” (page 474). Considerations for CURRENT_USER The CURRENT_USER function returns the user name in ISO8859-1 encoding on a Neoview platform that has the ISO88591 or SJIS configuration.
PAGE 358
DATE_ADD Function The DATE_ADD function adds the interval specified by interval_expression to datetime_expr. If the specified interval is in years or months, DATE_ADD normalizes the result. See “Standard Normalization” (page 317). The type of the datetime_expr is returned, unless the interval_expression contains any time components, then a timestamp is returned. DATE_ADD is a Neoview extension.
PAGE 359
DATE_SUB Function The DATE_SUB function subtracts the specified interval_expression from datetime_expr. If the specified interval is in years or months, DATE_SUB normalizes the result. See “Standard Normalization” (page 317). The type of the datetime_expr is returned, unless the interval_expression contains any time components, then a timestamp is returned. DATE_SUB is a Neoview extension.
PAGE 360
DATEADD Function The DATEADD function adds the interval of time specified by datepart and num_expr to datetime_expr. If the specified interval is in years or months, DATEADD normalizes the result. See “Standard Normalization” (page 317). The type of the datetime_expr is returned, unless the interval expression contains any time components, then a timestamp is returned. DATEADD is a Neoview extension.
PAGE 361
DATEDIFF Function The DATEDIFF function returns the integer value for the number of datepart units of time between startdate and enddate. If enddate precedes startdate, the return value is negative or zero. DATEDIFF is a Neoview extension.
PAGE 362
DATEFORMAT Function The DATEFORMAT function returns a datetime value as a character string literal in the DEFAULT, USA, or EUROPEAN format. The data type of the result is CHAR. DATEFORMAT is a Neoview SQL extension. DATEFORMAT (datetime-expression,{DEFAULT | USA | EUROPEAN}) datetime-expression is an expression that evaluates to a datetime value of type DATE, TIME, or TIMESTAMP. See “Datetime Value Expressions” (page 242). DEFAULT | USA | EUROPEAN specifies a format for a datetime value.
PAGE 363
DATE_PART Function (of an Interval) The DATE_PART function extracts the datetime field specified by text from the interval value specified by interval and returns the result as an exact numeric value. The DATE_PART function accepts the specification of 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', or 'SECOND' for text. DATE_PART is a Neoview extension. DATE_PART (text, interval) text specifies YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. The value must be enclosed in single quotes.
PAGE 364
DATE_PART Function (of a Timestamp) The DATE_PART function extracts the datetime field specified by text from the datetime value specified by datetime_expr and returns the result as an exact numeric value. The DATE_PART function accepts the specification of 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', or 'SECOND' for text. The DATE_PART function of a timestamp can be changed to DATE_PART function of a datetime because the second argument can be either a timestamp or a date expression.
PAGE 365
DATE_TRUNC Function The DATE_TRUNC function returns a value of type TIMESTAMP, which has all fields of lesser precision than text set to zero (or 1 in the case of months or days). DATE_TRUNC is a Neoview extension. DATE_TRUNC(text, datetime_expr) text specifies 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', or 'SECOND'. The DATE_TRUNC function also accepts the specification of 'CENTURY' or 'DECADE'. datetime_expr is an expression that evaluates to a datetime value of type DATE or TIMESTAMP.
PAGE 366
DAY Function The DAY function converts a DATE or TIMESTAMP expression into an INTEGER value in the range 1 through 31 that represents the corresponding day of the month. The result returned by the DAY function is equal to the result returned by the DAYOFMONTH function. DAY is a Neoview SQL extension. DAY (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 367
DAYNAME Function The DAYNAME function converts a DATE or TIMESTAMP expression into a character literal that is the name of the day of the week (Sunday, Monday, and so on). DAYNAME is a Neoview SQL extension. DAYNAME (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 368
DAYOFMONTH Function The DAYOFMONTH function converts a DATE or TIMESTAMP expression into an INTEGER value in the range 1 through 31 that represents the corresponding day of the month. The result returned by the DAYOFMONTH function is equal to the result returned by the DAY function. DAYOFMONTH is a Neoview SQL extension. DAYOFMONTH (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 369
DAYOFWEEK Function The DAYOFWEEK function converts a DATE or TIMESTAMP expression into an INTEGER value in the range 1 through 7 that represents the corresponding day of the week. The value 1 represents Sunday, 2 represents Monday, and so forth. DAYOFWEEK is a Neoview SQL extension. DAYOFWEEK (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 370
DAYOFYEAR Function The DAYOFYEAR function converts a DATE or TIMESTAMP expression into an INTEGER value in the range 1 through 366 that represents the corresponding day of the year. DAYOFYEAR is a Neoview SQL extension. DAYOFYEAR (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 371
DECODE Function The DECODE function compares expr to each test_expr value one by one in the order provided. If expr is equal to a test_expr, then the corresponding retval is returned. If no match is found, default is returned. If no match is found and default is omitted, NULL is returned. DECODE is a Neoview extension. DECODE (expr, test_expr, retval [, test_expr2, retval2 ... ] [ , default ] ) expr is an SQL expression. test_expr, test_expr,..
PAGE 372
If all the possible return values are of numeric types, none are NUMERIC, REAL, FLOAT, or DOUBLE PRECISION, and at least one is of type DECIMAL, then the returned value will be of type DECIMAL. If the returned value is of type NUMERIC or DECIMAL, it has a precision equal to the sum of: • • the maximum scale of all the possible return value types and the maximum value of (precision - scale) for all the possible return value types. However, the precision will not exceed 18.
PAGE 373
When ‘Hi’ is encountered in a ret-val position, the error is produced because the CHAR(2) type argument is not comparable with a NUMERIC(18,6) type return value.
PAGE 374
DEGREES Function The DEGREES function converts a numeric value expression expressed in radians to the number of degrees. DEGREES is a Neoview SQL extension. DEGREES (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the DEGREES function. See “Numeric Value Expressions” (page 250). Examples of DEGREES • This function returns the value 45.0001059971939008 in degrees: DEGREES (0.78540) • This function returns the value of 45.
PAGE 375
DIFF1 Function • • “Considerations for DIFF1” “Examples of DIFF1” The DIFF1 function is a sequence function that calculates the amount of change in an expression from row to row in an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). DIFF1 is a Neoview SQL extension. DIFF1 (column-expression-a [,column-expression-b]) column-expression-a specifies a derived column determined by the evaluation of the column expression.
PAGE 376
--- 5 row(s) selected. Note that the first row retrieved displays null because the offset from the current row does not fall within the results set. • Retrieve the difference between the TS column in the current row and the TS column in the previous row: SELECT DIFF1 (TS) AS DIFF1_TS FROM mining.seqfcn SEQUENCE BY TS; DIFF1_TS -------------------? 30002620.000000 134157861.000000 168588029.000000 114055223.000000 --- 5 row(s) selected. Note that the results are expressed as the number of seconds.
PAGE 377
DIFF2 Function • • “Considerations for DIFF2” “Examples of DIFF2” The DIFF2 function is a sequence function that calculates the amount of change in a DIFF1 value from row to row in an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). DIFF2 is a Neoview SQL extension. DIFF2 (column-expression-a [,column-expression-b]) column-expression-a specifies a derived column determined by the evaluation of the column expression.
PAGE 378
Note that the results are equal to the difference of DIFF1(I1) for the current row and DIFF1(I1) of the previous row. For example, in the third row of the output of this example, -31075 is equal to -9116 minus 21959. The value -9116 is the result of DIFF1(I1) for the current row, and the value 21959 is the result of DIFF1(I1) for the previous row. See “Examples of DIFF1” (page 375).
PAGE 379
EXP Function This function returns the exponential value (to the base e) of a numeric value expression. EXP is a Neoview SQL extension. EXP (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the EXP function. See “Numeric Value Expressions” (page 250). The minimum input value must be between -744.4400719 and -744.4400720. The maximum input value must be between 709.78271289338404 and 709.78271289338405.
PAGE 380
EXPLAIN Function “Considerations for EXPLAIN Function” “Examples of EXPLAIN Function” The EXPLAIN function is a table-valued stored function that generates a result table describing an access plan for a SELECT, INSERT, DELETE, UPDATE, or CALL statement. See “Result of the EXPLAIN Function” (page 380). The EXPLAIN function can be specified as a table reference (table) in the FROM clause of a SELECT statement if it is preceded by the keyword TABLE and surrounded by parentheses.
PAGE 381
A node of an operator tree is a point in the tree that represents an event (involving an operator) in a plan. Each node might have subordinate nodes—that is, each event might generate a subordinate event or events in the plan. Column Name Data Type Description MODULE_NAME CHAR(60) Reserved for future use. STATEMENT_ NAME CHAR(60) Statement name; truncated on the right if longer than 60 characters.
PAGE 382
IDLETIME An estimate of the number of seconds to wait for an event to happen. The estimate includes the amount of time to open a table or start an ESP process. PROBES The number of times the operator will be executed. Usually, this value is 1, but it can be greater when you have, for example, an inner scan of a nested-loop join.
PAGE 383
EXTRACT Function The EXTRACT function extracts a datetime field from a datetime or interval value expression. It returns an exact numeric value. EXTRACT (datetime-field FROM extract-source) datetime-field is: YEAR | MONTH | DAY | HOUR | MINUTE | SECOND extract-source is: datetime-expression | interval-expression See “Datetime Value Expressions” (page 242) and “Interval Value Expressions” (page 246).
PAGE 384
FLOOR Function The FLOOR function returns the largest integer, represented as a FLOAT data type, less than or equal to a numeric value expression. FLOOR is a Neoview SQL extension. FLOOR (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the FLOOR function. See “Numeric Value Expressions” (page 250). Examples of FLOOR This function returns the integer value 2.00000000000000000E+000, represented as a FLOAT data type: FLOOR (2.
PAGE 385
HOUR Function The HOUR function converts a TIME or TIMESTAMP expression into an INTEGER value in the range 0 through 23 that represents the corresponding hour of the day. HOUR is a Neoview SQL extension. HOUR (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type TIME or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 386
INSERT Function The INSERT function returns a character string where a specified number of characters within the character string has been deleted, beginning at a specified start position, and where another character string has been inserted at the start position. Every character, including multibyte characters, is treated as one character. INSERT is a Neoview SQL extension.
PAGE 387
ISNULL Function The ISNULL function returns the value of the first argument if it is not null, otherwise it returns the value of the second argument. Both expressions must be of comparable types. ISNULL is a Neoview extension. ISNULL(ck_expr, repl_value) ck_expr an expression of any valid SQL data type. repl_value an expression of any valid SQL data type, but must be a comparable type with that of ck_expr. Examples of ISNULL • This function returns a 0 instead of a null if value is null.
PAGE 388
JULIANTIMESTAMP Function The JULIANTIMESTAMP function converts a datetime value into a 64-bit Julian timestamp value that represents the number of microseconds that have elapsed between 4713 B.C., January 1, 00:00, and the specified datetime value. JULIANTIMESTAMP returns a value of data type LARGEINT. The function is evaluated once when the query starts execution and is not reevaluated (even if it is a long running query). JULIANTIMESTAMP is a Neoview SQL extension.
PAGE 389
LASTNOTNULL Function The LASTNOTNULL function is a sequence function that returns the last nonnull value of a column in an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). LASTNOTNULL is a Neoview SQL extension. LASTNOTNULL (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression. If only null values have been returned, LASTNOTNULL returns null.
PAGE 390
LCASE Function The LCASE function downshifts alphanumeric characters. For non-alphanumeric characters, LCASE returns the same character. LCASE can appear anywhere in a query where a value can be used, such as in a select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE predicate, an expression, or as qualifying a new value in an UPDATE or INSERT statement. The result returned by the LCASE function is equal to the result returned by the “LOWER Function” (page 395).
PAGE 391
LEFT Function The LEFT function returns the leftmost specified number of characters from a character expression. Every character, including multibyte characters, is treated as one character. LEFT is a Neoview SQL extension. LEFT (character-expr, count) character-expr specifies the source string from which to return the leftmost specified number of characters. The source string is an SQL character value expression. The operand is the result of evaluating character-expr.
PAGE 392
LOCATE Function The LOCATE function searches for a given substring in a character string. If the substring is found, Neoview SQL returns the character position of the substring within the string. Every character, including multibyte characters, is treated as one character. The result returned by the LOCATE function is equal to the result returned by the “POSITION Function” (page 423). LOCATE is a Neoview SQL extension.
PAGE 393
LOG Function The LOG function returns the natural logarithm of a numeric value expression. LOG is a Neoview SQL extension. LOG (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the LOG function. The value of the argument must be greater than zero. See “Numeric Value Expressions” (page 250). Example of LOG This function returns the value 6.93147180559945344E-001, or approximately 0.69315: LOG (2.
PAGE 394
LOG10 Function The LOG10 function returns the base 10 logarithm of a numeric value expression. LOG10 is a Neoview SQL extension. LOG10 (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the LOG10 function. The value of the argument must be greater than zero. See “Numeric Value Expressions” (page 250). Example of LOG10 This function returns the value 1.39794000867203776E+000, or approximately 1.
PAGE 395
LOWER Function • • “Considerations for LOWER” “Example of LOWER” The LOWER function downshifts alphanumeric characters. For non-alphanumeric characters, LOWER returns the same character. LOWER can appear anywhere in a query where a value can be used, such as in a select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE predicate, an expression, or as qualifying a new value in an UPDATE or INSERT statement.
PAGE 396
Table 6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) 396 x L (x) x L(x) x L(x) x L(x) x L(x) x L(x) 0050 0070 0194 0263 040F 045F 0545 0575 1E7A 1E7B 1F9A 1F92 0051 0071 0196 0269 0410 0430 0546 0576 1E7C 1E7D 1F9B 1F93 0052 0072 0197 0268 0411 0431 0547 0577 1E7E 1E7F 1F9C 1F94 0053 0073 0198 0199 0412 0432 0548 0578 1E80 1E81 1F9D 1F95 0054 0074 019C 026F 0413 0433 0549 0579 1E82 1E83 1F9E 1F96 0055 0075
PAGE 397
Table 6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) x L (x) x L(x) x L(x) x L(x) x L(x) x L(x) 00DA 00FA 01DB 01DC 0466 0467 10B2 10E2 1ECC 1ECD 1FFB 1F7D 00DB 00FB 01DE 01DF 0468 0469 10B3 10E3 1ECE 1ECF 1FFC 1FF3 00DC 00FC 01E0 01E1 046A 046B 10B4 10E4 1ED0 1ED1 2160 2170 00DD 00FD 01E2 01E3 046C 046D 10B5 10E5 1ED2 1ED3 2161 2171 00DE 00FE 01E4 01E5 046E 046F 10B6 10E6 1ED4 1ED5 2162 2172 0100 0101 01E6
PAGE 398
Table 6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) 398 x L (x) x L(x) x L(x) x L(x) x L(x) x L(x) 013F 0140 0392 03B2 04BC 04BD 1E20 1E21 1F1D 1F15 24C8 24E2 0141 0142 0393 03B3 04BE 04BF 1E22 1E23 1F28 1F20 24C9 24E3 0143 0144 0394 03B4 04C1 04C2 1E24 1E25 1F29 1F21 24CA 24E4 0145 0146 0395 03B5 04C3 04C4 1E26 1E27 1F2A 1F22 24CB 24E5 0147 0148 0396 03B6 04C7 04C8 1E28 1E29 1F2B 1F23 24CC 24E6 014A 014B
PAGE 399
Example of LOWER Suppose that your CUSTOMER table includes an entry for Hotel Oregon. Select the column CUSTNAME and return the result in uppercase and lowercase letters by using the UPPER and LOWER functions: SELECT custname,UPPER(custname),LOWER(custname) FROM sales.customer; (EXPR) ----------------... Hotel Oregon (EXPR) ------------------... HOTEL OREGON (EXPR) -----------------... hotel oregon --- 17 row(s) selected. See “UPPER Function” (page 472).
PAGE 400
LPAD Function The LPAD function pads the left side of a string with the specified string. Every character in the string, including multibyte characters, is treated as one character. LPAD is a Neoview extension. LPAD (str, len [,padstr]) str can be an expression. See “Character Value Expressions” (page 240). len identifies the desired number of characters to be returned and can be an expression but must be an integral value. If len is equal to the length of the string, no change is made.
PAGE 401
LTRIM Function The LTRIM function removes leading spaces from a character string. If you need to remove any leading character other than space, use the TRIM function and specify the value of the character. See the“TRIM Function” (page 463). LTRIM is a Neoview SQL extension. LTRIM (character-expression) character-expression is an SQL character value expression and specifies the string from which to trim leading spaces. See “Character Value Expressions” (page 240).
PAGE 402
MAX/MAXIMUM Function MAX is an aggregate function that returns the maximum value within a set of values. MAXIMUM is the equivalent of MAX wherever the function name MAX appears within a statement. The data type of the result is the same as the data type of the argument. MAX | MAXIMUM ([ALL | DISTINCT] expression) ALL | DISTINCT specifies whether duplicate values are included in the computation of the maximum of the expression. The default option is ALL, which causes duplicate values to be included.
PAGE 403
MIN Function MIN is an aggregate function that returns the minimum value within a set of values. The data type of the result is the same as the data type of the argument. MIN ([ALL | DISTINCT] expression) ALL | DISTINCT specifies whether duplicate values are included in the computation of the minimum of the expression. The default option is ALL, which causes duplicate values to be included. If you specify DISTINCT, duplicate values are eliminated before the MIN function is applied.
PAGE 404
MINUTE Function The MINUTE function converts a TIME or TIMESTAMP expression into an INTEGER value, in the range 0 through 59, that represents the corresponding minute of the hour. MINUTE is a Neoview SQL extension. MINUTE (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type TIME or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 405
MOD Function The MOD function returns the remainder (modulus) of an integer value expression divided by an integer value expression. MOD is a Neoview SQL extension. MOD (integer-expression-1,integer-expression-2) integer-expression-1 is an SQL numeric value expression of data type SMALLINT, INTEGER, or LARGEINT that specifies the value for the dividend argument of the MOD function.
PAGE 406
MONTH Function The MONTH function converts a DATE or TIMESTAMP expression into an INTEGER value in the range 1 through 12 that represents the corresponding month of the year. MONTH is a Neoview SQL extension. MONTH (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 407
MONTHNAME Function The MONTHNAME function converts a DATE or TIMESTAMP expression into a character literal that is the name of the month of the year (January, February, and so on). MONTHNAME is a Neoview SQL extension. MONTHNAME (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 408
MOVINGAVG Function The MOVINGAVG function is a sequence function that returns the average of nonnull values of a column in the current window of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). MOVINGAVG is a Neoview SQL extension. MOVINGAVG (column-expression,integer-expression [,max-rows]) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 409
MOVINGCOUNT Function The MOVINGCOUNT function is a sequence function that returns the number of nonnull values of a column in the current window of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). MOVINGCOUNT is a Neoview SQL extension. MOVINGCOUNT (column-expression,integer-expression [,max-rows]) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 410
MOVINGMAX Function The MOVINGMAX function is a sequence function that returns the maximum of nonnull values of a column in the current window of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). MOVINGMAX is a Neoview SQL extension. MOVINGMAX (column-expression,integer-expression [,max-rows]) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 411
MOVINGMIN Function The MOVINGMIN function is a sequence function that returns the minimum of nonnull values of a column in the current window of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). MOVINGMIN is a Neoview SQL extension. MOVINGMIN (column-expression,integer-expression [,max-rows]) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 412
MOVINGSTDDEV Function The MOVINGSTDDEV function is a sequence function that returns the standard deviation of nonnull values of a column in the current window of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). MOVINGSTDDEV is a Neoview SQL extension. MOVINGSTDDEV (column-expression,integer-expression [,max-rows]) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 413
-------------------.000 15527.357 14802.016 15115.012 6036.275 --- 5 row(s) selected.
PAGE 414
MOVINGSUM Function The MOVINGSUM function is a sequence function that returns the sum of nonnull values of a column in the current window of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). MOVINGSUM is a Neoview SQL extension. MOVINGSUM (column-expression,integer-expression [,max-rows]) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 415
MOVINGVARIANCE Function The MOVINGVARIANCE function is a sequence function that returns the variance of nonnull values of a column in the current window of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). MOVINGVARIANCE is a Neoview SQL extension. MOVINGVARIANCE (column-expression,integer-expression [,max-rows]) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 416
.000 241098840.500 219099697.000 228463602.333 36436621.000 --- 5 row(s) selected.
PAGE 417
NULLIF Function The NULLIF function compares the value of two expressions. Both expressions must be of comparable types. The return value is NULL when the two expressions are equal. Otherwise, the return value is the value of the first expression. NULLIF(expr1, expr2) expr1 an expression to be compared. expr2 an expression to be compared. The NULLIF(expr1, expr2) is equivalent to: CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END NULLIF returns a NULL if both arguments are equal.
PAGE 418
NULLIFZERO Function The NULLIFZERO function returns the value of the expression if that value is not zero. It returns NULL if the value of the expression is zero. NULLIFZERO (expression) expression specifies a value expression. It must be a numeric data type. Examples of NULLIFZERO • This function returns the value of the column named salary for each row where the column’s value is not zero. It returns a NULL for each row where the column’s value is zero .
PAGE 419
NVL Function The NVL function determines if the selected column has a null value and then returns the new-operand value; otherwise the operand value is returned. NVL (operand, ) operand specifies a value expression. new-operand specifies a value expression. operand and new-operand must be comparable data types. If operand is a null value, NVL returns new-operand. If operand is not a null value, NVL returns operand.
PAGE 420
OCTET_LENGTH Function The OCTET_LENGTH function returns the length of a character string in bytes. OCTET_LENGTH (string-value-expression) string-value-expression specifies the string value expression for which to return the length in bytes. Neoview SQL returns the result as a 2-byte signed integer with a scale of zero. If string-value-expression is null, Neoview SQL returns a length of zero. See “Character Value Expressions” (page 240).
PAGE 421
OFFSET Function The OFFSET function is a sequence function that retrieves columns from previous rows of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). OFFSET is a Neoview SQL extension. OFFSET (column-expression,number-rows [,max-rows]) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 422
PI Function The PI function returns the constant value of pi as a floating-point value. PI is a Neoview SQL extension. PI() Example of PI This constant function returns the value 3.
PAGE 423
POSITION Function The POSITION function searches for a given substring in a character string. If the substring is found, Neoview SQL returns the character position of the substring within the string. Every character, including multibyte characters, is treated as one character. The result returned by the POSITION function is equal to the result returned by the “LOCATE Function” (page 392).
PAGE 424
POWER Function The POWER function returns the value of a numeric value expression raised to the power of an integer value expression. You can also use the exponential operator **. POWER is a Neoview SQL extension. POWER (numeric-expression-1,numeric-expression-2) numeric-expression-1, numeric-expression-2 are SQL numeric value expressions that specify the values for the base and exponent arguments of the POWER function. See “Numeric Value Expressions” (page 250).
PAGE 425
QUARTER Function The QUARTER function converts a DATE or TIMESTAMP expression into an INTEGER value in the range 1 through 4 that represents the corresponding quarter of the year. Quarter 1 represents January 1 through March 31, and so on. QUARTER is a Neoview SQL extension. QUARTER (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 426
RADIANS Function The RADIANS function converts a numeric value expression expressed in degrees to the number of radians. RADIANS is a Neoview SQL extension. RADIANS (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the RADIANS function. See “Numeric Value Expressions” (page 250). Examples of RADIANS • Return the value 7.85398150000000000E-001, or approximately 0.78540 in degrees: RADIANS (45) • Return the value 45 in degrees.
PAGE 427
RANK/RUNNINGRANK Function The RUNNINGRANK function is a sequence function that returns the rank of the given value of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. RANK is an alternative syntax for RUNNINGRANK. RUNNINGRANK is a Neoview extension. RUNNINGRANK(expression) | RANK(expression) expression specifies the expression on which to perform the rank. RUNNINGRANK returns the rank of the expression within the intermediate result table.
PAGE 428
Return the rank of I1: SELECT I1, RUNNINGRANK (I1) AS RANK FROM cat.sch.seqfcn SEQUENCE BY I1; I1 ----------1 2 3 4 5 6 8 10 RANK -------------------1 2 3 4 5 6 7 8 --- 8 row(s) selected. Return the rank of I1 descending: SELECT I1, RUNNINGRANK (I1) AS RANK FROM cat.sch.seqfcn SEQUENCE BY I1 DESC; I1 RANK ----------- -------------------- 10 8 6 5 4 3 2 1 1 2 3 4 5 6 7 8 --- 8 row(s) selected. Return the rank of I2, using the alternative RANK syntax: SELECT I2, RANK (I2) AS RANK FROM cat.sch.
PAGE 429
I2 ----------? ? 300 200 200 200 100 100 RANK -------------------1 1 3 4 4 4 7 7 --- 8 row(s) selected. Return the rank of I2 descending, excluding NULL values: SELECT I2, RANK (I2) AS RANK FROM cat.sch.seqfcn WHERE I2 IS NOT NULL SEQUENCE BY I2 DESC; I2 RANK ----------- -------------------300 1 200 2 200 2 200 2 100 5 100 5 --- 6 row(s) selected.
PAGE 430
REPEAT Function The REPEAT function returns a character string composed of the evaluation of a character expression repeated a specified number of times. REPEAT is a Neoview SQL extension. REPEAT (character-expr, count) character-expr specifies the source string from which to return the specified number of repeated strings. The source string is an SQL character value expression. The operand is the result of evaluating character-expr. See “Character Value Expressions” (page 240).
PAGE 431
REPLACE Function The REPLACE function returns a character string where all occurrences of a specified character string in the original string are replaced with another character string. All three character value expressions must be comparable types and must use the same character set. The return value is the VARCHAR type. REPLACE is a Neoview SQL extension. REPLACE (char-expr-1, char-expr-2, char-expr-3) char-expr-1, char-expr-2, char-expr-3 are SQL character value expressions.
PAGE 432
RIGHT Function The RIGHT function returns the rightmost specified number of characters from a character expression. Every character, including multibyte characters, is treated as one character. RIGHT is a Neoview SQL extension. RIGHT (character-expr, count) character-expr specifies the source string from which to return the rightmost specified number of characters. The source string is an SQL character value expression. The operand is the result of evaluating character-expr.
PAGE 433
ROUND Function The ROUND function returns the value of numeric_expr rounded to num places to the right of the decimal point. ROUND is a Neoview extension. ROUND(numeric_expr [ , num ] ) numeric_expr is an SQL numeric value expression. num specifies the number of places to the right of the decimal point for rounding. If num is a negative number, all places to the right of the decimal point and num places to the left of the decimal point are zeroed.
PAGE 434
ROWS SINCE Function The ROWS SINCE function is a sequence function that returns the number of rows counted since the specified condition was last true in the intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). ROWS SINCE is a Neoview SQL extension. ROWS SINCE [INCLUSIVE] (condition [,max-rows]) INCLUSIVE specifies the current row is to be considered. If you specify INCLUSIVE, the condition is evaluated in the current row.
PAGE 435
(EXPR) --------------? 1 2 1 2 (EXPR) --------------0 1 0 1 0 --- 5 row(s) selected.
PAGE 436
ROWS SINCE CHANGED Function The ROWS SINCE CHANGED function is a sequence function that returns the number of rows counted since the specified set of values last changed in the intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). ROWS SINCE CHANGED is a Neoview SQL extension.
PAGE 437
RPAD Function The RPAD function pads the right side of a string with the specified string. Every character in the string, including multibyte characters, is treated as one character. RPAD is a Neoview extension. RPAD (str, len [,padstr]) str can be an expression. See “Character Value Expressions” (page 240). len identifies the desired number of characters to be returned and can be an expression but must be an integral value. If len is equal to the length of the string, no change is made.
PAGE 438
RTRIM Function The RTRIM function removes trailing spaces from a character string. If you need to remove any leading character other than space, use the TRIM function and specify the value of the character. See the “TRIM Function” (page 463). RTRIM is a Neoview SQL extension. RTRIM (character-expression) character-expression is an SQL character value expression and specifies the string from which to trim trailing spaces. See “Character Value Expressions” (page 240).
PAGE 439
RUNNINGAVG Function The RUNNINGAVG function is a sequence function that returns the average of nonnull values of a column up to and including the current row of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). RUNNINGAVG is a Neoview SQL extension. RUNNINGAVG (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 440
RUNNINGCOUNT Function The RUNNINGCOUNT function is a sequence function that returns the number of rows up to and including the current row of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). RUNNINGCOUNT is a Neoview SQL extension. RUNNINGCOUNT {(*) | (column-expression)} * as an argument causes RUNNINGCOUNT(*) to return the number of rows in the intermediate result table up to and including the current row.
PAGE 441
RUNNINGMAX Function The RUNNINGMAX function is a sequence function that returns the maximum of values of a column up to and including the current row of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). RUNNINGMAX is a Neoview SQL extension. RUNNINGMAX (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 442
RUNNINGMIN Function The RUNNINGMIN function is a sequence function that returns the minimum of values of a column up to and including the current row of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). RUNNINGMIN is a Neoview SQL extension. RUNNINGMIN (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 443
RUNNINGRANK Function See the “RANK/RUNNINGRANK Function” (page 427). RUNNINGSTDDEV Function The RUNNINGSTDDEV function is a sequence function that returns the standard deviation of nonnull values of a column up to and including the current row of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). RUNNINGSTDDEV is a Neoview SQL extension.
PAGE 444
RUNNINGSUM Function The RUNNINGSUM function is a sequence function that returns the sum of nonnull values of a column up to and including the current row of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). RUNNINGSUM is a Neoview SQL extension. RUNNINGSUM (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 445
RUNNINGVARIANCE Function The RUNNINGVARIANCE function is a sequence function that returns the variance of nonnull values of a column up to and including the current row of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 306). RUNNINGVARIANCE is a Neoview SQL extension. RUNNINGVARIANCE (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 446
SECOND Function The SECOND function converts a TIME or TIMESTAMP expression into an INTEGER value in the range 0 through 59 that represents the corresponding second of the hour. SECOND is a Neoview SQL extension. SECOND (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type TIME or TIMESTAMP. See “Datetime Value Expressions” (page 242).
PAGE 447
SIGN Function The SIGN function returns an indicator of the sign of a numeric value expression. If the value is less than zero, the function returns -1 as the indicator. If the value is zero, the function returns 0. If the value is greater than zero, the function returns 1. SIGN is a Neoview SQL extension. SIGN (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the SIGN function. See “Numeric Value Expressions” (page 250).
PAGE 448
SIN Function The SIN function returns the sine of a numeric value expression, where the expression is an angle expressed in radians. SIN is a Neoview SQL extension. SIN (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the SIN function. See “Numeric Value Expressions” (page 250). Example of SIN This function returns the value 3.42052233254419840E-001, or approximately 0.3420, the sine of 0.3491 (which is 20 degrees): SIN (0.
PAGE 449
SINH Function The SINH function returns the hyperbolic sine of a numeric value expression, where the expression is an angle expressed in radians. SINH is a Neoview SQL extension. SINH (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the SINH function. See “Numeric Value Expressions” (page 250). Example of SINH This function returns the value 1.60191908030082560E+000, or approximately 1.6019, the hyperbolic sine of 1.25: SINH (1.
PAGE 450
SPACE Function The SPACE function returns a character string consisting of a specified number of spaces, each of which is 0x20 or 0x0020, depending on the chosen character set. SPACE is a Neoview SQL extension. SPACE (length [,char-set-name]) length specifies the number of characters to be returned. The number count must be a value greater than or equal to zero of exact numeric data type and with a scale of zero.
PAGE 451
SQRT Function The SQRT function returns the square root of a numeric value expression. SQRT is a Neoview SQL extension. SQRT (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the SQRT function. The value of the argument must not be a negative number. See “Numeric Value Expressions” (page 250). Example of SQRT This function returns the value 5.19615242270663232E+000, or approximately 5.
PAGE 452
STDDEV Function • • “Considerations for STDDEV” “Examples of STDDEV” STDDEV is an aggregate function that returns the standard deviation of a set of numbers. STDDEV is a Neoview SQL extension. STDDEV ([ALL | DISTINCT] expression [,weight]) ALL | DISTINCT specifies whether duplicate values are included in the computation of the STDDEV of the expression. The default option is ALL, which causes duplicate values to be included.
PAGE 453
Examples of STDDEV • Compute the standard deviation of the salary of the current employees: SELECT STDDEV(salary) AS StdDev_Salary FROM persnl.employee; STDDEV_SALARY ------------------------3.57174062500000000E+004 --- 1 row(s) selected. • Compute the standard deviation of the cost of parts in the current inventory: SELECT STDDEV (price * qty_available) FROM sales.parts; (EXPR) ------------------------7.13899499999999808E+006 --- 1 row(s) selected.
PAGE 454
SUBSTRING/SUBSTR Function The SUBSTRING function extracts a substring out of a given character expression. It returns a character string of data type VARCHAR, with a maximum length equal to the smaller of these two: • • The fixed length of the input string (for CHAR-type strings) or the maximum variable length (for VARCHAR-type strings) The value of the length argument (when a constant is specified) or 32708 (when a non-constant is specified) SUBSTR is equivalent to SUBSTRING.
PAGE 455
Examples of SUBSTRING/SUBSTR • Extract 'Ro': SUBSTRING('Robert John Smith' FROM 0 FOR 3) SUBSTR('Robert John Smith' FROM 0 FOR 3) • Extract 'John': SUBSTRING ('Robert John Smith' FROM 8 FOR 4) SUBSTR ('Robert John Smith' FROM 8 FOR 4) • Extract 'John Smith': SUBSTRING ('Robert John Smith' FROM 8) SUBSTR ('Robert John Smith' FROM 8) • Extract 'Robert John Smith': SUBSTRING ('Robert John Smith' FROM 1 FOR 17) SUBSTR ('Robert John Smith' FROM 1 FOR 17) • Extract 'John Smith': SUBSTRING ('Robert John S
PAGE 456
SUM Function SUM is an aggregate function that returns the sum of a set of numbers. SUM ([ALL | DISTINCT] expression) ALL | DISTINCT specifies whether duplicate values are included in the computation of the SUM of the expression. The default option is ALL, which causes duplicate values to be included. If you specify DISTINCT, duplicate values are eliminated before the SUM function is applied. expression specifies a numeric or interval value expression that determines the values to sum.
PAGE 457
TAN Function The TAN function returns the tangent of a numeric value expression, where the expression is an angle expressed in radians. TAN is a Neoview SQL extension. TAN (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the TAN function. See “Numeric Value Expressions” (page 250). Example of TAN • This function returns the value 3.64008908293626880E-001, or approximately 0.3640, the tangent of 0.3491 (which is 20 degrees): TAN (0.
PAGE 458
TANH Function The TANH function returns the hyperbolic tangent of a numeric value expression, where the expression is an angle expressed in radians. TANH is a Neoview SQL extension. TANH (numeric-expression) numeric-expression is an SQL numeric value expression that specifies the value for the argument of the TANH function. See “Numeric Value Expressions” (page 250). Example of TANH • This function returns the value 8.48283639957512960E-001 or approximately 0.8483, the hyperbolic tangent of 1.
PAGE 459
THIS Function The THIS function is a sequence function that is used in the ROWS SINCE function to distinguish between the value of the column in the current row and the value of the column in previous rows (in an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement). See “ROWS SINCE Function” (page 434). THIS is a Neoview SQL extension. THIS (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 460
TIMESTAMPADD Function The TIMESTAMPADD function adds the interval of time specified by interval-ind and num_expr to datetime_expr. If the specified interval is in years, months, or quarters and the resulting date is not a valid date, the day will be rounded down to the last day of the result month. The type of the datetime_expr is returned except when the interval-ind contains any time component, in which case a TIMESTAMP is returned. TIMESTAMPADD is a Neoview extension.
PAGE 461
TIMESTAMPDIFF Function The TIMESTAMPDIFF function returns the integer value for the number of interval-ind units of time between startdate and enddate. If enddate precedes startdate, the return value is negative or zero.
PAGE 462
TRANSLATE Function The TRANSLATE function translates a character string from a source character set to a target character set. The TRANSLATE function changes both the character string data type and the character set encoding of the string. TRANSLATE(character-value-expression USING translation-name) character-value-expression is a character string.
PAGE 463
TRIM Function The TRIM function removes leading and trailing characters from a character string. Every character, including multibyte characters, is treated as one character. TRIM ([[trim-type] [trim-char] FROM] trim-source) trim-type is: LEADING | TRAILING | BOTH trim-type specifies whether characters are to be trimmed from the leading end (LEADING), trailing end (TRAILING), or both ends (BOTH) of trim-source. If you omit trim-type, the default is BOTH.
PAGE 464
UCASE Function • • “Considerations for UCASE” “Examples of UCASE” The UCASE function upshifts alphanumeric characters. For non-alphanumeric characters, UCASE returns the same character. UCASE can appear anywhere in a query where a value can be used, such as in a select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE predicate, an expression, or as qualifying a new value in an UPDATE or INSERT statement.
PAGE 465
Table 6-2 One-to-One UCS2 Mappings (continued) x U (x) x U(x) x U(x) x U(x) x U(x) x U(x) 006B 004B 018C 018B 03E3 03E2 04E9 04E8 1E89 1E88 1F7C 1FFA 006C 004C 0192 0191 03E5 03E4 04EB 04EA 1E8B 1E8A 1F7D 1FFB 006D 004D 0199 0198 03E7 03E6 04EF 04EE 1E8D 1E8C 1F80 1F88 006E 004E 01A1 01A0 03E9 03E8 04F1 04F0 1E8F 1E8E 1F81 1F89 006F 004F 01A3 01A2 03EB 03EA 04F3 04F2 1E91 1E90 1F82 1F8A 0070 0050 01A5 01A4 03ED 03EC 04F5 04F4 1E
PAGE 466
Table 6-2 One-to-One UCS2 Mappings (continued) 466 x U (x) x U(x) x U(x) x U(x) x U(x) x U(x) 00F4 00D4 01EF 01EE 044A 042A 057E 054E 1ED9 1ED8 1FF3 1FFC 00F5 00D5 01F2 01F1 044B 042B 057F 054F 1EDB 1EDA 2170 2160 00F6 00D6 01F3 01F1 044C 042C 0580 0550 1EDD 1EDC 2171 2161 00F8 00D8 01F5 01F4 044D 042D 0581 0551 1EDF 1EDE 2172 2162 00F9 00D9 01FB 01FA 044E 042E 0582 0552 1EE1 1EE0 2173 2163 00FA 00DA 01FD 01FC 044F 042F 0583 05
PAGE 467
Table 6-2 One-to-One UCS2 Mappings (continued) x U (x) x U(x) x U(x) x U(x) x U(x) x U(x) 0133 0132 028B 01B2 0481 0480 1E37 1E36 1F25 1F2D 24E3 24C9 0135 0134 0292 01B7 0491 0490 1E39 1E38 1F26 1F2E 24E4 24CA 0137 0136 0345 0399 0493 0492 1E3B 1E3A 1F27 1F2F 24E5 24CB 013A 0139 03AC 0386 0495 0494 1E3D 1E3C 1F30 1F38 24E6 24CC 013C 013B 03AD 0388 0497 0496 1E3F 1E3E 1F31 1F39 24E7 24CD 013E 013D 03AE 0389 0499 0498 1E41 1E40 1F
PAGE 468
Table 6-3 Two-Character UCS2 Mapping 468 Titlecase characters Two-character uppercase expansions 0x00DF 0x0053 0x0053 0x0149 0x02BC 0x004E 0x01F0 0x004A 0x030C 0x0587 0x0535 0x0552 0x1E96 0x0048 0x0331 0x1E97 0x0054 0x0308 0x1E98 0x0057 0x030A 0x1E99 0x0059 0x030A 0x1E9A 0x0041 0x02BE 0x1F50 0x03A5 0x0313 0x1F80 0x1F08 0x0399 0x1F81 0x1F09 0x0399 0x1F82 0x1F0A 0x0399 0x1F83 0x1F0B 0x0399 0x1F84 0x1F0C 0x0399 0x1F85 0x1F0D 0x0399 0x1F86 0x1F0E 0x0399 0x1F87 0x1F0F 0x039
PAGE 469
Table 6-3 Two-Character UCS2 Mapping (continued) Titlecase characters Two-character uppercase expansions 0x1F9A 0x1F2A 0x0399 0x1F9B 0x1F2B 0x0399 0x1F9C 0x1F2C 0x0399 0x1F9D 0x1F2D 0x0399 0x1F9E 0x1F2E 0x0399 0x1F9F 0x1F2F 0x0399 0x1FA0 0x1F68 0x0399 0x1FA1 0x1F69 0x0399 0x1FA2 0x1F6A 0x0399 0x1FA3 0x1F6B 0x0399 0x1FA4 0x1F6C 0x0399 0x1FA5 0x1F6D 0x0399 0x1FA6 0x1F6E 0x0399 0x1FA7 0x1F6F 0x0399 0x1FA8 0x1F68 0x0399 0x1FA9 0x1F69 0x0399 0x1FAA 0x1F6A 0x0399 0x1FAB 0x1F6B
PAGE 470
Table 6-3 Two-Character UCS2 Mapping (continued) Titlecase characters Two-character uppercase expansions 0x1FF3 0x03A9 0x0399 0x1FF4 0x038F 0x0399 0x1FF6 0x03A9 0x0342 0x1FFC 0x03A9 0x0399 0xFB00 0x0046 0x0046 0xFB01 0x0046 0x0049 0xFB02 0x0046 0x004C 0xFB05 0x0053 0x0054 0xFB06 0x0053 0x0054 0xFB13 0x0544 0x0546 0xFB14 0x0544 0x0535 0xFB15 0x0544 0x053B 0xFB16 0x054E 0x0546 0xFB17 0x0544 0x053D Table 6-4 Three-Character UCS2 Mapping 470 Titlecase characters Three-character
PAGE 471
Examples of UCASE • Suppose that your CUSTOMER table includes an entry for Hotel Oregon. Select the column CUSTNAME and return in uppercase and lowercase letters by using the UCASE and LCASE functions: SELECT custname,UCASE(custname),LCASE(custname) FROM sales.customer; (EXPR) ----------------... Hotel Oregon (EXPR) ------------------... HOTEL OREGON (EXPR) -----------------... hotel oregon --- 17 row(s) selected. See “LCASE Function” (page 390).
PAGE 472
UPPER Function The UPPER function upshifts alphanumeric characters. For non-alphanumeric characters, UCASE returns the same character. UPPER can appear anywhere in a query where a value can be used, such as in a select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE predicate, an expression, or as qualifying a new value in an UPDATE or INSERT statement. The result returned by the UPPER function is equal to the result returned by the “UPSHIFT Function” (page 473) or “UCASE Function” (page 464).
PAGE 473
UPSHIFT Function The UPSHIFT function upshifts alphanumeric characters. For non-alphanumeric characters, UCASE returns the same character. UPSHIFT can appear anywhere in a query where a value can be used, such as in a select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE predicate, an expression, or as qualifying a new value in an UPDATE or INSERT statement.
PAGE 474
USER Function The USER function returns the user name that you logged in as. If you are logged in as the role, the role is returned. This function can be used anywhere in the query. The values returned are string datatype VARCHAR(64). USER The USER function is equivalent to the “CURRENT_USER Function” (page 357). Considerations for USER The USER function returns the user name in ISO8859-1 encoding on a Neoview platform that has the ISO88591 or SJIS configuration.
PAGE 475
VARIANCE Function • • “Considerations for VARIANCE” “Examples of VARIANCE” VARIANCE is an aggregate function that returns the statistical variance of a set of numbers. VARIANCE is a Neoview SQL extension. VARIANCE ([ALL | DISTINCT] expression [,weight]) ALL | DISTINCT specifies whether duplicate values are included in the computation of the VARIANCE of the expression. The default option is ALL, which causes duplicate values to be included.
PAGE 476
where vi is the i-th value of expression, wi is the i-th value of weight, and N is the cardinality of the result table. Data Type of the Result The data type of the result is always DOUBLE PRECISION. Operands of the Expression The expression includes columns from the rows of the SELECT result table—but cannot include an aggregate function. These expressions are valid: VARIANCE (SALARY) VARIANCE (SALARY * 1.
PAGE 477
WEEK Function The WEEK function converts a DATE or TIMESTAMP expression into an INTEGER value in the range 1 through 54 that represents the corresponding week of the year. If the year begins on a Sunday, the value 1 will be returned for any datetime that occurs in the first 7 days of the year. Otherwise, the value 1 will be returned for any datetime that occurs in the partial week before the start of the first Sunday of the year.
PAGE 478
YEAR Function The YEAR function converts a DATE or TIMESTAMP expression into an INTEGER value that represents the year. YEAR is a Neoview SQL extension. YEAR (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. See “Datetime Value Expressions” (page 242). Example of YEAR • Return an integer that represents the year from the START_DATE column in the PROJECT table: SELECT start_date, ship_timestamp, YEAR(start_date) FROM persnl.
PAGE 479
ZEROIFNULL Function The ZEROIFNULL function returns a value of zero if the expression if NULL. Otherwise, it returns the value of the expression. ZEROIFNULL (expression) expression specifies a value expression. It must be a numeric data type. Example of ZEROIFNULL ZEROIFNULL returns the value of the column named salary whenever the column value is not NULL and it returns 0 whenever the column value is NULL.
PAGE 480
PAGE 481
7 OLAP Functions This section describes the syntax and semantics of the On Line Analytical Process (OLAP) window functions. The OLAP window functions are ANSI compliant. Considerations for Window Functions These considerations apply to all window functions. inline-window-specification The window defined by the inline-window-specification consists of the rows specified by the window-frame-clause, bounded by the current partition.
PAGE 482
SELECT num, workgroupnum, annualsalary, SUM(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING), AVG(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING), MIN(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING), MAX(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING), VARIANCE(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING), STDDEV(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num
PAGE 483
AVG Window Function AVG is a window function that returns the average of nonnull values of the given expression for the current window specified by the inline-window specification. AVG ([ALL] expression) OVER (inline-window-specification) inline-window-specification is: [PARTITION BY expression [, expression]...] [ORDER BY expression [ASC[ENDING] | DESC[ENDING]] [,expression [ASC[ENDING] | DESC[ENDING]]]...
PAGE 484
SELECT deptnum, empnum, AVG(SALARY) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 485
COUNT Window Function COUNT is a window function that returns the count of the non null values of the given expression for the current window specified by the inline-window-specification. COUNT {(*) | ([ALL] expression) } OVER inline-window-specification inline-window-specification is: [PARTITION BY expression [, expression]...] [ORDER BY expression [ASC[ENDING] | DESC[ENDING]] [,expression [ASC[ENDING] | DESC[ENDING]]]...
PAGE 486
SELECT deptnum, empnum, COUNT(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.employee; • Return the running count of employees within each department: SELECT deptnum, empnum, COUNT(*) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING) FROM persnl.
PAGE 487
DENSE_RANK Window Funtion DENSE_RANK is a window function that returns the ranking of each row of the current partition specified by the inline-window-specification. The ranking is relative to the ordering specified in the inline-window-specification. The return value of DENSE_RANK starts at 1 for the first row of the window. Values of the given expression that are equal have the same rank. The value of DENSE_RANK advances 1 when the value of the given expression changes.
PAGE 488
MAX Window Function MAX is a window function that returns the maximum value of all non null values of the given expression for the current window specified by the inline-window-specification. MAX ([ALL] expression) OVER (inline-window-specification) inline-window-specification is: [PARTITION BY expression [, expression]...] [ORDER BY expression [ASC[ENDING] | DESC[ENDING]] [,expression [ASC[ENDING] | DESC[ENDING]]]...
PAGE 489
SELECT deptnum, empnum, MAX(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 490
MIN Window Function MIN is a window function that returns the minimum value of all non null values of the given expression for the current window specified by the inline-window-specification. MIN ([ALL] expression) OVER (inline-window-specification) inline-window-specification is: [PARTITION BY expression [, expression]...] [ORDER BY expression [ASC[ENDING] | DESC[ENDING]] [,expression [ASC[ENDING] | DESC[ENDING]]]...
PAGE 491
SELECT deptnum, empnum, MIN(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 492
RANK Window Function RANK is a window function that returns the ranking of each row of the current partition specified by the inline-window-specification. The ranking is relative to the ordering specified in the inline-window-specification. The return value of RANK starts at 1 for the first row of the window. Values that are equal have the same rank. The value of RANK advances to the relative position of the row in the window when the value changes.
PAGE 493
ROW_NUMBER Window Function ROW_NUMBER is a window function that returns the row number of each row of the current window specified by the inline-window-specification. ROW_NUMBER () OVER (inline-window-specification) inline-window-specification is: [PARTITION BY expression [, expression]...] [ORDER BY expression [ASC[ENDING] | DESC[ENDING]] [,expression [ASC[ENDING] | DESC[ENDING]]]...] inline-window-specification specifies the window over which the ROW_NUMBER is computed.
PAGE 494
STDDEV Window Function STDDEV is a window function that returns the standard deviation of non null values of the given expression for the current window specified by the inline-window-specification. STDDEV ([ALL] expression) OVER (inline-window-specification) inline-window-specification is: [PARTITION BY expression [, expression]...] [ORDER BY expression [ASC[ENDING] | DESC[ENDING]] [,expression [ASC[ENDING] | DESC[ENDING]]]...
PAGE 495
SUM Window Function SUM is a window function that returns the sum of non null values of the given expression for the current window specified by the inline-window-specification. SUM ([ALL] expression) OVER (inline-window-specification) inline-window-specification is: [PARTITION BY expression [, expression]...] [ORDER BY expression [ASC[ENDING] | DESC[ENDING]] [,expression [ASC[ENDING] | DESC[ENDING]]]...
PAGE 496
SELECT deptnum, empnum, SUM (salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 497
VARIANCE Function VARIANCE is a window function that returns the variance of non null values of the given expression for the current window specified by the inline-window-specification. VARIANCE ([ALL] expression) OVER (inline-window-specification) inline-window-specification is: [PARTITION BY expression [, expression]...] [ORDER BY expression [ASC[ENDING] | DESC[ENDING]] [,expression [ASC[ENDING] | DESC[ENDING]]]...
PAGE 498
PAGE 499
A Quick Reference This appendix provides a quick, alphabetic reference to commands, statements, and utilities. For other topics, see the Index.
PAGE 500
L “LOCK TABLE Statement” (page 147) M “MAINTAIN Command” (page 210) “MERGE INTO Statement” (page 149) P “POPULATE INDEX Utility” (page 218) “PURGEDATA Utility” (page 219) “PREPARE Statement” (page 152) R “REVOKE Statement” (page 154) “REVOKE EXECUTE Statement” (page 156) “REVOKE SCHEMA Statement” (page 158) “ROLLBACK WORK Statement” (page 161) S “SELECT Statement” (page 162) “SET SCHEMA Statement” (page 184) “SET TABLE TIMEOUT Statement” (page 185) “SET TRANSACTION Statement” (page 187) “SHOWDDL Stateme
PAGE 501
B Reserved Words The words listed in this appendix are reserved for use by Neoview SQL. To prevent syntax errors, avoid using these words as identifiers in Neoview SQL. In Neoview SQL, if an HP operating system name contains a reserved word, you must enclose the reserved word in double quotes (") to access that column or object. In Table B-1, an asterisk (*) indicates reserved words that are Neoview SQL extensions. Words reserved by the ANSI standard are not marked.
PAGE 502
Table B-1 Reserved SQL Identifiers (continued) BIT_LENGTH INITIALLY SCHEMA BLOB1 INNER SCOPE BOOLEAN INOUT SCROLL BOTH INPUT SEARCH BREADTH INSENSITIVE SECOND BY INSERT SECTION CALL INT SELECT CASE INTEGER SENSITIVE1 CASCADE INTERSECT SESSION CASCADED INTERVAL SESSION_USER CAST INTO SET CATALOG IS SETS CHAR ISOLATION SIGNAL1 CHAR_LENGTH ITERATE SIMILAR1 CHARACTER JOIN SIZE CHARACTER_LENGTH KEY SMALLINT CHECK LANGUAGE SOME CLASS LARGE CLOB LAST SPECIFIC
PAGE 503
Table B-1 Reserved SQL Identifiers (continued) CUBE MINUTE SQLWARNING CURRENT MODIFIES STRUCTURE CURRENT_DATE MODIFY SUBSTRING CURRENT_PATH MODULE SUM CURRENT_ROLE MONTH SYSTEM_USER CURRENT_TIME NAMES TABLE CURRENT_TIMESTAMP NATIONAL TEMPORARY CURRENT_USER NATURAL TERMINATE CURSOR NCHAR TEST1 CYCLE NCLOB THAN1 DATE NEW THEN DATETIME1 NEXT THERE1 DAY NO TIME DEALLOCATE NONE TIMESTAMP DEC NOT TIMEZONE_HOUR DECIMAL NULL TIMEZONE_MINUTE DECLARE NULLIF TO DEFAUL
PAGE 504
Table B-1 Reserved SQL Identifiers (continued) EACH PAD VALUE PARAMETER VALUES ELSEIF PARAMETERS VARCHAR END PARTIAL VARIABLE END-EXEC PENDANT1 VARYING EQUALS POSITION VIEW ESCAPE POSTFIX VIRTUAL1 EXCEPT PRECISION VISIBLE1 EXCEPTION PREFIX WAIT1 EXEC PREORDER WHEN EXECUTE PREPARE WHENEVER EXISTS PRESERVE WHERE EXTERNAL PRIMARY WHILE1 EXTRACT PRIOR WITH FALSE PRIVATE1 WITHOUT FETCH PRIVILEGES WORK FIRST PROCEDURE WRITE FLOAT PROTECTED1 YEAR ELSE 1 ZONE 1
PAGE 505
C Limits This appendix lists limits for various parts of Neoview SQL Column names 128 characters in length. Constraints The maximum combined length of the columns for a PRIMARY KEY constraint is 2048 bytes. Indexes The maximum combined length of the columns for an index is 2048 bytes. A nonunique index consists of columns and a clustering key. A unique index consists of columns only. Schema names 128 characters in length. Tables ANSI names are of the form schema.
PAGE 506
PAGE 507
D Standard SQL and Neoview SQL This appendix describes Neoview SQL conformance to the SQL standards established by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO). It describes: • “ANSI SQL Standards” • “ISO Standards” • “Neoview SQL Compliance” • “Standard SQL and Neoview SQL” • “Character Set Support” This appendix documents Neoview SQL conformance with the standards criteria for SQL:1999, which replaced ANSI SQL-92.
PAGE 508
Neoview SQL Compliance The ANSI and ISO SQL standards require conformance claims to state the type of conformance and the implemented facilities. Neoview SQL products provide full or partial conformance.
PAGE 509
This table lists the Core SQL:1999 features for which Neoview SQL offers partial support: ID, Feature Level of Support E071 Basic query expressions Neoview SQL fully supports this subfeature: • E071-02 UNION ALL table operator Neoview SQL partially supports these subfeatures: • E071-01 UNION [DISTINCT] table operator (Neoview SQL does not support explicitly specifying DISTINCT) • E071-05 Columns combined by table operators need not have exactly the same type (Neoview SQL has rules for determining result
PAGE 510
ID, Feature Level of Support F051 Basic date and time Neoview SQL fully supports these subfeatures: • F051-01 DATE data type (and literal) • F051-02 TIME data type (and literal) with fractional seconds precision of at least 0 • F051-03 TIMESTAMP data type (and literal) with fractional seconds precision of at least 0 and 6 • F051-04 Comparison predicate for DATE, TIME, and TIME-STAMP data types • F051-05 Explicit CAST between datetime types and character types • F051-06 CURRENT_DATE Neoview SQL does not s
PAGE 511
choice between module language and embedded language. Module language and embedded language are identical in capability. They differ only in how SQL statements are associated with the host programming language. Neoview SQL Extensions to Standard SQL Neoview SQL provides many features that enhance or supplement the functionality of standard SQL. In your Neoview SQL applications, you can use these extensions just as you can use Core SQL:1999.
PAGE 512
Neoview SQL relaxes SQL:1999's data type matching rule for UCS2 character set host variables for ease of use and better performance. A UCS2 host variable is assignment and comparison compatible with an ISO88591 value expression. Neoview SQL allows various SQL:1999's NATIONAL CHARACTER syntax to denote a predesignated character set. The default NATIONAL character set is UCS2. Neoview SQL uses binary collation (that is, comparison of binary code values) to compare character strings.
PAGE 513
E Special Neoview Features—Enabled Upon Request Mode_Special_2 NOTE: To enable these features, you must request access from your HP account representative. Mode_special_2 is a mode that offers some compatibility with another DBMS. It is intended to help you transition from your current platform to the Neoview platform. HP recommends that you use Neoview syntax and semantics on the Neoview platform to avoid getting errors.
PAGE 514
YYYYMMDD DD.MM.YYYY DD-MM-YYYY Specify the date format in a DATE literal using this syntax: 'string' (DATE, FORMAT 'date-format') For example: "INSERT INTO table (date_column) VALUES ( '20090217' (date, FORMAT 'YYYYMMDD') )" • String Truncation No error is returned if truncation occurred in an INSERT or UPDATE statement. • Rounding of Arithmetic and Conversion Operators The "HalfMagEven" semantics are supported.
PAGE 515
Index A ABS function examples of, 324, 326 syntax diagram of, 324 Access options summary of, 28 DELETE statement use of, 106 DML statements use of, 28 INSERT statement use of, 141 READ COMMITTED, 28 READ UNCOMMITTED , 28 REPEATABLE READ, 29 SELECT statement use of, 171 SERIALIZABLE, 28, 29, 35 SKIP CONFLICT, 29 UPDATE statement use of, 198 Access privileges stored procedures, 135 tables, 133, 137 ACOS function examples of, 325 syntax diagram of, 325, 326 AFTER LAST ROW clause, 166 Aggregate functions summar
PAGE 516
required privileges, 55 syntax diagram of, 55 usage restrictions, 55 CASE expression data type of, 335 examples of, 335 searched CASE form, 334 syntax diagram of, 334 CAST expression data type conversion, 337 examples of, 337 syntax diagram of, 337 valid type combinations, 337 CEILING function examples of, 339 syntax diagram of, 339 CHAR data type, 233, 234 CHAR function examples of, 340 syntax diagram of, 340 CHAR VARYING data type, 234 CHAR_LENGTH function examples of, 341 syntax diagram of, 341 Character
PAGE 517
JULIANTIMESTAMP inverse relationship to, 347 syntax diagram of, 347 CONVERTTOHEX function examples of, 345, 346 syntax diagram of, 345 Correlation names examples of, 226 purpose of, 226 table reference use of, 226 COS function examples of, 348 syntax diagram of, 348 COSH function examples of, 349 syntax diagram of, 349 COUNT function DISTINCT clause within, 350 examples of, 350 syntax diagram of, 350 COUNT window function examples of, 485 syntax diagram of, 485 CREATE INDEX statement authorization and avail
PAGE 518
CROSS join, description of, 168 CURRENT_DATE function examples of, 353 syntax diagram of, 353 CURRENT_ROLE function example of, 354 syntax diagram of, 354 CURRENT_TIME function examples of, 355 precision specification within, 355 syntax diagram of, 355 CURRENT_TIMESTAMP function examples of, 352, 356 precision specification within, 352, 356 syntax diagram of, 352, 356 CURRENT_USER function example of, 357 syntax diagram of, 357 D Data Definition Language (DDL) statements summary of, 39 ALTER MATERIALIZED V
PAGE 519
syntax diagram of, 362 Datetime data types DATE, 235 description of, 235 examples of literals, 258 TIME, 235 TIMESTAMP, 235 Datetime functions summary of, 319 ADD_MONTHS, 326 CONVERTTIMESTAMP, 347 CURRENT, 352 CURRENT_DATE, 353 CURRENT_TIME, 355 CURRENT_TIMESTAMP, 356 DATE_ADD, 358 DATE_PART (interval), 363 DATE_PART (timestamp), 364 DATE_SUB, 359 DATE_TRUNC, 365 DATEADD, 360 DATEDIFF, 361 DATEFORMAT, 362 DAY, 366 DAYNAME, 367 DAYOFMONTH, 368 DAYOFWEEK, 369 DAYOFYEAR, 370 EXTRACT, 383 HOUR, 385 JULIANTIMEST
PAGE 520
SUM function use of, 456 VARIANCE function use of, 475 DML operations on materialized views restrictions for, 66 DML statements (see Data Manipulation Language (DML) statements) DOUBLE PRECISION data type, 239 DROP INDEX statement authorization and availability requirements, 109 examples of, 109 syntax diagram of, 109 DROP MATERIALIZED VIEW statement example of, 110 syntax diagram of, 110 DROP MVGROUP statement example of, 111 syntax description of, 111 DROP SCHEMA statement authorization and availability r
PAGE 521
FLOAT data type, 239 FLOOR function examples of, 384 syntax diagram of, 384 FORMAT clause considerations for date formats, 297 considerations for other formats, 297 examples of, 297 syntax diagram, 296 FULL join, description of, 168 Functions, ANSI compliant, 36 G GET SERVICE statement examples of, 131 syntax of, 131 GRANT EXECUTE statement authorization and availability requirements, 135 examples of, 136 syntax diagram of, 135 GRANT SCHEMA statement authorization and availability requirements, 139 example
PAGE 522
optional specifications, 168 RIGHT, 168 types, 168 JOIN ON join, description of, 168 JULIANTIMESTAMP function examples of, 388 syntax diagram of, 388 K Keys clustering, 255 index, 255 primary, 255 L LARGEINT data type, 239 LASTNOTNULL function examples of, 389 syntax diagram of, 389 LCASE function examples of, 390 syntax diagram of, 390 LEFT function examples of, 391 syntax diagram of, 391 LEFT join, description of, 168 LIKE predicate syntax of, 275 Limits constraints, 505 IN predicate, 273 indexes, 60, 5
PAGE 523
using other materialized views, 64 Math functions summary of, 320 ABS, 324 ACOS, 325 ASIN, 328 ATAN, 329 ATAN2, 330 CEILING, 339 COS, 348 COSH, 349 DEGREES, 374 EXP, 379 FLOOR, 384 LOG, 393 LOG10, 394 MOD, 405 PI, 422 POWER, 424 RADIANS, 426 ROUND, 433 SIGN, 447 SIN, 448 SINH, 449 SQRT, 451 TAN, 457 TANH, 458 ZEROIFNULL, 479 MAV definition of, 63, 263 restrictions for, 65 MAX function considerations for, 402 DISTINCT clause within, 402 examples of, 402 syntax diagram of, 402 MAX window function examples of,
PAGE 524
NULL, using, 197 NULLIF function example of, 417 syntax diagram of, 417 NULLIFZERO function examples of, 418 syntax diagram of, 418 Numeric data types approximate numeric, 238 exact numeric, 238 extended numeric , 232 literals, examples of, 262 Numeric literals approximate, 261 exact, 261 examples of, 262 Numeric value expression evaluation order, 250 examples of, 251 syntax diagram of, 250 NVL function examples of, 419 syntax diagram of, 419 O Object names, 228 Object namespace, 228 Object-privilege, gene
PAGE 525
on views, 134 REVOKE EXECUTE statement use of, 156 REVOKE SCHEMA statement use of, 158 REVOKE statement use of, 154 PURGEDATA utility, 219 Q Quantified comparison predicates ALL, ANY, SOME, 279 examples of, 279 operand requirements, 279 result of, 279 syntax diagram of, 278 QUARTER function examples of, 425 syntax diagram of, 425 Query execution plan displayed, 120 operators, 120 reviewing, 120 Query expression INSERT statement use of, 142 SELECT statement use of, 166 syntax diagram of, 99, 141 Query speci
PAGE 526
syntax diagram of, 440 RUNNINGMAX function examples of, 441 syntax diagram of, 441 RUNNINGMIN function examples of, 442 syntax diagram of, 442 RUNNINGRANK function considerations for, 427 example for, 427 syntax diagram of, 427 RUNNINGSTDDEV function equivalent definition, 443 examples of, 443 syntax diagram of, 443 RUNNINGSUM function examples of, 444 syntax diagram of, 444 RUNNINGVARIANCE function examples of, 445 syntax diagram of, 445 S SAMPLE clause cluster sampling, 300 examples of, 300 SELECT statem
PAGE 527
examples of, 186 syntax diagram of, 185 SET TRANSACTION statement considerations for, 189 description of, 188 examples of, 191 syntax diagram of, 187 transaction modes set by, 188 SHARE lock mode, 29 SHOWDDL statement, 192 considerations for, 192 differences between output and original DDL, 192 examples of, 193 privileges option, 193 syntax diagram of, 192 SIGN function examples of, 447 syntax diagram of, 447 Simple table, in SELECT statement, 168 SIN function examples of, 448 syntax diagram of, 448 SINH fu
PAGE 528
Synonyms considerations, 44, 113 description of, 289 T Table description of, 290 limits, 505 names, reserved, 83 size, 73 subquery, 273 Table reference description of, 166 SELECT statement use of, 166 Table value constructor description of, 169 simple table, form of, 169 Tables creating, 70 renaming, 48 TAN function examples of, 457 syntax diagram of, 457 TANH function examples of, 458 syntax diagram of, 458 THIS function examples of, 459 syntax diagram of, 459 Timeout values dynamic, 185 TIMESTAMPADD func
PAGE 529
POPULATE INDEX utility, 218 PURGEDATA, 219 V Value expressions, 240 summary of, 323 CASE (Conditional) expression, 334 CAST expression, 337 VARCHAR data type, 234 Variable-length character column, 234 VARIANCE function DISTINCT clause within, 475 examples of, 476 statistical definition of, 475 syntax diagram of, 475 VARIANCE window function examples of, 497 syntax diagram of, 497 Vertical partition example, 103 NOT ENFORCED constraint, 104 Views CREATE VIEW statement, 99 description of, 292 DROP VIEW state
PAGE 530