Neoview SQL Reference Manual (R2.4 SP2)

ManualsBrandsHP ManualsSoftwareHP Neoview Release 2.4 Software

HP Neoview SQL Reference Manual

HP Part Number: 602622-002

Published: April 2010

Edition: HP Neoview Release 2.4 Service Pack 2

Summary of content (538 pages)

PAGE 1
HP Neoview SQL Reference Manual HP Part Number: 602622-002 Published: April 2010 Edition: HP Neoview Release 2.
PAGE 2
© Copyright 2010 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
PAGE 3
Table of Contents About This Document.......................................................................................................19 Intended Audience................................................................................................................................19 New and Changed Information in This Edition...................................................................................19 Document Organization.......................................................................
PAGE 4
ALTER MATERIALIZED VIEW Statement..........................................................................................40 Syntax Description of ALTER MATERIALIZED VIEW..................................................................40 Considerations for ALTER MATERIALIZED VIEW.......................................................................40 Example of ALTER MATERIALIZED VIEW...................................................................................41 ALTER MVGROUP Statement.............
PAGE 5
CREATE TABLE Statement...................................................................................................................72 Syntax Description of CREATE TABLE...........................................................................................73 Considerations for CREATE TABLE................................................................................................78 Considerations for CREATE SET TABLE...............................................................................
PAGE 6
Examples of DROP TABLE............................................................................................................116 DROP TRIGGER Statement................................................................................................................117 Syntax Description of DROP TRIGGER........................................................................................117 Considerations for DROP TRIGGER...................................................................................
PAGE 7
Examples of REVOKE EXECUTE..................................................................................................158 REVOKE SCHEMA Statement............................................................................................................159 Syntax Description of REVOKE SCHEMA....................................................................................159 Considerations for REVOKE SCHEMA........................................................................................
PAGE 8
Examples of MAINTAIN...............................................................................................................215 POPULATE INDEX Utility.................................................................................................................218 Syntax Description of POPULATE INDEX....................................................................................218 Considerations for POPULATE INDEX.................................................................................
PAGE 9
Materialized Views.............................................................................................................................268 Types of Materialized Views..........................................................................................................268 MVGROUPs .......................................................................................................................................269 Null............................................................................
PAGE 10
6 SQL Functions and Expressions................................................................................325 Categories............................................................................................................................................325 Standard Normalization................................................................................................................325 Aggregate (Set) Functions................................................................................
PAGE 11
CONVERTTOHEX Function...............................................................................................................354 Considerations for CONVERTTOHEX..........................................................................................354 Examples of CONVERTTOHEX....................................................................................................354 CONVERTTIMESTAMP Function..................................................................................................
PAGE 12
DECODE Function..............................................................................................................................380 Considerations for DECODE.........................................................................................................380 Examples of DECODE...................................................................................................................381 DEGREES Function................................................................................
PAGE 13
Considerations for MIN.................................................................................................................408 Example of MIN.............................................................................................................................408 MINUTE Function...............................................................................................................................409 Example of MINUTE...................................................................
PAGE 14
RIGHT Function..................................................................................................................................437 Examples of RIGHT.......................................................................................................................437 ROUND Function................................................................................................................................438 Examples of ROUND.............................................................
PAGE 15
TAN Function......................................................................................................................................462 Example of TAN............................................................................................................................462 TANH Function...................................................................................................................................463 Example of TANH.........................................................
PAGE 16
Examples of ROW_NUMBER Window Function.........................................................................491 STDDEV Window Function................................................................................................................492 Examples of STDDEV....................................................................................................................492 SUM Window Function......................................................................................................
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 A-1 A-2 A-3 A-4 B-1 B-2 B-3 B-4 B-5 B-6 B-7 B-8 B-9 B-10 B-11 B-12 B-13 B-14 B-15 B-16 B-17 B-18 B-19 B-20 B-21 B-22 B-23 B-24 B-25 Concurrent DDL/Utility Operation and File Access Modes.........................................................30 Concurrent DDL/Utility and DML Operations.............................................................................30 Concurrent DML and DDL Operations ...................................................
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 one of these client-based utilities: • The Neoview Command Interface (NCI). For information about NCI, see the Neoview Command Interface (NCI) Guide. • The HP Database Manager.
PAGE 20
Notation Conventions General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: SELECT Italic Letters Italic letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required.
PAGE 21
| Vertical Line A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example: {expression | NULL} … Ellipsis An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example: ATTRIBUTE[S] attribute [, attribute]... {, sql-expression}... An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times.
PAGE 22
Neoview Customer Library The manuals in the Neoview customer library are listed here for your convenience.
PAGE 23
Neoview User Management Information Information about the logging of user-management actions on the Neoview platform and the facilities available for monitoring such actions. Neoview Workload Management Information about using Neoview Workload Management Services (WMS) to Services Guide manage workload and resources on a Neoview data warehousing platform.
PAGE 24
HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to docsfeedback@hp.com. Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
PAGE 25
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 26
“SERIALIZABLE or REPEATABLE READ” Specifies that the DML 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” Allows transactions to skip rows locked in a conflicting mode by another transaction. SKIP CONFLICT cannot be used in a SET TRANSACTION statement. The SQL default access option for DML statements is READ COMMITTED.
PAGE 27
Database Integrity and Locking To protect the integrity of the database, Neoview SQL provides locks on data. For example, Neoview SQL locks a row when an executing process accesses a row to modify it. The lock ensures that no other process simultaneously modifies the same row. Default locking normally protects data but reduces concurrency. If your application has problems with lock contention, you might want to use options that control the characteristics of locks.
PAGE 28
Lock Holder Only the lock holder can release a lock. A transaction releases the locks it holds at the end of the transaction in either of these cases: • • Locks on data read using SERIALIZABLE access Locks on rows updated Transaction Management A transaction (a set of database changes that must be completed as a group) is the basic recoverable unit in case of a failure or transaction interruption. Transactions are controlled through client tools that interact with the database using ODBC or JDBC.
PAGE 29
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. User-Defined and System-Defined Transactions User-Defined Transactions Transactions you define are called user-defined transactions. To ensure that a sequence of statements executes successfully or not at all, you can define one transaction consisting of these statements by using the BEGIN WORK statement and COMMIT WORK statement.
PAGE 30
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 31
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 32
the DML statements, only the SELECT statement can execute within that transaction. DDL statements are not permitted in a READ ONLY transaction. 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 33
READ COMMITTED provides sufficient consistency for any transaction that does not require a repeatable-read capability. The default isolation level is READ COMMITTED. 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.
PAGE 34
• • • • • • • • • • • • • • • • • DROP TRIGGER statement DROP VIEW statement EXECUTE statement GRANT statement 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 TABLE statement UPDATE statement VALUES statement Statements That Are Neoview SQL Extensions These statements are Neoview SQL extensions to the ANSI standard.
PAGE 35
• • • • • • • • • • • • • • • • • • CHAR_LENGTH COALESCE COUNT Function CURRENT CURRENT_DATE CURRENT_TIME 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.
PAGE 36
PAGE 37
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 one of these client-based utilities: • The Neoview Command Interface (NCI). For information about NCI, see the Neoview Command Interface (NCI) Guide. • The HP Database Manager. For information about HPDM, see the HP Database Manager (HPDM) User Guide.
PAGE 38
“DROP TRIGGER Statement” (page 117) Drops a trigger. “DROP VIEW Statement” (page 118) Drops a view. “GRANT Statement” (page 135) Grants access privileges for a table or view to specified roles. “GRANT EXECUTE Statement” (page 137) Grants privileges for executing a stored procedure in Java (SPJ) to specified roles. “GRANT SCHEMA Statement” (page 139) Grants access privileges for a schema to specified roles.
PAGE 39
“PREPARE Statement” (page 153) Compiles an SQL statement for later use with the EXECUTE statement. “UPDATE STATISTICS Statement” (page 203) Updates statistics about the contents of a table and its indexes. 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.
PAGE 40
ALTER MATERIALIZED VIEW Statement • • • “Syntax Description of ALTER MATERIALIZED VIEW” “Considerations for ALTER MATERIALIZED VIEW” “Example of ALTER MATERIALIZED VIEW” The ALTER MATERIALIZED VIEW statement changes a materialized view. 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 41
rows in the materialized view, an error will occur that indicates a REFRESH needs to be performed before altering the materialized view to add IGNORE CHANGES on the base table. Only a base table that is in the FROM clause of the query expression of the materialized view can be added to the IGNORE CHANGES list.
PAGE 42
ALTER MVGROUP Statement • • • “Syntax Description of ALTER MVGROUP” “Considerations for ALTER MVGROUP” “Examples of ALTER MVGROUP” 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 269). ALTER MVGROUP mv-group-name {ADD mv-name [, mv-name] ...
PAGE 43
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 44
ALTER TABLE Statement • • • “Syntax Description of ALTER TABLE” “Considerations for ALTER TABLE” “Example of ALTER TABLE” The ALTER TABLE statement changes a Neoview SQL table. See “Database Object Names” (page 230).
PAGE 45
default is: literal | NULL | CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP } ref-spec is: referenced-table [(column-list)] table-constraint is: FOREIGN KEY (column-list) REFERENCES ref-spec NOT ENFORCED | CHECK (condition) recalibrate-option is: | ALTER COLUMN column-name RECALIBRATE | ALTER COLUMN column-name RECALIBRATE TO internal-sequence-generator-numeric-value | ALTER COLUMN column-name RECALIBRATE TO internal-sequence-generator-numeric-value NO SELECT basic-sequence-generator-option is: internal-se
PAGE 46
NULL clauses. If you omit the DEFAULT clause and specify NOT NULL, Neoview SQL returns an error. For existing rows of the table, the added column takes on its default value. If you set the default to the datetime value CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP, Neoview SQL uses January 1, 1 A.D. 12:00:00.000000 as the default date and time for the existing rows.
PAGE 47
ADD [CONSTRAINT constraint] table-constraint adds a constraint to the table and optionally specifies constraint as the name for the constraint. The new constraint must be consistent with any data already present in the table. CONSTRAINT constraint specifies a name for the column or table constraint. constraint must have the same schema as table and must be unique among constraint names in its schema.
PAGE 48
sequence-generator options: INCREMENT BY signed-numeric-literal increments the current value to obtain the next value. Default is 1 (one). The INCREMENT BY value cannot be greater than the maximum value of the data type of the IDENTITY column. INCREMENT BY can not be zero (0) or less than zero. The INCREMENT BY and MAXVALUE options can only be altered one at a time. MAXVALUE signed-numeric-literal minimum non-negative value of the data type of the IDENTITY column starting the cycle range.
PAGE 49
*** ERROR[1595] The MAXVALUE option for the IDENTITY column SURROGATE_KEY must be a valid numeric value. NO MAXVALUE is not allowed. --- SQL operation failed with errors. Rules for ALTER TABLE ALTER COLUMN SET The only options that can be altered for an IDENTITY column are: • INCREMENT BY • MAXVALUE These rules apply: • Only one IDENTITY column sequence generator option can be altered at a time. • The INCREMENT BY option shall not be 0 (zero) or less than 0 (zero).
PAGE 50
Rules for Recalibrating Based on the INCREMENT BY Value • • • The column to be recalibrated must exist and be an IDENTITY column in the table. If the table containing the IDENTITY column was newly created and has no rows added, no calibration is necessary. The recalibration statement ends successfully, but no update will be performed for the internal sequence generator current value. If the table containing the IDENTITY column has rows added, calibration is necessary.
PAGE 51
Recalibrate to a User-Specified Value Without SELECT This method does not perform a SELECT on the base table containing the IDENTITY column to obtain the current maximum value of the IDENTITY column. The user-specified value will not be incremented using the INCREMENT BY internal sequence generator option or adjusted to match its numbering scheme. Rules for Recalibrating to a User-Specified Value Without SELECT • • • • • The column to be recalibrated must exist and be an IDENTITY column in the table.
PAGE 52
100 102 2 3 Example of ALTER TABLE • This example adds a column: ALTER TABLE persnl.
PAGE 53
ALTER TRIGGER Statement • • • “Syntax Description of ALTER TRIGGER” “Considerations for ALTER TRIGGER” “Examples of 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 230).
PAGE 54
ALTER VIEW Statement • • • “Syntax Description of ALTER VIEW” “Considerations for ALTER VIEW” “Example of ALTER VIEW” The ALTER VIEW statement renames an object within the same schema. See “Database Object Names” (page 230). 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 230). RENAME TO new-name changes the logical name of the object within the same schema.
PAGE 55
BEGIN WORK Statement • • “Considerations for BEGIN WORK” “Example of BEGIN WORK” 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 28). BEGIN WORK will raise an error if a transaction is currently active. BEGIN WORK is a Neoview SQL extension.
PAGE 56
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 57
Input Parameter Arguments You pass data to an SPJ by using IN or INOUT parameters.
PAGE 58
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 59
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 28). COMMIT [WORK] WORK is an optional keyword that has no effect. COMMIT WORK issued outside of an active transaction generates error 8605. Considerations for COMMIT WORK BEGIN WORK starts a transaction.
PAGE 60
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 230). CREATE INDEX is a Neoview SQL extension.
PAGE 61
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. The DROP TABLE statement also drops offline indexes of the specified table. DML statements have no effect on offline indexes. By using the POPULATE INDEX utility, you can populate an offline index and remove its offline designation.
PAGE 62
occurs. If you want to access the data while populating the index, use the POPULATE INDEX utility. You cannot access an index directly. Limits on Indexes For nonunique indexes, the sum of the lengths of the columns in the index plus the sum of the length of the clustering key of the underlying table cannot exceed 2048 bytes. For unique indexes, the sum of the lengths of the columns in the index cannot exceed 2048 bytes. There is no restriction on the number of indexes per table.
PAGE 63
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 268). 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 64
refresh-type specifies the method that will be used to update the materialized view. ON REQUEST specifies that the materialized view has to be explicitly refreshed through the MAINTAIN command. RECOMPUTE a materialized view that is initialized every time the materialized view needs to be updated with changes to its base tables. ON STATEMENT a materialized view that is refreshed ON STATEMENT is often called immediate.
PAGE 65
mv-attributes The COMMIT REFRESH EACH attribute is only allowed for ON REQUEST MVs that are defined on single delta MVs. A single delta MV is an MV that can be refreshed based on changes to a single table. So, a MAV or a single delta MAJV (an MV that ignores updates for all the tables in the FROM clause except one table) can specify the COMMIT REFRESH EACH. n-rows refers to the number of rows that refresh processes from the log before committing a transaction and starting another one.
PAGE 66
The INSERT and DELETE privileges on a materialized view can be granted by the creator of the materialized view, the schema owner, or any user that has a grant option. INSERT and UPDATE commands are blocked on materialized views. The DELETE command on RECOMPUTE and ON STATEMENT materialized views is blocked. The DELETE privilege on ON REQUEST MV enables the user to perform deletes.
PAGE 67
Restrictions for CREATE MATERIALIZED VIEW The following restrictions specify what materialized view types can be defined as incremental. MJV (Materialized Join View) • • • • Only inner equi-joins are supported; outer joins and cross joins are not supported. Must be a single block SELECT-FROM-WHERE query (for example, UNION, ORDER BY, subqueries, TRANSPOSE, and SAMPLE are not allowed). SELECT DISTINCT is not supported. Aggregates are not allowed in the select list.
PAGE 68
FROM sales, store WHERE sales.storekey = store.storekey; • This is an example of an MAJV: CREATE MATERIALIZED VIEW sales_by_day_category REFRESH ON REQUEST INITIALIZE ON REFRESH AS SELECT part.category, time.day, SUM(price) sum_price FROM sales, part, time WHERE sales.timekey = time.timekey AND sales.partkey = part.partkey GROUP BY part.category, time.
PAGE 69
CREATE MVGROUP Statement • • “Considerations for MVGROUP” “Example of CREATE MVGROUP” 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 269) and “MAINTAIN MVGROUP” (page 215). CREATE MVGROUP mv-group-name mv-group-name specifies the materialized view group name to create.
PAGE 70
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 291). 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 71
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 72
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. 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 73
default is: literal | NULL | CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP table-constraint is: PRIMARY KEY (key-column-list) | CHECK (condition) | FOREIGN KEY (column-list) REFERENCES ref-spec NOT ENFORCED 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 74
upon insert. Duplicate row means an entire row inclusive of all columns. If the row being inserted has only identical key columns, a duplicate key error is generated, much like normal table behavior. VOLATILE specifies a VOLATILE table, which is a table limited to the session that creates the table. After the session ends, the table is automatically dropped. NO PARTITION creates a non-partitioned table. To create a non-partitioned table, specify the NO PARTITION option in the CREATE TABLE statement.
PAGE 75
If you do not specify MAX TABLE SIZE, a table is created with the following default characteristics: • • For a partitioned table, the space is 25 MB times number of partitions. The maximum size a partitioned table can reach is the size of the disk at the time of table creation. When a partitioned table is created, the table is automatically partitioned across all the disk volumes in the disk pool. For a non-partitioned table, the space allocated is 25 MB.
PAGE 76
identity-column-specification indicates that a particular column is a sequence-generating IDENTITY column. Neoview SQL will generate unique values for this column by default. See “Generating Values For an IDENTITY Column” (page 87). • GENERATED BY DEFAULT AS IDENTITY creates an IDENTITY column, either system-generated or user-specified. DEFAULT specification in the place of a value is allowed. The internal sequence generator is created when an IDENTITY column is specified.
PAGE 77
ref-spec is: referenced-table [(column-list)] referenced-table is the table referenced by the foreign key in a referential constraint. referenced-table cannot be a view. referenced-table cannot be the same as table. referenced-table corresponds to the foreign key in the table. column-list specifies the column or set of columns in the referenced-table that corresponds to the foreign key in table.
PAGE 78
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. include-option WITH CONSTRAINTS directs Neoview SQL to use constraints from source-table.
PAGE 79
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. Considerations for CREATE VOLATILE TABLE • • • • • • • • Volatile temporary tables are closely linked to the session.
PAGE 80
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 81
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 82
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 83
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 84
CREATE TABLE t2 LIKE t1 AS SELECT a,b FROM t1; Table t2 has been created with an internal sequence generator table START WITH value of 0. The current value in the internal sequence generator table is 0. ALTER TABLE t2 ALTER COLUMN a RECALIBRATE; The current maximum value of the IDENTITY column is added to the INCREMENT BY property of the internal sequence generator table and saved as the new current value in the internal sequence generator table.
PAGE 85
CREATE TABLE t2 (a LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, b INT, PRIMARY KEY(a)) AS SELECT a,b FROM t1; --A full select --list is required SELECT * FROM t2; A B 15 1 The CREATE TABLE AS SELECT statement, using a full select list from the source table, provides the values for the GENERATED ALWAYS AS IDENTITY column as user-specified values. The target table will contain the same values for the IDENTITY column as were selected from the source table.
PAGE 86
15 16 1 1 In the next example, table t3 provokes an error because you tried to create a column with the GENERATED ALWAYS AS IDENTITY column, which is not allowed. CREATE TABLE t3 (a LARGEINT GENERATED ALWAYS AS IDENTITY NOT NULL, b INT, PRIMARY KEY(a)) AS SELECT a,b FROM t1; *** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user. *** ERROR[8822] The statement was not prepared.
PAGE 87
• Number of nullable columns. • Padding bytes for proper alignment. • Row header. A set of 32 nullable columns require an additional 4 bytes. Each variable length column requires an additional 4 bytes. Though columns are rearranged to reduce the number of padding bytes, up to 9 additional bytes might be required to ensure proper column alignment for performance reasons. A rough estimate can be calculated using the following formula. The actual size of the row may vary.
PAGE 88
Available Values for an IDENTITY Column The IDENTITY column can be defined as signed LARGEINT, unsigned INTEGER, and unsigned SMALLINT. Each data type has a natural maximum value. The settings chosen for START WITH, MINVALUE, MAXVALUE, and INCREMENT BY create a valid range of available numbers before a maximum is reached. The settings MAXVALUE and INCREMENT BY can be altered using ALTER TABLE ALTER COLUMN to change an artificially low range created during the CREATE TABLE process.
PAGE 89
• • • • • • • The START WITH value must be less than or equal to the MAXVALUE and greater than or equal to the MINVALUE for ascending sequence generator. If the START WITH option is not specified, the minimum value is the minimum value of the data type of the IDENTITY column or, in the case of the IDENTITY column being of data type LARGEINT, the minimum default value will be zero, not -9223372036854775808. The INCREMENT BY value cannot be 0 (zero) or greater than the MAXVALUE.
PAGE 90
CREATE TABLE t_id_S (surrogate_key LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, name CHAR (5) NOT NULL, primary key(surrogate_key) ) HASH PARTITION BY(surrogate_key); • This example shows IDENTITY column surrogate_key as part of the clustering key.
PAGE 91
• In this example, none of the sequence generator options are specified; the defaults values for all options are used. start value: 0 (zero) increment: 1 min value: 0 (zero) max value: 4294967295 NO CYCLE CREATE TABLE tbl1 ( Id_col INTEGER UNSIGNED GENERATED BY DEFAULT AS IDENTITY NOT NULL, Col2 INTEGER, NOT NULL, PRIMARY KEY(Id_col) ); • This example shows that the IDENTITY column options can be specified in any order.
PAGE 92
Examples of CREATE TABLE • This example creates a table. The clustering key is the primary key. CREATE TABLE SALES.ODETAIL ( ordernum NUMERIC (6) UNSIGNED NO partnum NUMERIC (4) UNSIGNED NO unit_price NUMERIC (8,2) NO qty_ordered NUMERIC (5) UNSIGNED NO PRIMARY KEY (ordernum, partnum) ); • DEFAULT DEFAULT DEFAULT DEFAULT NOT NOT NOT NOT NULL, NULL, NULL, NULL, This example creates a table like the JOB table with the same constraints: CREATE TABLE SAMDBCAT.PERSNL.JOB_CORPORATE LIKE SAMDBCAT.PERSNL.
PAGE 93
• The row is returned in this example. Both sides are case sensitive. SELECT * FROM T WHERE a = ‘A’ (not casespecific); • A row is not returned in this example. A case sensitive comparison is done since column ‘b’ is case sensitive. SELECT * FROM T WHERE b = ‘A’; • A row is not returned in this example. A case sensitive comparison is done since column ‘b’ is case sensitive.
PAGE 94
Table t has 2 columns named (a,b) and the same data attributes as columns from table t1. • If the select list item is an expression, it must be renamed with an AS clause. An error is returned if expressions are not named. For example: create table t as select a+1 as c from t1 Table t has 1 column named (c) and data attribute of (a+1) create table t as select a+1 from t1 An error is returned, expression must be renamed.
PAGE 95
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 298).
PAGE 96
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 97
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 98
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 99
As a workaround for this issue, change column c1 of table t1 from varchar(2040) to varchar(2000). The primary key length is now 2000 bytes, and the CREATE TRIGGER statement completes successfully. 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 100
((SELECT sequence + 1 from prescription_seq), (SELECT pat_id FROM record WHERE sample.id = record.vital_id), (SELECT phys_id FROM record WHERE sample.id = record.vital_id), (SELECT record.id FROM record WHERE sample.id = record.
PAGE 101
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 299). CREATE VIEW view [(column-name ] [,column-name ...)] AS query-expr [order-by-clause] [WITH [CASCADED] CHECK OPTION] Syntax Description of CREATE VIEW view specifies the name of the view. See “Database Object Names” (page 230). (column-name [,column-name ]...) specifies names for the columns of the view.
PAGE 102
Considerations for CREATE VIEW • • You can specify GROUP BY using ordinals to refer to the relative position within the SELECT list. For example, GROUP BY 3, 2, 1. Dynamic parameters are not allowed. Reserved View Names View names prefixed by the name of a user metadata table are reserved. You cannot create views with such names.
PAGE 103
The SELECT statement reads rows in table t in READ UNCOMMITTED access. The same behavior would occur with this SELECT statement: Select * from t for read uncommitted access; Multiple Tables in a View Create view v as select * from t1, t2 for serializable access; Select * from v; The SELECT statement reads rows in tables t1 and t2 in SERIALIZABLE access.
PAGE 104
Create view v as select a from t order by a; Select * from v x, v y; Or this INSERT statement: Insert into t1 select * from v; In these two examples, the ORDER BY clause is ignored during DML processing because the first appears as part of a derived table and the second as a subquery selects, both created after the view expansion.
PAGE 105
CREATE VIEW SALES.MYVIEW1 AS SELECT ordernum, qty_ordered FROM SALES.ODETAIL; • This example creates a view with a column list: CREATE VIEW SALES.MYVIEW2 (v_ordernum, t_partnum) AS SELECT v.ordernum, t.partnum FROM SALES.MYVIEW1 v, SALES.ODETAIL t; • This example creates a view from two tables by using an INNER JOIN: CREATE VIEW MYVIEW4 (v_ordernum, v_partnum) AS SELECT od.ordernum, p.partnum FROM SALES.ODETAIL OD INNER JOIN SALES.PARTS P ON od.partnum = p.
PAGE 106
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. Neoview SQL provides searched DELETE—deletes rows whose selection depends on a search condition.
PAGE 107
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 230).
PAGE 108
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 109
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 110
delete from table1 where a in (select a from table1 where b > 200) 110 SQL Statements
PAGE 111
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 230). 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 “Database Object Names” (page 230). Considerations for DROP INDEX For indexes on materialized views, see “Indexes and Materialized Views” (page 66).
PAGE 112
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 113
DROP MVGROUP Statement • • • “Syntax Description of DROP MVGROUP” (page 113) “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 269). 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 114
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 291). 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 115
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 296). DROP SYNONYM alternate-name Syntax Description of DROP SYNONYM alternate-name specifies the name of the synonym. See “Database Object Names” (page 230).
PAGE 116
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 230). DROP [VOLATILE] TABLE table [RESTRICT|CASCADE] Syntax Description of DROP TABLE table is the name of the table to delete.
PAGE 117
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 298). 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 Object Names” (page 230).
PAGE 118
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 299). 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 119
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 257).
PAGE 120
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 121
--- 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 122
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 389). NOTE: Formatting changes to the EXPLAIN output data might occur in the future.
PAGE 123
with a letter. When you refer to the prepared query in a SELECT statement, you must use uppercase.
PAGE 124
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 125
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 126
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 127
------------------------------------------------------------------ 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 128
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 129
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 130
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 131
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 132
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 133
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 134
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.USR CONNRULE NCI TDM_Default_DataSource ROLE.HR --- SQL operation complete. SQL> The query plan and SQL text are turned on (represented by 1 in the output) for the SALESQUERIES service.
PAGE 135
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. See “Privileges” (page 288). GRANT { privilege [,privilege]... | ALL [PRIVILEGES] } ON [TABLE] object TO {grantee [,grantee ]...
PAGE 136
grantee is authid authid 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. authid is not case-sensitive. GRANT allows two special authorization IDs called PUBLIC and SYSTEM. • • PUBLIC grants the privilege to all present and future authorization IDs. SYSTEM specifies the implicit grantor of privileges to the creator of objects.
PAGE 137
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. See “Privileges” (page 288). GRANT EXECUTE ON [PROCEDURE] procedure-ref TO {grantee [,grantee ]... } [WITH GRANT OPTION] procedure-ref is: [schema-name.
PAGE 138
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 139
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. See “Privileges” (page 288). GRANT {privilege [,privilege]... | all [PRIVILEGES] } ON SCHEMA schema-name TO {grantee [,grantee ]...
PAGE 140
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 141
TO {grantee [,grantee]... } specifies one or more roles to whom you grant privileges. grantee is authid authid 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. authid is not case-sensitive. GRANT SCHEMA allows two special authorization IDs called PUBLIC and SYSTEM. • • PUBLIC grants the privilege to all present and future authorization IDs.
PAGE 142
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 143
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]... insert-source is: query-expr [order-by-clause] [access-clause] | DEFAULT VALUES Syntax Description of INSERT NOMVLOG specifies that the operation will not be recorded in the log table.
PAGE 144
to form a new row. If you specify DEFAULT VALUES, you cannot specify a column list. You can use DEFAULT VALUES only when all columns in table have default values. Considerations for INSERT Authorization Requirements INSERT requires authority to read and write to the table or view receiving the data and authority to read tables or views specified in the query expression (or any of its subqueries) in the INSERT statement.
PAGE 145
Inserting Character Values Any character string data type is compatible with all other character string data types that have the same character set. For fixed length, an inserted value shorter than the column length is padded on the right with blank characters of the appropriate character set (for example, ISO88591 blanks (HEX20). If the value is longer than the column length, string truncation of nonblank trailing characters returns an error, and the truncated string is not inserted.
PAGE 146
--- 1 row(s) inserted. Unlike the previous example, the insert source of this statement does not contain a value for the CREDIT column, which has a default value. As a result, this INSERT must include the column name list. This SELECT statement shows the default value 'C1' for CREDIT: SELECT * FROM sales.customer WHERE custnum = 1120; CUSTNUM ------1120 CUSTNAME -------------EXPERT MAILERS STREET -----------------5769 N. 25TH PL POSTCODE -------85016 CREDIT -----C1 --- 1 row(s) selected.
PAGE 147
Examples of Self-Referencing Inserts • This is an example of a self-referencing insert: insert into table1 select pk+?, b, c from table1 • This is an example of a self-referencing insert where the target of the insert, table1, is also used in a subquery of the insert-source: insert into table1 select a+16, b, c from table2 where table2.b not in (select b from table1 where a > 16) The source table is not affected by the insert.
PAGE 148
LOCK TABLE Statement • • • “Syntax Description of LOCK TABLE” “Considerations for LOCK TABLE” “Examples of LOCK TABLE” The LOCK TABLE statement locks all partitions of 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. Locks are held until the end of the transaction. See “Database Integrity and Locking” (page 27). LOCK TABLE is a Neoview SQL extension.
PAGE 149
at the end of statement execution. When you issue a LOCK TABLE statement without turning off AUTOCOMMIT, Neoview SQL locks the table temporarily, commits the transaction at the end of the LOCK TABLE statement and releases the locks. If you use LOCK TABLE, turn off AUTOCOMMIT by using the SET TRANSACTION statement. See “Effect of AUTOCOMMIT Option” (page 29). Indexes LOCK TABLE attempts to lock all indexes of any table it locks.
PAGE 150
MERGE INTO Statement • • • “Syntax Description of MERGE INTO” “Considerations for MERGE INTO” “Example of MERGE” (page 152) The MERGE INTO statement:. • Updates a table if the row exists and inserts into a table if the row does not exist. This is upsert functionality. • Updates (merges) matching rows from one table to another.
PAGE 151
This example updates column b if a is found. If a is not found, nothing is done. 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 if the table has triggers, materialized views, or constraints. • • Merge is not allowed with SET ON ROLLBACK.
PAGE 152
Example of MERGE 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 (SELECT * FROM t1) z(a,b) on a = z.a WHEN MATCHED THEN UPDATE SET b = z.b WHEN NOT MATCHED THEN INSERT VALUES (z.a, z.
PAGE 153
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 154
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 119).
PAGE 155
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. See “Privileges” (page 288). REVOKE [GRANT OPTION FOR] {privilege [,privilege ]...
PAGE 156
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. REVOKE allows two special authorization IDs called PUBLIC and SCHEMA: • PUBLIC grants the privilege to all present and future authorization IDs. • SYSTEM specifies the implicit grantor of privileges to the creator of objects.
PAGE 157
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. See “Privileges” (page 288). REVOKE [GRANT OPTION FOR] EXECUTE ON [PROCEDURE] procedure-ref FROM {grantee [,grantee ]... } [RESTRICT | CASCADE] procedure-ref is: [schema-name.
PAGE 158
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 159
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 “Privileges” (page 288). REVOKE [GRANT OPTION FOR] {privilege [,privilege ] ...| all [PRIVILEGES ]} ON SCHEMA schema-name FROM {grantee [,grantee]...
PAGE 160
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 161
FROM {authid [,authid]} specifies one or more roles from whom you revoke privileges. 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. REVOKE SCHEMA allows two special authorization IDs called PUBLIC and SCHEMA: • PUBLIC grants the privilege to all present and future authorization IDs.
PAGE 162
ROLLBACK WORK Statement • • • “Syntax Description of REVOKE SCHEMA” “Considerations for ROLLBACK WORK” “Example 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 28). Syntax Description of ROLLBACK WORK ROLLBACK [WORK] WORK is an optional keyword that has no effect.
PAGE 163
SELECT Statement • • • • • • • • “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” The SELECT statement is a DML statement that retrieves values from tables, views, and derived tables determined by the evaluation of query expressions, or joined tables.
PAGE 164
order-by-clause is: [ORDER BY {colname | colnum} [ASC[ENDING] | DESC[ENDING]] [,{colname | colnum} [ASC[ENDING] | DESC[ENDING]]]...] [access-clause] [mode-clause] access clause is: [FOR] access-option ACCESS access-option is: READ UNCOMMITTED | READ COMMITTED | SERIALIZABLE | REPEATABLE READ | SKIP CONFLICT mode-clause is: IN {SHARE | EXCLUSIVE} MODE select-list is: * | select-sublist [,select-sublist]... select-sublist is: corr.* | [corr.
PAGE 165
percent-size is: percent-result PERCENT [ROWS | {CLUSTERS OF number-blocks BLOCKS}] | BALANCE WHEN condition 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]...
PAGE 166
single-col [[AS] name] specifies a column. 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]...
PAGE 167
(delete-statement [RETURN select-list]) [AS] corr [( col-expr-list)] enables an application to read and delete rows with a single operation. For the syntax of delete-statement, see the “DELETE Statement” (page 106). 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.
PAGE 168
RETURN old.empno,old.salary,new.salary, (new.salary - old.salary) AS emp (empno, oldsalary, newsalary, increase). (insert-statement) [AS] corr [(col-expr-list)] For the syntax of insert-statement, see the “INSERT Statement” (page 143). [AS] corr [(col-expr-list)] specifies an optional correlation name and an optional column list.
PAGE 169
table-ref LEFT JOIN table-ref join-spec joins rows that satisfy the condition in the join-spec clause, plus rows from the left table-ref that do not satisfy the condition. table-ref RIGHT JOIN table-ref join-spec joins rows that satisfy the condition in the join-spec clause, plus rows from the right table-ref that do not satisfy the condition. table-ref FULL OUTER JOIN table-ref join-spec combines the results of both left and right outer joins.
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
SELECT * FROM job UNION SELECT * FROM job_corporate; 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
FROM sales.parts P, sales.odetail OD WHERE OD.partnum = P.partnum AND OD.ordernum IN (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.
PAGE 183
select a 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 291). 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” “Examples 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
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. The default isolation level is READ COMMITTED. access-mode specifies the type of data access that the transaction requires, depending on whether the transaction changes the database. If the isolation-level is READ UNCOMMITTED, you cannot specify READ WRITE. The default access-mode is READ WRITE.
PAGE 189
autobegin-option specifies whether transactions should be initiated 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. It cannot be combined with other functions of SET TRANSACTION.
PAGE 190
Restrictions for the MULTI COMMIT option: • • • • Not compatible with the READ UNCOMMITTED isolation level. Not compatible with READ ONLY access mode. Except for MULTI COMMIT, all of the SET TRANSACTION options are valid for the child transactions and not the logical parent transaction. Not compatible with the NO ROLLBACK option set to ON.
PAGE 191
Effect on Utilities The SET TRANSACTION statement has no effect on the utility statements. The SET TRANSACTION statement does set attributes for transactions for UPDATE STATISTICS. Examples of SET TRANSACTION • Set the isolation level of a transaction that performs deletes, inserts, and updates: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --- SQL operation complete. BEGIN WORK; --- SQL operation complete. DELETE FROM persnl.employee WHERE empnum = 23; --- 1 row(s) deleted. INSERT INTO persnl.
PAGE 192
SHOWDDL Statement • • • “Syntax Description of SHOWDDL” “Considerations for SHOWDDL” “Examples of SHOWDDL” 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. If object-name is not fully qualified, SHOWDDL uses the default schema for the session.
PAGE 193
will be displayed as: GRANT GRANT GRANT GRANT GRANT SELECT ON TABLE sch.t1 TO "role_user1"; UPDATE ON TABLE sch.t1 TO "role_user1"; DELETE ON TABLE sch.t1 TO "role_user1"; INSERT ON TABLE sch.t1 TO "role_user1"; REFERENCES ON TABLE sch.t1 TO "role_user1"; GRANT GRANT GRANT GRANT GRANT SELECT ON TABLE sch.t1 TO "role_user2"; UPDATE ON TABLE sch.t1 TO "role_user21"; DELETE ON TABLE sch.t1 TO "role_user21"; INSERT ON TABLE sch.t1 TO "role_user2"; REFERENCES ON TABLE sch.
PAGE 194
salary numeric (10,2) default 1000 ) hash partition by (empl_no) max table size 2000; create index empl_dept_no on empl (dept_no) hash partition by (dept_no); create mv empl_mv refresh on request initialize on refresh store by (dept_no) hash partition by (dept_no) as select max(salary) as salary, dept_no from empl group by dept_no; alter table empl add constraint empl_salary_check check (salary >= 1000); alter table empl add constraint empl_dept_no_check check (dept_no > 0); alter table empl add constraint
PAGE 195
-- showddl output -- Schema level privileges -- GRANT SELECT ON SCHEMA SCH TO “ROLE.READER”; CREATE TABLE SCH.DEPT ( DEPT_NO INT NO DEFAULT -- NOT NULL , DEPT_NAME VARCHAR(20) CHARACTER SET ISO88591 NO DEFAULT -- NOT NULL , CONSTRAINT SCH.DEPT_849937374_7746 PRIMARY KEY (DEPT_NO ASC) , CONSTRAINT SCH.DEPT_985137374_7746 CHECK (SCH.DEPT.DEPT_NO IS NOT NULL AND SCH.DEPT.
PAGE 196
TABLE Statement • • “Considerations for TABLE” “Examples of TABLE” The TABLE statement is equivalent to the query specification SELECT * FROM table. TABLE table table names the user table or view. Considerations for TABLE Relationship to SELECT Statement The result of the TABLE statement is one form of a simple-table, which refers to the definition of a table reference within a SELECT statement. See the “SELECT Statement” (page 163).
PAGE 197
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 198
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 163). [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 199
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 292). 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 200
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 201
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 202
(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 203
UPDATE STATISTICS Statement NOTE: HP recommends that you update statistics by using the automated UPDATE STATISTICS stored procedures. The syntax for UPDATE STATISTICS is included here for reference. For information about automated UPDATE STATISTICS, see the Neoview Database Administrator's Guide.
PAGE 204
If you specify column-group-list, only columns in the group list are deleted. ON column-group-list specifies one or more groups of columns for which to generate histogram statistics with the option of clearing the histogram statistics. You must use the ON clause to generate statistics 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 206).
PAGE 205
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 generates statistics for histograms the optimizer has requested, but do not exist. In all cases, automation must be enabled for NECESSARY COLUMN[S] to generate statistics.
PAGE 206
Considerations for UPDATE STATISTICS Physical Statistics Physical statistics (index level, nonempty block count, and EOF) are generated for UPDATE STATISTICS statements unless you use the CLEAR option. Using Statistics Use UPDATE STATISTICS to collect and save statistics on columns. The SQL compiler uses histogram statistics to determine the selectivity of predicates, indexes, and tables.
PAGE 207
Column Lists and Access Plans Generate statistics for columns most often used in data access plans for a table—that is, the primary key, indexes defined on the table, and any other columns frequently referenced in predicates in WHERE or GROUP BY clauses of queries issued on the table. Use the EVERY COLUMN option to generate histograms for every individual column or multicolumns that make up the primary key and indexes. The EVERY KEY option generates histograms that make up the primary key and indexes.
PAGE 208
VALUES Statement • • “Considerations for VALUES” “Examples of VALUES” The VALUES statements starts with the VALUES keyword followed by a sequence of row value constructors, each of which is enclosed in parenthesis. It displays the results of the evaluation of the expressions and the results of row subqueries within the row value constructors. VALUES (row-value-const) [, (row-value-const)]... row-value-const is: row-subquery | {expression | NULL} [,{expression | NULL}...
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” “MAINTAIN MVGROUP” (page 215) “Examples of MAINTAIN” The MAINTAIN command is a syntax-based utility that can be executed in DB Admin, 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.
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
— — — — — • • • Update of mvlog statistics Refresh of all MVs on that table Reorg of all MVs on the table Reorg of all indexes on the MVs Update of statistics for all MVs referencing 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.
PAGE 213
other-options CONTINUE ON ERROR If any of the maintain tasks return an error, that error is returned, and execution continues to the next task. The default is to continue on error. 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 enabled to be executed without actually executing them.
PAGE 214
NOTE: The MAINTAIN command is not supported for use with volatile tables. RATE percentage specifies that the reorg operation should spend a percentage of its time doing the reorg and the rest of its time delaying. percentage is an integer in the range of 1 through 100. If this option is not specified, the default value of 40 is used.
PAGE 215
MAINTAIN MVGROUP MAINTAIN MVGROUP mvgroupname, REFRESH mv-refresh-option mv-refresh-option is: [RECOMPUTE | CANCEL] [{WITH | WITHOUT |DO ONLY} MVLOG CLEANUP] mvgroupname is the ANSI name of the materialized view group to be maintained. It can be a one or two-part name. See “Database Object Names” (page 230). The REFRESH utility can be applied to both incremental materialized views (MVs) and recomputed MVs. The REFRESH utility optimizes the refresh of MVGROUP.
PAGE 216
• Reorganizes index T1_090_2: maintain index T1_090_2, reorg; • Shows the internal SQL statements that will be used for each task: maintain table t1_090, all, display detail; • Disables all tasks that are allowed to be disabled (reorg table, index, update statistics): maintain table t1_090, all, disable; The status will show up as Disabled for the tasks that were disabled by the previous command.
PAGE 217
Partition: Location: Status: Segment: Cpu: PctDone: NSK_SYSTEM_ZSDCNHM6_BSQJPG00 \NSK.$SYSTEM.ZSDCNHM6.BSQJPG00 NOT_INITIATED \NSK 0 0 TableName: Partition: Location: Status: Segment: Cpu: PctDone: CAT.SCH.T2 NSK_SYSTEM_ZSDCNHM6_SFS6PG00 \NSK.$SYSTEM.ZSDCNHM6.SFS6PG00 NOT_INITIATED \NSK 0 0 Total elapsed Time: 00:00:00.000 Task: REORG_TABLE Status: Ended ET: 00:00:00.031 --- SQL operation complete.
PAGE 218
POPULATE INDEX Utility • • • “Syntax Description of POPULATE INDEX” “Considerations for POPULATE INDEX” “Examples of POPULATE INDEX” POPULATE INDEX is a 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. You cannot qualify index with its schema name.
PAGE 219
• • If the POPULATE INDEX operation fails unexpectedly, you may need to drop the index again and recreate and repopulate, or run recover. Contact HP support for details on how to run recover. Online POPULATE INDEX reads the audit trail to replay updates by allowing read/write access. If you plan to create many indexes in parallel or if you have a high level of activity on the audit trail, you should consider using the OFFLINE option.
PAGE 220
PURGEDATA Utility • • • “Syntax Description of PURGEDATA” “Considerations for PURGEDATA” “Example 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 object [IGNORE_TRIGGER] [NOLOG] Syntax Description of PURGEDATA object is the name of the table from which to purge the data. See “Database Object Names” (page 230).
PAGE 221
the data. Error 8551 with an accompanying file system error 59 or error 1071 is returned in this case. Example of PURGEDATA • This example purges the data in the specified table. If the table has indexes, their data is also purged. PURGEDATA myschema.
PAGE 222
PAGE 223
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 224
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 225
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 226
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 227
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 228
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 229
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” “Tables” “Views” Ownership In Neoview SQL, the creator of a schema owns all the objects defined in the schema and has all privileges on the objects defined in the schema.
PAGE 230
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 229). Logical Names for SQL Objects You may refer to an SQL table, view, or procedure by using a one-part or two-part logical name, also called an ANSI name: schema-name.
PAGE 231
Data Types Neoview SQL data types are character, datetime, interval, or numeric (exact or approximate): “Character String Data Types” (page 235) Fixed-length and variable-length character data types. “Datetime Data Types” (page 237) DATE, TIME, and TIMESTAMP data types. “Interval Data Types” (page 239) Year-month intervals (years and months) and day-time intervals (days, hours, minutes, seconds, and fractions of a second). “Numeric Data Types ” (page 241) Exact and approximate numeric data types.
PAGE 232
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 233
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 234
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 235
— — • • 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 236
CHAR[ACTER] VARYING (length) [CHARACTERS] [char-set] [UPSHIFT] [[NOT]CASESPECIFIC] 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 237
Collations A collation contains rules for a collating sequence (the sequence in which characters are ordered for sorting), case, and character class and character string equivalence. To be compared, character strings must be from the same character set. When two strings are compared, the comparison is made with a temporary copy of the shorter string that has been padded on the right with blanks to have the same length as the longer string.
PAGE 238
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 239
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 240
Considerations for Interval Data Types Adding or Subtracting Imprecise Interval Values Adding or subtracting an interval that is any multiple of a MONTH, a YEAR, or a combination of these may result in a runtime error.
PAGE 241
Numeric Data Types Numeric data types are either exact or approximate. A numeric data type is compatible with any other numeric data type, but not with character, datetime, or interval data types.
PAGE 242
FLOAT [( precision )] specifies an approximate numeric column. The column stores floating-point numbers and designates from 1 through 54 bits of precision. The range is from +/2.2250738585072014e-308 through +/-1.7976931348623157e+308 stored in 8 bytes. An IEEE FLOAT precision data type is stored as an IEEE DOUBLE, that is, in 8 bytes, with the specified precision. The default precision is 54. REAL specifies a 4-byte approximate numeric column.
PAGE 243
Expressions An SQL value expression, referred to as an expression, can evaluate to a value with one of these: “Character Value Expressions” (page 243) Operands can be combined with the concatenation operator (||). Example: 'HOUSTON,' ||' TEXAS' “Datetime Value Expressions” (page 245) Operands can be combined in specific ways with arithmetic operators.
PAGE 244
Examples of Character Value Expressions These are examples of character value expressions: 244 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.
PAGE 245
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 246
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: Datetime Expression Description Result Data Type CURRENT_DATE + INTERVAL '1' The sum of the current date and an interval value of one day. DAY DATE CURRENT_DATE + est_complete The sum of the current date and the interval value in column EST_COMPLETE.
PAGE 247
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 248
-------------------------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 249
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 250
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 251
Operands Result type Date – Date Interval Time + Interval or Interval + Time Time Time + Numeric or Numeric + Time Time Time - Number Time 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
PAGE 252
The next example uses these values in the PROJECT table: PROJCODE START_DATE SHIP_TIMESTAMP EST_COMPLETE 1000 2007-04-10 2007-04-21:08:15:00.0000 15 2000 2007-06-10 2007-07-21:08:30:00.0000 30 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.
PAGE 253
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 254
Considerations for Numeric Value Expressions Order of Evaluation 1. 2. 3. 4. 5. Expressions within parentheses Unary operators Exponentiation Multiplication and division Addition and subtraction Operators at the same level are evaluated from left to right for all operators except exponentiation. Exponentiation operators at the same level are evaluated from right to left. For example, X + Y + Z is evaluated as (X + Y) + Z, whereas X ** Y ** Z is evaluated as X ** (Y ** Z).
PAGE 255
Conversion of Numeric Types for Arithmetic Operations Neoview SQL automatically converts between floating-point numeric types (REAL and DOUBLE PRECISION) and other numeric types. All numeric values in the expression are first converted to binary, with the maximum precision needed anywhere in the evaluation.
PAGE 256
Examples of Numeric Value Expressions These are examples of numeric value expressions: 256 -57 Numeric literal. salary * 1.10 The product of the values in the SALARY column and a numeric literal. unit_price * qty_ordered The product of the values in the UNIT_PRICE and QTY_ORDERED columns. 12 * (7 - 4) An expression whose operands are numeric literals. COUNT (DISTINCT city) Function applied to the values in a column.
PAGE 257
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 258
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 259
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. Indexes are transparent to DML syntax. 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 260
Keys Neoview SQL supports these types of keys: • • • • “Clustering Keys” “Hash Partition Keys” (page 260) “Index Keys” “Primary Keys” Clustering Keys Every table has a clustering key, which is the set of columns that determine the order of the rows on disk. Neoview SQL organizes records of a table or index by using a b-tree based on this clustering key. Therefore, the values of the clustering key act as logical row-ids.
PAGE 261
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 261) A series of characters enclosed in single quotes. Example: 'Planning' “Datetime Literals” (page 262) Begins with keyword DATE, TIME, or TIMESTAMP and followed by a character string.
PAGE 262
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 235).
PAGE 263
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 264
Interval Literals • • “Considerations for Interval Literals” “Examples of Interval Literals” 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).
PAGE 265
'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 266
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 267
Examples of Numeric Literals • 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 268
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 269
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 270
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 271
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 272
Partitions Typically, there is a one-to-one correspondence between a table definition and a physical file. However, large tables or tables with special performance requirements might require partitioning into multiple physical files. A partition is the part of a table or index that resides on a single disk volume. Each table or index consists of at least one partition. A nonpartitioned table or index consists of exactly one partition. A partitioned table or index consists of more than one partition.
PAGE 273
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 273) Determines whether a sequence of values is within a range of sequences of values.
PAGE 274
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 275
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 276
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 277
(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 278
... --- 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 279
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 280
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 281
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 282
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 283
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 284
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 285
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 243).
PAGE 286
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 287
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 288
Privileges These privileges are used by the GRANT and REVOKE statements. For further information on GRANT, see “GRANT Statement” (page 135), “GRANT EXECUTE Statement” (page 137), or “GRANT SCHEMA Statement” (page 139). For further information on REVOKE, see “REVOKE Statement” (page 155), “REVOKE EXECUTE Statement” (page 157), or “REVOKE SCHEMA Statement” (page 159).
PAGE 289
• • • • • INSERT - Can use INSERT statement. UPDATE - Can use UPDATE statement. EXECUTE - Can use EXECUTE 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. Privilege Descriptions for Schemas • • • • • • • • • • • • • • • • • • • • • • • • • • SELECT - Can use SELECT statement. DELETE - Can use DELETE statement. INSERT - Can use INSERT statement.
PAGE 290
• • • • • • DROP_TABLE - Can drop any table (regular or volatile) in the schema. Indirectly allows the grantee to drop any dependent object on the table such as constraints, indexes, triggers, and synonyms. DROP_TRIGGER - Can drop any trigger in the schema. DROP_VIEW - Can drop any regular view in the schema. ALL - Can have all privileges (SELECT, DELETE, INSERT, UPDATE, EXECUTE, and REFERENCES) that apply to the object type.
PAGE 291
Schemas The ANSI SQL:1999 schema name is an SQL identifier that is unique for a given ANSI catalog name. Neoview SQL automatically qualifies the schema name with the current default catalog name. 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 292
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 293
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 294
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 163). 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 295
correlation name is known to other subqueries at the same level, or to inner queries but not to outer queries. If you use the same correlation name at different levels of nesting, an inner query uses the one from the nearest outer level.
PAGE 296
Synonyms A synonym is an alternate name that you create for a table, view, or materialized view to use 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. The advantage of this is to change the object being referenced without changing the query. When a synonym is assigned to an object, the object can be referenced either with the synonym or the actual name. The resultant query will be the same.
PAGE 297
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 298
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 95), “ALTER TRIGGER Statement” (page 53), “DROP TRIGGER Statement” (page 117).
PAGE 299
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 300
PAGE 301
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.
PAGE 302
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 303
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 specified by the user. GENERATED ALWAYS AS IDENTITY indicates that the system will always generate values for this column by default. User-specified values are not allowed with GENERATED ALWAYS AS IDENTITY.
PAGE 304
FORMAT Clause • • • “Considerations for Date Formats” “Considerations for Other Formats” “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 305
-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 306
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 163). SAMPLE is a Neoview SQL extension.
PAGE 307
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 308). BALANCE If you specify a BALANCE expression, Neoview SQL performs stratified sampling.
PAGE 308
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 309
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 310
• 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 311
(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 312
--- 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 313
39 75 ... GRAPHICPRINTER, M3 LASERPRINTER, X1 ... S W ... 20000.00 42000.00 ... --- 30 row(s) selected.
PAGE 314
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 163). SEQUENCE BY is a Neoview SQL extension. SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] | DESC[ENDING]]]...
PAGE 315
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 316
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 317
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 318
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 319
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 233).
PAGE 320
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 321
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 322
• 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 323
• 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 324
PAGE 325
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 326
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 327
“SUBSTRING/SUBSTR Function” (page 459) Extracts a substring from a character string. “TRANSLATE Function” (page 467) Translates a character string from a source character set to a target character set. “TRIM Function” (page 468) Removes leading or trailing characters from a character string. “UCASE Function” (page 469) Upshifts alphanumeric characters. You can also use UPSHIFT or UPPER. “UPPER Function” (page 470) Upshifts alphanumeric characters. You can also use UPSHIFT or UCASE.
PAGE 328
“DAYOFMONTH Function” (page 377) 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 378) Returns an integer value in the range 1 through 7 that represents the corresponding day of the week. “DAYOFYEAR Function” (page 379) Returns an integer value in the range 1 through 366 that represents the corresponding day of the year.
PAGE 329
“DEGREES Function” (page 383) Converts a numeric value expression expressed in radians to the number of degrees. “EXP Function” (page 388) Returns the exponential value (to the base e) of a numeric value expression. “FLOOR Function” (page 393) Returns the largest integer less than or equal to a numeric value expression. “LOG Function” (page 402) Returns the natural logarithm of a numeric value expression. “LOG10 Function” (page 403) Returns the base 10 logarithm of a numeric value expression.
PAGE 330
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 384) Calculates differences between values of a column expression in the current row and previous rows. “DIFF2 Function” (page 386) Calculates differences between values of the result of DIFF1 of the current row and DIFF1 of previous rows.
PAGE 331
“ROWS SINCE CHANGED Function” (page 441) Returns the number of rows counted since the specified set of values last changed. “THIS Function” (page 464) 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 314) and the individual entry for each function.
PAGE 332
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 253).
PAGE 333
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 253). Examples of ACOS • The ACOS function returns the value 3.49044274380724416E-001 or approximately 0.
PAGE 334
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 245).
PAGE 335
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 243).
PAGE 336
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 253). Examples of ASIN • This function returns the value 3.49044414403046400E-001 or approximately 0.
PAGE 337
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 253). Examples of ATAN • This function returns the value 8.72766423249958272E-001 or approximately 0.8727 in radians (which is 50 degrees): ATAN (1.
PAGE 338
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 253). Example of ATAN2 This function returns the value 2.
PAGE 339
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 340
--- 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 341
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 342
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 343
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 344
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 345
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 345).
PAGE 346
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 347
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 253). Example of CEILING This function returns the integer value 3.
PAGE 348
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 349
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 350
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 351
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. For more information on the Neoview character set configurations, see the Neoview Character Sets Administrator's Guide. CODE_VALUE is a Neoview SQL extension. CODE_VALUE(character-value-expression) _character-set character-value-expression is a character string.
PAGE 352
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 353
INSERT INTO stats.
PAGE 354
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 355
----------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 CONVERTTOHEX
PAGE 356
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 357
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 253). 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 358
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 253). Example of COSH This function returns the value 1.88842387716101568E+000, or approximately 1.8884, the hyperbolic cosine of 1.
PAGE 359
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 360
----------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 361
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 365). 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 362
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 363
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 364
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 365
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 361). 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 366
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 472). 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 367
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 325). 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 368
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 325). 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 369
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 325). 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 370
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 371
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 245). DEFAULT | USA | EUROPEAN specifies a format for a datetime value.
PAGE 372
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 373
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 374
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 375
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 245).
PAGE 376
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 245).
PAGE 377
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 245).
PAGE 378
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 245).
PAGE 379
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 245).
PAGE 380
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 381
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 382
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 383
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 253). Examples of DEGREES • This function returns the value 45.0001059971939008 in degrees: DEGREES (0.78540) • This function returns the value of 45.
PAGE 384
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 314). 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 385
--- 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 386
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 314). 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 387
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 384).
PAGE 388
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 253). 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 389
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 389). 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 390
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 391
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 392
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 245) and “Interval Value Expressions” (page 249).
PAGE 393
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 253). Examples of FLOOR This function returns the integer value 2.00000000000000000E+000, represented as a FLOAT data type: FLOOR (2.
PAGE 394
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 245).
PAGE 395
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 396
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 397
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 398
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 314). 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 399
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 404).
PAGE 400
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 401
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 428). LOCATE is a Neoview SQL extension.
PAGE 402
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 253). Example of LOG This function returns the value 6.93147180559945344E-001, or approximately 0.69315: LOG (2.
PAGE 403
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 253). Example of LOG10 This function returns the value 1.39794000867203776E+000, or approximately 1.
PAGE 404
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 405
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 243). 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 406
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 468). 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 243).
PAGE 407
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 408
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 409
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 245).
PAGE 410
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 411
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 245).
PAGE 412
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 245).
PAGE 413
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 314). 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 414
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 314). 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 415
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 314). 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 416
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 314). 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 417
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 314). 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 418
-------------------.000 15527.357 14802.016 15115.012 6036.275 --- 5 row(s) selected.
PAGE 419
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 314). 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 420
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 314). 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 421
.000 241098840.500 219099697.000 228463602.333 36436621.000 --- 5 row(s) selected.
PAGE 422
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 423
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 424
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, new-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 425
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 243).
PAGE 426
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 314). 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 427
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 428
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 401).
PAGE 429
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 253).
PAGE 430
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 245).
PAGE 431
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 253). Examples of RADIANS • Return the value 7.85398150000000000E-001, or approximately 0.78540 in degrees: RADIANS (45) • Return the value 45 in degrees.
PAGE 432
RANK/RUNNINGRANK Function The RANK/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 RANK/RUNNINGRANK. RANK/RUNNINGRANK is a Neoview extension. RUNNINGRANK(expression) | RANK(expression) expression specifies the expression on which to perform the rank. RANK/RUNNINGRANK returns the rank of the expression within the intermediate result table.
PAGE 433
I1 I2 6 null 8 200 • 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.
PAGE 434
--- 8 row(s) selected. Notice that the two NULL values received the same rank. • Return the rank of I2 descending, using the alternative RANK syntax: SELECT I2, RANK (I2) AS RANK FROM cat.sch.seqfcn SEQUENCE BY I2 DESC; 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.
PAGE 435
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 243).
PAGE 436
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 437
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 438
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 439
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 314). 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 440
(EXPR) --------------? 1 2 1 2 (EXPR) --------------0 1 0 1 0 --- 5 row(s) selected.
PAGE 441
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 314). ROWS SINCE CHANGED is a Neoview SQL extension.
PAGE 442
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 243). 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 443
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 468). 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 243).
PAGE 444
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 314). RUNNINGAVG is a Neoview SQL extension. RUNNINGAVG (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 445
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 314). 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 446
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 314). RUNNINGMAX is a Neoview SQL extension. RUNNINGMAX (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 447
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 314). RUNNINGMIN is a Neoview SQL extension. RUNNINGMIN (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 448
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 314). RUNNINGSTDDEV is a Neoview SQL extension. RUNNINGSTDDEV (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 449
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 314). RUNNINGSUM is a Neoview SQL extension. RUNNINGSUM (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 450
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 314). RUNNINGVARIANCE is a Neoview SQL extension. RUNNINGVARIANCE (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 451
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 245).
PAGE 452
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 253).
PAGE 453
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 253). 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 454
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 253). Example of SINH This function returns the value 1.60191908030082560E+000, or approximately 1.6019, the hyperbolic sine of 1.25: SINH (1.
PAGE 455
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 456
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 253). Example of SQRT This function returns the value 5.19615242270663232E+000, or approximately 5.
PAGE 457
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 458
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 459
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 460
• • If the start position is greater than the length of the character expression, an empty string ('') is returned. The resulting substring is always of type VARCHAR. If the source character string is an upshifted CHAR or VARCHAR string, the result is an upshifted VARCHAR type.
PAGE 461
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 462
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 253). 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 463
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 253). Example of TANH • This function returns the value 8.48283639957512960E-001 or approximately 0.8483, the hyperbolic tangent of 1.
PAGE 464
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 439). THIS is a Neoview SQL extension. THIS (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 465
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 466
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 467
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 468
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 469
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 470
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 471) or “UCASE Function” (page 469).
PAGE 471
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 472
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 366). 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 473
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 474
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 475
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 476
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 245). 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 477
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 478
PAGE 479
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 480
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 481
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 482
SELECT deptnum, empnum, AVG(SALARY) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 483
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 484
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 485
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 486
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 487
SELECT deptnum, empnum, MAX(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 488
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 489
SELECT deptnum, empnum, MIN(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 490
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 491
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 492
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 493
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 494
SELECT deptnum, empnum, SUM (salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 495
VARIANCE Window 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 496
PAGE 497
A Mapping Tables The following tables list mappings for the UCS2 character set. Characters not listed in these tables use themselves as their uppercase mappings.
PAGE 498
Table A-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) 498 x L (x) x L(x) x L(x) x L(x) x L(x) x L(x) 00C6 00E6 01B2 028B 0420 0440 0556 0586 1EA6 1EA7 1FBB 1F71 00C7 00E7 01B3 01B4 0421 0441 10A0 10D0 1EA8 1EA9 1FBC 1FB3 00C8 00E8 01B5 01B6 0422 0442 10A1 10D1 1EAA 1EAB 1FC8 1F72 00C9 00E9 01B7 0292 0423 0443 10A2 10D2 1EAC 1EAD 1FC9 1F73 00CA 00EA 01B8 01B9 0424 0444 10A3 10D3 1EAE 1EAF 1FCA 1F74 00CB 00EB
PAGE 499
Table A-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) 0118 0119 0202 0203 0496 0497 10C3 10F3 1EEE 1EEF 216F 217F 011A 011B 0204 0205 0498 0499 10C4 10F4 1EF0 1EF1 24B6 24D0 011C 011D 0206 0207 049A 049B 10C5 10F5 1EF2 1EF3 24B7 24D1 011E 011F 0208 0209 049C 049D 1E00 1E01 1EF4 1EF5 24B8 24D2 0120 0121 020A 020B 049E 049F 1E02 1E03 1EF6 1EF7 24B9 24D3 0122 0123 020C
PAGE 500
Table A-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) 0162 0163 03A4 03C4 04E6 04E7 1E42 1E43 1F48 1F40 FF2A FF4A 0164 0165 03A5 03C5 04E8 04E9 1E44 1E45 1F49 1F41 FF2B FF4B 0166 0167 03A6 03C6 04EA 04EB 1E46 1E47 1F4A 1F42 FF2C FF4C 0168 0169 03A7 03C7 04EE 04EF 1E48 1E49 1F4B 1F43 FF2D FF4D 016A 016B 03A8 03C8 04F0 04F1 1E4A 1E4B 1F4C 1F44 FF2E FF4E 016C 016D 03A9
PAGE 501
Table A-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) 006F 004F 01A3 01A2 03EB 03EA 04F3 04F2 1E91 1E90 1F82 1F8A 0070 0050 01A5 01A4 03ED 03EC 04F5 04F4 1E93 1E92 1F83 1F8B 0071 0051 01A8 01A7 03EF 03EE 04F9 04F8 1E95 1E94 1F84 1F8C 0072 0052 01AD 01AC 03F0 039A 0561 0531 1E9B 1E60 1F85 1F8D 0073 0053 01B0 01AF 03F1 03A1 0562 0532 1EA1 1EA0 1F86 1F8E 0074 0054 01B4 01B3 03F2 03A3 0563 0533 1E
PAGE 502
Table A-2 One-to-One UCS2 Mappings (continued) 502 x U (x) x U(x) x U(x) x U(x) x U(x) x U(x) 00F9 00D9 01FB 01FA 044E 042E 0582 0552 1EE1 1EE0 2173 2163 00FA 00DA 01FD 01FC 044F 042F 0583 0553 1EE3 1EE2 2174 2164 00FB 00DB 01FF 01FE 0451 0401 0584 0554 1EE5 1EE4 2175 2165 00FC 00DC 0201 0200 0452 0402 0585 0555 1EE7 1EE6 2176 2166 00FD 00DD 0203 0202 0453 0403 0586 0556 1EE9 1EE8 2177 2167 00FE 00DE 0205 0204 0454 0404 1E01 1E
PAGE 503
Table A-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) 013C 013B 03AD 0388 0497 0496 1E3F 1E3E 1F31 1F39 24E7 24CD 013E 013D 03AE 0389 0499 0498 1E41 1E40 1F32 1F3A 24E8 24CE 0140 013F 03AF 038A 049B 049A 1E43 1E42 1F33 1F3B 24E9 24CF 0142 0141 03B1 0391 049D 049C 1E45 1E44 1F34 1F3C FF41 FF21 0144 0143 03B2 0392 049F 049E 1E47 1E46 1F35 1F3D FF42 FF22 0146 0145 03B3 0393 04A1 04A0 1E49 1E48 1F
PAGE 504
Table A-3 Two-Character UCS2 Mapping (continued) 504 Titlecase characters Two-character uppercase expansions 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 0x0399 0x1F88 0x1F08 0x0399 0x1F89 0x1F09 0x0399 0x1F8A
PAGE 505
Table A-3 Two-Character UCS2 Mapping (continued) Titlecase characters Two-character uppercase expansions 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 0x0399 0x1FAC 0x1F6C 0x0399 0x1FAD 0x1F6D 0x0399 0x1FAE 0x1F6E
PAGE 506
Table A-3 Two-Character UCS2 Mapping (continued) Titlecase characters Two-character uppercase expansions 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 A-4 lists UCS2 characters with three-character uppercase mapping.
PAGE 507
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. NOTE: In Neoview SQL, ABSOLUTE, DATA, EVERY, INITIALIZE, OPERATION, PATH, SPACE, STATE, STATEMENT, STATIC, and START are not reserved words.
PAGE 508
Table B-5 Reserved SQL Identifiers — E EACH ELSE ELSEIF END END-EXEC EQUALS ESCAPE EXCEPT EXCEPTION EXEC EXECUTE EXISTS EXTERNAL EXTRACT Table B-6 Reserved SQL Identifiers — F FALSE FETCH FIRST FLOAT FOR FOREIGN FOUND FRACTION FREE FROM FULL FUNCTION GO GOTO Table B-7 Reserved SQL Identifiers — G GENERAL GET GLOBAL GRANT GROUP GROUPING Table B-8 Reserved SQL Identifiers — H HAVING HOST HOUR Table B-9 Reserved SQL Identifiers — I IDENTITY IF IGNORE IMMEDIATE IN INDIC
PAGE 509
Table B-14 Reserved SQL Identifiers — N NAMES NATIONAL NATURAL NCHAR NCLOB NEW NEXT NO NONE NOT NULL NULLIF NUMERIC Table B-15 Reserved SQL Identifiers — O OCTET_LENGTH OF OFF OID OLD ON ONLY OPEN OPERATORS OPTION OPTIONS OR ORDER ORDINALITY OTHERS OUT OUTER OUTPUT OVERLAPS Table B-16 Reserved SQL Identifiers — P PAD PARAMETER PARAMETERS PARTIAL PENDANT POSITION POSTFIX PRECISION PREFIX PREORDER PREPARE PRESERVE PRIMARY PRIOR PRIVATE PRIVILEGES PROCEDURE PROT
PAGE 510
Table B-20 Reserved SQL Identifiers — T (continued) TIMEZONE_MINUTE TO TRAILING TRANSACTION TRANSLATE TRANSLATION TREAT TRIGGER TRIM TRANSPOSE TRUE Table B-21 Reserved SQL Identifiers — U UNDER UNION UNIQUE UNKNOWN UNNEST UPDATE UPPER UPSHIFT USAGE USER VARIABLE VARYING WHILE USING Table B-22 Reserved SQL Identifiers — V VALUE VALUES VARCHAR VIEW VIRTUAL VISIBLE Table B-23 Reserved SQL Identifiers — W WAIT WHEN WHENEVER WHERE WITH WITHOUT WORK WRITE Table B-24 Reserved
PAGE 511
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 512
PAGE 513
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 514
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 515
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 516
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 TIMESTAMP data types • F051-05 Explicit CAST between datetime types and character types • F051-06 CURRENT_DATE Neoview SQL does not su
PAGE 517
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 518
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 519
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 520
Features Supported in Mode_Special_2 • NOT CASESPECIFIC By default, all columns and literals are set to NOT CASESPECIFIC. If you are using this special mode, you must create and access tables in the same mode. Otherwise, you could encounter data corruption or runtime errors. • Input Date Formats The FORMAT clause supports these formats for a DATE literal: YYYY-MM-DD MM/DD/YYYY YY/MM/DD YYYY/MM/DD YYYYMMDD DD.MM.
PAGE 521
Multiplication op1 * op2 Result scale = op1scale + op2scale Division op1 / op2 Result scale = MAXOF(op1, op2) Examples 10*6 => 10.0*6 => 10.00*6.0=> • 60 10/6 => 2 60.0 10.0/6 => 1.7 60.000 10.00/6 => 1.67 Ignore Duplicate Keys The “Ignore Duplicate Keys” feature simulates SET table support and provides the same semantics with one exception: Rows with duplicate keys are ignored during insert operations.
PAGE 522
PAGE 523
Index A ABS function examples of, 332 syntax diagram of, 332 Access options summary of, 25 DELETE statement use of, 107 DML statements use of, 25 INSERT statement use of, 143 READ COMMITTED, 26 READ UNCOMMITTED , 26 REPEATABLE READ, 26 SELECT statement use of, 171 SERIALIZABLE, 26, 27, 33 SKIP CONFLICT, 26 UPDATE statement use of, 199 Access privileges stored procedures, 137 tables, 135, 139 ACOS function examples of, 333 syntax diagram of, 333 ADD_MONTHS function examples of, 334 syntax diagram of, 334 AFT
PAGE 524
C CALL statement considerations for, 56 description for, 56 examples of, 57 required privileges, 56 syntax diagram of, 56 usage restrictions, 56 CASE expression data type of, 343 examples of, 343 searched CASE form, 342 syntax diagram of, 342 CAST expression data type conversion, 345 examples of, 345 syntax diagram of, 345 valid type combinations, 345 CEILING function examples of, 347 syntax diagram of, 347 CHAR data type, 235, 236 CHAR function examples of, 348 syntax diagram of, 348 CHAR VARYING data type
PAGE 525
FOREIGN KEY, 46, 77, 227 limits, 511 NOT NULL, 227 PRIMARY KEY, 227 CONVERTTIMESTAMP function examples of, 356 JULIANTIMESTAMP inverse relationship to, 356 syntax diagram of, 356 CONVERTTOHEX function examples of, 354, 355 syntax diagram of, 354 Correlation names examples of, 228 purpose of, 228 table reference use of, 228 COS function examples of, 357 syntax diagram of, 357 COSH function examples of, 358 syntax diagram of, 358 COUNT function DISTINCT clause within, 359 examples of, 359 syntax diagram of, 3
PAGE 526
syntax diagram of, 72 CROSS JOIN, description of, 66, 168 CURRENT_DATE function examples of, 362 syntax diagram of, 362 CURRENT_ROLE function example of, 363 syntax diagram of, 363 CURRENT_TIME function examples of, 364 precision specification within, 364 syntax diagram of, 364 CURRENT_TIMESTAMP function examples of, 361, 365 precision specification within, 361, 365 syntax diagram of, 361, 365 CURRENT_USER function example of, 366 syntax diagram of, 366 D Data Definition Language (DDL) statements summary o
PAGE 527
DATEFORMAT function examples of, 371 syntax diagram of, 371 Datetime data types DATE, 237 description of, 237 examples of literals, 263 TIME, 237 TIMESTAMP, 237 Datetime functions summary of, 327 ADD_MONTHS, 334 CONVERTTIMESTAMP, 356 CURRENT, 361 CURRENT_DATE, 362 CURRENT_TIME, 364 CURRENT_TIMESTAMP, 365 DATE_ADD, 367 DATE_PART (interval), 372 DATE_PART (timestamp), 373 DATE_SUB, 368 DATE_TRUNC, 374 DATEADD, 369 DATEDIFF, 370 DATEFORMAT, 371 DAY, 375 DAYNAME, 376 DAYOFMONTH, 377 DAYOFWEEK, 378 DAYOFYEAR, 37
PAGE 528
COUNT function use of, 359 MAX function use of, 407 MAXIMUM function use of, 407 MIN function use of, 408 STDDEV function use of, 457 SUM function use of, 461 VARIANCE function use of, 473 DML statements (see Data Manipulation Language (DML) statements) Documents, related information, 21 DOUBLE PRECISION data type, 242 DROP INDEX statement authorization and availability requirements, 111 considerations for, 111 example of, 111 syntax diagram of, 111 DROP MATERIALIZED VIEW statement example of, 112 syntax di
PAGE 529
syntax diagram of, 392 F File options CREATE INDEX use of, 60 CREATE TABLE use of, 72 Fixed-length character column, 236 FLOAT data type, 242 FLOOR function examples of, 393 syntax diagram of, 393 FOREIGN KEY constraint, 47, 77, 105, 227 FORMAT clause considerations for date formats, 305 considerations for other formats, 305 examples of, 305 syntax diagram, 304 FULL join, description of, 169 Functions, ANSI compliant, 34 G GET SERVICE statement considerations for, 133 examples of, 133 syntax diagram of, 1
PAGE 530
J Join CROSS, 168 FULL, 169 JOIN ON, 168 join predicate, 178 LEFT, 169 limits, 173 NATURAL, 168 NATURAL FULL, 168 NATURAL LEFT, 168 NATURAL RIGHT, 168 optional specifications, 168 RIGHT, 169 types, 168 JOIN ON join, description of, 168 JULIANTIMESTAMP function examples of, 397 syntax diagram of, 397 K Keys clustering, 260 hash partition, 260 index, 260 primary, 260 L LARGEINT data type, 241 LASTNOTNULL function examples of, 398 syntax diagram of, 398 LCASE function examples of, 399 syntax diagram of, 399
PAGE 531
M Magnitude, 254 MAINTAIN command considerations for, 214 description of, 211 examples of, 215, 216 multiple table reorg, 214 syntax diagram of, 211 uses, 210 MAINTAIN MVGROUP command, 215 Materialized aggregate view definition of, 65, 268 restricitions for, 67 Materialized join view definition of, 65, 268 restrictions for, 67 Materialized views and indexes, 66 authorization, 65 clustering, 66 described, 268 examples, 67 RECOMPUTE, 65, 268 restrictions, 67 single delta, 66 types of, 65, 268 using other mate
PAGE 532
examples of, 419 syntax diagram of, 419 MOVINGVARIANCE function examples of, 420 syntax diagram of, 420 MVGROUP, definition of, 269 N NATIONAL CHAR data type, 236 NATIONAL CHAR VARYING data type, 236 NATURAL FULL join, description of, 168 NATURAL join, description of, 168 NATURAL LEFT join, description of, 168 NATURAL RIGHT join, description of, 168 NCHAR data type, 236 NCHAR VARYING data type, 236 Neoview SQL objects, logical names, 230 NOT CASESPECIFIC, 77 NOT NULL constraint, 227 NULL predicate examples
PAGE 533
result of, 428 syntax diagram of, 428 POWER function examples of, 429 syntax diagram of, 429 Precision, description of, 254 Predicates summary of, 273 BETWEEN, 273 comparison, 274 description of, 273 EXISTS, 278 IN, 279 LIKE, 281 NULL, 283 quantified comparison, 284 PREPARE statement availability, 153 considerations for, 153 examples of, 153 syntax diagram of, 153 PRIMARY KEY constraint, 227 Primary key, description of, 260 Privileges GRANT EXECUTE statement use of, 137 GRANT SCHEMA statement use of, 139 GR
PAGE 534
Roles, 25 Rollback mode norollback-option, 188 ROLLBACK WORK statement considerations for, 162 example of, 162 syntax diagram of, 162 ROUND function examples of, 438 syntax diagram of, 438 Row size, calculating, 86 Row value constructor BETWEEN predicate use of, 273 comparison predicates use of, 275 IN predicate use of, 279 NULL predicate use of, 283 quantified comparison predicates use of, 284 ROW_NUMBER window function examples of, 491 syntax diagram of, 491 ROWS SINCE CHANGED function considerations for,
PAGE 535
summary of, 329 DIFF1, 384 DIFF2, 386 LASTNOTNULL, 398 MOVINGAVG, 413 MOVINGCOUNT, 414 MOVINGMAX, 415 MOVINGMIN, 416 MOVINGSTDDEV, 417 MOVINGSUM, 419 MOVINGVARIANCE, 420 OFFSET, 426 RANK/RUNNINGRANK, 432 ROWS SINCE, 439 ROWS SINCE CHANGED, 441 RUNNINGAVG, 444 RUNNINGCOUNT, 445 RUNNINGMAX, 446 RUNNINGMIN, 447 RUNNINGSTDDEV, 448 RUNNINGSUM, 449 RUNNINGVARIANCE, 450 THIS, 464 Sequence-generating column, 76 Sequence-generator table recalibrating the current value, 49, 83 SERIALIZABLE, 26, 27, 33 Set functions,
PAGE 536
examples of, 244 syntax diagram of, 243 Subquery correlated, 278, 294 description of, 294 inner query, 294 outer query, 294 outer reference, 294 row BETWEEN predicate, 273 comparison predicate, 275 IN predicate, 279 NULL predicate, 283 quantified comparison predicate, 285 scalar BETWEEN predicate , 273 comparison predicate , 275 DELETE statement, 107, 198 IN predicate , 279 NULL predicate, 283 quantified comparison predicate, 285 UPDATE statement, 198 table, 279 SUBSTR function examples of, 460 operand requ
PAGE 537
syntax diagram of, 469 Union operation associative, UNION ALL, 176 columns, characteristics of, 175 ORDER BY clause restriction, 176 SELECT statement use of, 171 Updatable view, requirements for, 102 UPDATE statement authorization requirements, 199 conflicting updates, 200 description of, 197 examples of, 201 isolation levels, 200 performance, 199 SET clause, 198 SET ON ROLLBACK clause, 198 SKIP CONFLICT access, 199 syntax diagram of, 197 WHERE clause, 199 UPDATE STATISTICS statement column groups, 204 colu
PAGE 538