Neoview SQL Reference Manual (R2.5)

ManualsBrandsHP ManualsSoftwareHP Neoview Release 2.5 Software

HP Neoview SQL Reference Manual

HP Part Number: 611360-001

Published: July 2010

Edition: HP Neoview Release 2.5

Summary of content (552 pages)

PAGE 1
HP Neoview SQL Reference Manual HP Part Number: 611360-001 Published: July 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
Syntax Description of ALTER MATERIALIZED VIEW..................................................................40 Considerations for ALTER MATERIALIZED VIEW.......................................................................40 Example of ALTER MATERIALIZED VIEW...................................................................................41 ALTER MVGROUP Statement..............................................................................................................
PAGE 5
Syntax Description of CREATE TABLE...........................................................................................73 Considerations for CREATE TABLE................................................................................................78 Considerations for CREATE SET TABLE........................................................................................78 Considerations for CREATE VOLATILE TABLE............................................................................
PAGE 6
Syntax Description of DROP TRIGGER........................................................................................119 Considerations for DROP TRIGGER.............................................................................................119 Example of DROP TRIGGER.........................................................................................................119 DROP VIEW Statement...........................................................................................................
PAGE 7
Syntax Description of REVOKE SCHEMA....................................................................................160 Considerations for REVOKE SCHEMA........................................................................................162 Examples of REVOKE SCHEMA...................................................................................................162 ROLLBACK WORK Statement...........................................................................................................
PAGE 8
Syntax Description of POPULATE INDEX....................................................................................220 Considerations for POPULATE INDEX........................................................................................220 Examples of POPULATE INDEX...................................................................................................221 PURGEDATA Utility.....................................................................................................................
PAGE 9
Literals.................................................................................................................................................274 Character String Literals................................................................................................................274 Datetime Literals............................................................................................................................275 Interval Literals....................................................
PAGE 10
Examples of SEQUENCE BY.........................................................................................................327 TRANSPOSE Clause...........................................................................................................................329 Considerations for TRANSPOSE...................................................................................................330 Examples of TRANSPOSE.....................................................................................
PAGE 11
Example of CODE_VALUE Function............................................................................................363 CONCAT Function..............................................................................................................................364 Concatenation Operator (||).........................................................................................................364 Considerations for CONCAT................................................................................
PAGE 12
Examples of DAYOFMONTH.......................................................................................................389 DAYOFWEEK Function......................................................................................................................390 Example of DAYOFWEEK.............................................................................................................390 DAYOFYEAR Function............................................................................................
PAGE 13
Example of LTRIM.........................................................................................................................418 MAX/MAXIMUM Function................................................................................................................419 Considerations for MAX/MAXIMUM...........................................................................................419 Example of MAX/MAXIMUM........................................................................................
PAGE 14
Examples of RANK/RUNNINGRANK.........................................................................................444 REPEAT Function................................................................................................................................447 Example of REPEAT......................................................................................................................447 REPLACE Function.........................................................................................
PAGE 15
Considerations for SUBSTRING/SUBSTR.....................................................................................471 Examples of SUBSTRING/SUBSTR...............................................................................................472 SUM Function.....................................................................................................................................473 Considerations for SUM..........................................................................................
PAGE 16
MIN Window Function.......................................................................................................................500 Examples of MIN Window Function.............................................................................................500 RANK Window Function....................................................................................................................502 Examples of RANK 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.........................................................29 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
• • • • • • • • • • Chapter 3: “SQL Utilities” (page 211), describes utilities that perform tasks such as maintenance, populating indexes, purging data from tables and indexes, and replicating schemas, data, and statistics. Chapter 4: “SQL Language Elements” (page 233), describes parts of the language, such as database objects, data types, expressions, identifiers, literals, and predicates, which occur within the syntax of Neoview SQL statements.
PAGE 21
A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: DROP SCHEMA schema [CASCADE] [RESTRICT] DROP SCHEMA schema [ CASCADE | RESTRICT ] { } Braces Braces enclose required syntax items. For example: FROM { grantee[, grantee]...
PAGE 22
DAY (datetime-expression) DAY(datetime-expression) If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: myfile.sh Line Spacing If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections.
PAGE 23
Neoview Command Interface (NCI) Guide Information about using the HP Neoview Command Interface to run SQL statements interactively or from script files. Neoview Performance Analysis Tools Online Help Context-sensitive help topics that describe how to use the Neoview Performance Analysis Tools to analyze and troubleshoot query-related issues on the Neoview data warehousing platform.
PAGE 24
Publishing History Part Number Product Version Publication Date 546189-001 HP Neoview Release 2.4 May 2009 546189-002 HP Neoview Release 2.4 August 2009 602622–001 HP Neoview Release 2.4 Service Pack 2 March 2010 602622–002 HP Neoview Release 2.4 Service Pack 2 April 2010 611360–001 HP Neoview Release 2.5 July 2010 HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs.
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
For related information about transactions, see “Transaction Isolation Levels” (page 32). READ UNCOMMITTED This option enables you to access locked data. READ UNCOMMITTED is not available for DML statements that modify the database. It is available only for a SELECT statement. READ UNCOMMITTED provides the lowest level of data consistency.
PAGE 27
• • “Lock Mode” (exclusive, shared, no lock) “Lock Holder” (transaction or process) Lock Duration Lock duration controls how long a lock is held. You can specify lock duration for only the read portion of a statement. You can use the LOCK TABLE statement to lock a table. Lock duration is also affected by whether you choose the SERIALIZABLE access option for DML statements. This access option causes the maximum lock duration.
PAGE 28
1. 2. 3. Transaction is started. Database changes are made. Transaction is committed. If, however, the changes cannot be made or if you do not want to complete the transaction, you can abort the transaction so that the database is rolled back to its original state.
PAGE 29
automatically abort the transaction and return an error indicating that the transaction was rolled back. System-Defined Transactions In some cases, Neoview SQL defines transactions for you. These transactions are called system-defined transactions. Most DML statements initiate transactions implicitly at the start of execution. See “Implicit Transactions” (page 191).
PAGE 30
Table 1-2 Concurrent DDL/Utility and DML Operations DML Operation in Progress DDL Operations You Can Start SELECT UNCOMMITTED SELECT SHARE SELECT EXCLUSIVE UPDATE/ INSERT/ DELETE ALTER TABLE attributes Allowed1 Allowed Allowed Allowed ALTER TABLE other Allowed1 Waits Waits Waits CREATE INDEX with Allowed1 POPULATE Allowed Waits Waits CREATE INDEX NO Allowed POPULATE Allowed Allowed Allowed CREATE TRIGGER subject table Allowed Allowed Waits Waits CREATE TRIGGER referenced table A
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 REVOKE Allowed1 Allowed Waits Waits UPDATE STATISTICS Allowed Allowed Allowed Allowed2 1 2 DDL operation aborts the DML operation. Allowed except during commit phase.
PAGE 32
READ ONLY transactions: • • • Will not write anything into the transaction log Will not abort if they extend beyond the system configured auto abort interval Will not pin the transaction log When AUTOCOMMIT is ON, the Neoview platform automatically uses READ ONLY transactions for select statements. This could reduce concurrency if the select statement is long-running and the isolation level is SERIALIZABLE.
PAGE 33
SERIALIZABLE or REPEATABLE READ This option locks all data accessed through the transaction and holds the locks on data until the end of the transaction. SERIALIZABLE provides the highest level of data consistency. A transaction executing with this isolation level does not allow dirty reads, nonrepeatable reads, or phantoms. Transaction Rollback Mode The rollback mode for a transaction can be set to either ON or OFF.
PAGE 34
• • • • • • • • • • • • • • 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
• • • • • • • • • • • • • • • 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. For example, Neoview SQL might display this error message: *** ERROR[1000] A syntax error occurred.
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 119) Drops a trigger. “DROP VIEW Statement” (page 120) Drops a view. “GRANT Statement” (page 136) Grants access privileges for a table or view to specified roles. “GRANT EXECUTE Statement” (page 138) Grants privileges for executing a stored procedure in Java (SPJ) to specified roles. “GRANT SCHEMA Statement” (page 140) Grants access privileges for a schema to specified roles.
PAGE 39
“PREPARE Statement” (page 154) Compiles an SQL statement for later use with the EXECUTE statement. “UPDATE STATISTICS Statement” (page 204) 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 186) 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 282). 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 242).
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
NOTE: The NOT ENFORCED referential constraint (REFERENCES/FOREIGN KEY) is used to help the optimizer eliminate redundant joins, and not to enforce referential integrity. 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.
PAGE 48
new-name specifies the new name of the object after the RENAME TO operation occurs. CASCADE specifies that indexes and constraints on the renamed object will be renamed. 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.
PAGE 49
This allows for more available values in the cycle range for the internal sequence generator. Inserts will be successful until the new maxvalue is reached. NO MAXVALUE is not allowed. If you use NO MAXVALUE on the ALTER TABLE ALTER COLUMN specification, an error will be raised: >>alter table T115T002 alter column surrogate_key set NO MAXVALUE; *** ERROR[1595] The MAXVALUE option for the IDENTITY column SURROGATE_KEY must be a valid numeric value. NO MAXVALUE is not allowed.
PAGE 50
Recalibrate an IDENTITY Column Based on the INCREMENT BY Value This method always performs a SELECT on the base table containing the IDENTITY column to obtain the current maximum value of the IDENTITY column. This maximum value will be incremented using the value of the INCREMENT BY internal sequence generator option. It will be checked against the current numbering scheme of the INCREMENT BY option.
PAGE 51
• • • If the default specification type is GENERATED BY DEFAULT AS IDENTITY, a number less than the current maximum value of the IDENTITY column is allowed. The user-specified recalibration value must not be less than the START WITH and MINVALUE options of the internal sequence generator. The user-specified recalibration value will not be synchronized with the INCREMENT BY numbering scheme. The specified value will be considered a pure override.
PAGE 52
insert into T1 values(default,3); --- 1 row(s) inserted. select * from T1; SURROGATE_KEY -------------------------------99 100 102 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 242).
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 242). 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 242). 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 27). 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 27). 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 242). 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 281). 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
embedded inserts, updates, or deletes. For the syntax and syntax description of the order-by-clause, see “SELECT Statement” (page 164). mv-attributes The COMMIT REFRESH EACH attribute is only allowed for ON REQUEST materialized views that are defined on single delta materialized views. A single delta materialized view is a materialized view that can be refreshed based on changes to a single table.
PAGE 66
The INSERT privilege is used to authorize the incremental REFRESH. You must have INSERT privileges on a materialized view to perform an incremental REFRESH on it. The INSERT privilege can be at the object or schema level. You must have full access (SELECT, INSERT, and DELETE privileges) to the materialized view to perform either initialize or recompute on the materialized view.
PAGE 67
You can create additional secondary indexes, which cannot be unique. Joins • • • An inner equi-join query is a query where rows from each table are matched to specific rows in other tables using equal predicates. Outer joins include LEFT OUTER JOIN or FULL OUTER JOIN. In a cross join, not all predicates are given so each row of a table is matched with all the rows of the other table.
PAGE 68
AS SELECT PUBSCH.DETAIL_TABLE.ORDERNUM, SUM(PUBSCH.DETAIL_TABLE.QUANTITY) AS TOTAL_AVAIL FROM PUBSCH.DETAIL_TABLE WHERE PUBSCH.DETAIL_TABLE.ORDERNUM > 1 GROUP BY PUBSCH.DETAIL_TABLE.ORDERNUM; • This is an example of an MJV: CREATE MATERIALIZED VIEW sales_store REFRESH ON REQUEST INITIALIZE ON REFRESH AS SELECT price, partkey, timekey, store.nam FROM sales, store WHERE sales.storekey = store.
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 282) and “MAINTAIN MVGROUP” (page 217). 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 304). 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
Maximum Extents = minimum of ((Disk Size - Primary Extent Size) / Secondary Extent Size, 768); In most cases, the space allocated for the table after the first row is inserted is the value set by this option, unless the value set is too big. If the value set by this option is less than or equal to 1024 MB times the number of partitions, then the space allocated after the first row is inserted is the value set by this option.
PAGE 76
DEFAULT default | NO DEFAULT specifies a default value for the column or specifies that the column does not have a default value. “DEFAULT Clause” (page 314). 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 88). • GENERATED BY DEFAULT AS IDENTITY creates an IDENTITY column, either system-generated or user-specified.
PAGE 77
REFERENCES ref-spec NOT ENFORCED specifies a REFERENCES column constraint. The maximum combined length of the columns for a REFERENCES constraint is 2048 bytes. 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.
PAGE 78
specified in the FOREIGN KEY clause. For information about ref-spec, see REFERENCES ref-spec NOT ENFORCED. NOT CASESPECIFIC is a column constraint that specifies that the column contains strings that are not case specific. The default is CASESPECIFIC. Comparison between two values is done in a case insensitive way only if both are case insensitive. This applies to comparison in a binary predicate, LIKE predicate, and POSITION/REPLACE string function searches. See “Examples of CREATE TABLE” (page 93).
PAGE 79
For IDENTITY columns, the GENERATED BY DEFAULT AS IDENTITY option allows you to supply the value or use the system-generated value. The GENERATED ALWAYS AS IDENTITY option provides system-generated unique values only. If the values are user-provided, duplicates could occur. In that case, the duplicate rows are automatically ignored and no errors occur. Restrictions for CREATE SET TABLE • • CREATE SET TABLE is applicable only on base tables and is not supported for index tables.
PAGE 80
How Neoview SQL Supports Nullable Keys for Volatile Tables • • • Allows nullable keys in primary key, HASH PARTITION BY, STORE BY, and unique constraints. A null value is treated as the highest value for that column. A null value as equal to other null values and only one value is allowed for that column. 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.
PAGE 81
Creating Nullable Constraints In a Volatile Table These examples show the creation of nullable constraints (primary key, HASH PARTITION BY, STORE BY, and unique) in a volatile table: create volatile table t (a int, primary key(a)); create volatile table t (a int, primary key(a)); create volatile table t (a int, primary key(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: >>c
PAGE 82
using the LIKE specification are defined as if the new table is created explicitly by the current user. For tables with IDENTITY columns, the target table inherits the IDENTITY property of a column along with the corresponding sequence generating properties from the source table. Neither Neoview SQL's CREATE TABLE LIKE support nor its behavior with respect to the IDENTITY column is ANSI compliant.
PAGE 83
Create table t2 like table t1. The CREATE TABLE LIKE target table t2 will inherit the internal sequence generator properties from the source table t1. The AS SELECT syntax will select all rows from table t1 and insert them into table t2. The IDENTITY column values will be user-specified values. 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.
PAGE 84
The CREATE TABLE LIKE target table t2 will inherit the internal sequence generator properties from the source table t1. The AS SELECT syntax will select all rows from table t1 and insert them into table t2. The IDENTITY column values will be user-specified values. 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.
PAGE 85
The NO LOAD option in a CREATE TABLE AS statement creates a table with the CREATE TABLE AS statement, but does not load data into the table. The option is useful if you need to create a table to review its structure and to analyze the SELECT part of the CREATE TABLE AS statement with the EXPLAIN statement. You can also use EXPLAIN to analyze the implicated INSERT/SELECT part of the CREATE TABLE AS ... NO LOAD statement. For example: CREATE TABLE ttgt NO LOAD AS (SELECT ...); CREATE TABLE AS ...
PAGE 86
This may cause duplicates to be experienced because the current value of the internal sequence generator is incremented to values that are already present in the IDENTITY column of table t2. To avoid this problem, HP recommends that you determine a START WITH value that is greater than the largest value of the IDENTITY column of the source table. For example: SELECT MAX(a) FROM t1; (EXPR) --------15 The CREATE TABLE AS statement for table t2 would then include internal sequence generator options.
PAGE 87
Limits for Tables You can create tables and indexes with rows that are 32,708 bytes in size and blocks that are 32K bytes in size. The default block size for all create table and index operations is 32768 (32K) byte block size (in all cases). No tools are provided for explicitly migrating an existing 4KB block size table to a 32KB block size table. If you want a larger block size, a new table must be created and data copied from the old table to the new table.
PAGE 88
Generating Values For an IDENTITY Column You can use IDENTITY columns to automatically generate unique values. The values are unique across all partitions of the table for the IDENTITY column. IDENTITY columns are declared in the CREATE TABLE statement. IDENTITY columns can be used as surrogate keys. They can also be used to uniquely identify records with the same key.
PAGE 89
• • • • CREATE TABLE AS with GENERATED BY DEFAULT AS IDENTITY column is supported. Inserting values for a GENERATED ALWAYS AS IDENTITY column is not allowed. CREATE TABLE LIKE with an IDENTITY column is supported. The target table will have the same column and sequence generating attributes as the source table. CREATE VOLATILE TABLE with an IDENTITY column is supported. The internal sequence generator associated with the IDENTITY column will be dropped when the table with that IDENTITY column is dropped.
PAGE 90
• • • • • • • • • You cannot add an IDENTITY column using the ALTER TABLE statement. Expressions involving the keyword DEFAULT are not allowed as IDENTITY column values. You must specify the keyword DEFAULT or supply a valid value. Error 3411 will be raised if an expression is specified for an IDENTITY column value. UPDATE operations on IDENTITY columns defined as GENERATED ALWAYS AS IDENTITY are not supported.
PAGE 91
• This example shows that the values for the IDENTITY column Id_col will always be generated by the system. The following sequence generator options will take default values because they are not specified: MINVALUE, MAXVALUE, and NO CYCLE.
PAGE 92
Neoview SQL Extensions to CREATE TABLE This statement is supported for compliance with ANSI SQL:1999 Entry Level. Neoview SQL extensions to the CREATE TABLE statement are ASCENDING, DESCENDING, PARTITION, MAXTABLESIZE, ATTRIBUTE, and DISK POOL clauses. CREATE TABLE LIKE is also an extension. Considerations for DISK POOL The DISK POOL attribute allows disks to be divided into disk pools.
PAGE 93
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 PERSNL.JOB_CORPORATE LIKE PERSNL.
PAGE 94
• 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 95
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 no partition 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 no partition as select a+1 from t1 An error is returned, expression must be renamed.
PAGE 96
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 311).
PAGE 97
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 98
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 99
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 100
As a workaround for this issue, change column c1 of table t1 from varchar(2040) to char(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 101
((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 102
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 312). 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 242). (column-name [,column-name ]...) specifies names for the columns of the view.
PAGE 103
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 104
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 105
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 106
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 107
select a, c from vp; -- reads vp0 and vp1 select d from vp; -- reads vp0 and vp2 CREATE VIEW Statement 107
PAGE 108
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 109
TRANSACTION statement to enable the NO ROLLBACK option for the transaction. See “SET TRANSACTION Statement” (page 188). 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 242).
PAGE 110
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 111
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 112
delete from table1 where a in (select a from table1 where b > 200) 112 SQL Statements
PAGE 113
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 242). 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 242). Considerations for DROP INDEX For indexes on materialized views, see “Indexes and Materialized Views” (page 66).
PAGE 114
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 115
DROP MVGROUP Statement • • • “Syntax Description of DROP MVGROUP” “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 282). 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 materialized views that were part of the group.
PAGE 116
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 304). 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 117
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 309). DROP SYNONYM alternate-name Syntax Description of DROP SYNONYM alternate-name specifies the name of the synonym. See “Database Object Names” (page 242).
PAGE 118
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 242). DROP [VOLATILE] TABLE table [RESTRICT|CASCADE] Syntax Description of DROP TABLE table is the name of the table to delete.
PAGE 119
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 311). 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 242).
PAGE 120
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 312). 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 121
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 269).
PAGE 122
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 123
--- 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 124
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 401). NOTE: Formatting changes to the EXPLAIN output data might occur in the future.
PAGE 125
prepared-stmt-name an SQL identifier containing the name of a statement already prepared in this session. An SQL identifier is case-insensitive (will be in uppercase) unless it is double-quoted. It must be double-quoted if it contains blanks, lowercase letters, or special characters. It must start with a letter. When you refer to the prepared query in a SELECT statement, you must use uppercase.
PAGE 126
OPERATOR The operator type OPT Query optimizations that were applied DESCRIPTION Additional information about the operator CARD Estimated number of rows returned by the plan. CARDINALITY and ROWS_OUT are the same. This example uses OPTIONS 'f ': >>explain options 'f' select * from region; LC --2 1 . RC --. . . OP --3 2 1 OPERATOR -------------------root partition_access file_scan OPT -------- DESCRIPTION -------------------- fs fr REGION CARD --------1.00E+002 1.00E+002 1.
PAGE 127
Table 2-5 Node Listing Information Field Description Operator name Current operator type, such as FILE_SCAN, HASH_JOIN, NESTED_JOIN, and so on. SEQ_NO Sequence number of the current operator in the query execution plan; indicates the sequence in which the query plan is generated. Children information Sequence number of the operator’s children (operators that feed data up to this operator). There can be 0, 1, or 2 children shown as NO CHILDREN, ONLY CHILD 6, or CHILDREN 5, 7.
PAGE 128
PARTITION_ACCESS ========================== REQUESTS_IN .............. (not found) ROWS_OUT ............... 100 EST_OPER_COST ............ 0.01 EST_TOTAL_COST ........... 0.01 DESCRIPTION max_card_est ......... 100 fragment_id ............ 2 parent_frag ............ 0 fragment_type .......... dp2 buffer_size ....... 31,000 record_length ........ 306 space_usage ............ 5:32:100:144 bottom_node_map ........
PAGE 129
------------------------------------------------------------------ PLAN SUMMARY MODULE_NAME .............. DYNAMICALLY COMPILED STATEMENT_NAME ........... NOT NAMED PLAN_ID .................. 212141478299499838 ROWS_OUT ............... 100 EST_TOTAL_COST ........... 0.01 STATEMENT ................ select * from region; ------------------------------------------------------------------ NODE LISTING ROOT ====================================== SEQ_NO 3 ONLY CHILD 2 REQUESTS_IN ..............
PAGE 130
key_columns ............ R_KEY begin_key .............. (R_KEY = end_key ................ (R_KEY = ---SQL operation complete. Machine-Readable [OPTIONS 'm'] Considerations The machine-readable format also displays all of the plan data, but in a format easy to process by programs, one line per operator. This option provides the same output as the EXPLAIN function, but uses a different command to obtain the output. Each line is about 4000 characters long.
PAGE 131
Column Name Data Type Description DETAIL_COST VARCHAR (200) Cost vector of five items, which are described in detail in the next table. DESCRIPTION VARCHAR (3000) Additional information about the operator. For a detailed look at all operators, see the Neoview Query Guide. The DETAIL_COST column contains these cost factors: Table 2-6 Cost Factors of DETAIL_COST column CPU_TIME An estimate of the number of seconds of processor time it might take to execute the instructions for this operator.
PAGE 132
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 133
for (int i = 0; i < 10; i++) { switch (i) { case 0: System.out.println(""); stmt = connection.createStatement(); rs = stmt.executeQuery(sqlText); System.out.println(sqlText); break; default: rs = null; continue; } ResultSetMetaData rsMD = rs.getMetaData(); System.out.println(""); for (int j = 1; j <= rsMD.getColumnCount(); j++) { System.out.println(rsMD.getColumnName(j)); } rowNo = 0; while (rs.next()) { rowNo++; for (int j=1; j <= rsMD.getColumnCount(); j++) { System.out.println(rs.
PAGE 134
GET SERVICE Statement • • • “Syntax Description of GET SERVICE” “Considerations for GET SERVICE” “Examples of GET SERVICE” 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.
PAGE 135
SQL> By default, the query plan and SQL text are turned off (represented by 0 in the output) for HP_DEFAULT_SERVICE, and there is no connection rule in effect. This GET SERVICE statement shows that SALESQUERIES is the current service for the NCI session: SQL>get service; CUR_SERVICE PLAN TEXT CUR_SCHEMA RULE_NAME APPL_NAME SESSION_NAME DSN_NAME ROLE_NAME ------------ ---- ---- ---------- --------- --------- ------------ ---------------------- --------SALESQUERIES 1 1 NEO.
PAGE 136
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 301). GRANT { privilege [,privilege]... | ALL [PRIVILEGES] } ON [TABLE] object TO {grantee [,grantee ]...
PAGE 137
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 138
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 301). GRANT EXECUTE ON [PROCEDURE] procedure-ref TO {grantee [,grantee ]... } [WITH GRANT OPTION] procedure-ref is: [schema-name.
PAGE 139
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 140
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 301). GRANT {privilege [,privilege]... | all [PRIVILEGES] } ON SCHEMA schema-name TO {grantee [,grantee ]...
PAGE 141
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 142
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 143
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 144
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 145
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 146
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 147
--- 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 148
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 149
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 26). LOCK TABLE is a Neoview SQL extension.
PAGE 150
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 151
MERGE INTO Statement • • • “Syntax Description of MERGE INTO” “Considerations for MERGE INTO” “Example of MERGE INTO” 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 152
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 153
Example of MERGE INTO 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 154
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 155
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 121).
PAGE 156
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 301). REVOKE [GRANT OPTION FOR] {privilege [,privilege ]...
PAGE 157
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 158
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 301). REVOKE [GRANT OPTION FOR] EXECUTE ON [PROCEDURE] procedure-ref FROM {grantee [,grantee ]... } [RESTRICT | CASCADE] procedure-ref is: [schema-name.
PAGE 159
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 160
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 301). REVOKE [GRANT OPTION FOR] {privilege [,privilege ] ...| all [PRIVILEGES ]} ON SCHEMA schema-name FROM {grantee [,grantee]...
PAGE 161
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 162
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 163
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 27). Syntax Description of ROLLBACK WORK ROLLBACK [WORK] WORK is an optional keyword that has no effect.
PAGE 164
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 165
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 166
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 167
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 175). FROM table-ref [,table-ref]...
PAGE 168
(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 108). 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 169
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 144). [AS] corr [(col-expr-list)] specifies an optional correlation name and an optional column list.
PAGE 170
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 171
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 172
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 175).
PAGE 173
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 174
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 175
• • 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 176
• • • • 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 177
(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 178
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 179
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 180
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 181
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 182
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 183
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 184
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 185
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 304). default-schema-name is an SQL identifier. For example, you can use MYSCHEMA or myschema or a delimited identifier "my schema".
PAGE 186
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 187
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 188
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 189
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 190
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 191
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 192
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 193
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 194
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 195
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 196
-- 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 197
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 164).
PAGE 198
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 199
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 164). [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 200
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 305). 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 201
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 188).
PAGE 202
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 203
(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 204
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 205
Syntax Description of UPDATE STATISTICS table names the table or materialized view for which statistics are to be updated. To refer to a table, use the ANSI logical name. See “Database Object Names” (page 242). CLEAR deletes some or all histograms for the table table. Use this option when new applications no longer use certain histogram statistics. If you do not specify column-group-list, all histograms for table are deleted. If you specify column-group-list, only columns in the group list are deleted.
PAGE 206
EVERY KEY The EVERY KEY keyword indicates that histogram statistics are to be generated for columns that make up the primary key and indexes. For example, table has columns A, B, C, D defined. If the primary key comprises columns A, B, statistics are generated for (A, B), A and B. If the primary key comprises columns A, B, C, statistics are generated for (A,B,C), (A,B), A, B, C. If the primary key comprises columns A, B, C, D, statistics are generated for (A, B, C, D), (A, B, C), (A, B), and A, B, C, D.
PAGE 207
PERIODIC size ROWS EVERY period ROW directs Neoview SQL to choose the first size number of rows from each period of rows. The value size must be an integer that is greater than zero and less than or equal to the value period. (0 < size <= period). The size of the period is defined by the number of rows specified for period. The value period must be an integer that is greater than zero (period > 0). persistent-sample-option r rows A row sample is used to read r rows from the table.
PAGE 208
Authorization and Locking To run the UPDATE STATISTICS statement against SQL tables, you must have the authority to read the user table for which statistics are generated. Because the histogram tables are registered in the schema of table, you must have the authority to read and write to this schema. UPDATE STATISTICS momentarily locks the definition of the user table during the operation but not the user table itself. The UPDATE STATISTICS statement uses READ UNCOMMITTED isolation level for the user table.
PAGE 209
Examples of UPDATE STATISTICS • This example generates four histograms for the columns jobcode, empnum, deptnum, and (empnum, deptnum) for the table EMPLOYEE. Depending on the table’s size and data distribution, each histogram should contain ten intervals. UPDATE STATISTICS FOR TABLE employee ON (jobcode),(empnum, deptnum) GENERATE 10 INTERVALS; --- SQL operation complete. • This example generates histogram statistics using the ON EVERY COLUMN option for the table DEPT.
PAGE 210
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 211
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 212) Performs one or more maintenance tasks, such as REORG, UPDATE STATISTICS, and REFRESH, on a specified database object. “POPULATE INDEX Utility” (page 220) Loads indexes.
PAGE 212
MAINTAIN Command • • • “Syntax Description of MAINTAIN” “MAINTAIN MVGROUP” “Examples of MAINTAIN” The MAINTAIN command is a syntax-based utility that can be executed in the Neoview Command Interface (NCI) 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. The MAINTAIN command provides support to specify multiple tables in one MAINTAIN command.
PAGE 213
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 214
— — — — — • • • Update of mvlog statistics Refresh of all materialized views on that table Reorg of all materialized views on the table Reorg of all indexes on the materialized views Update of statistics for all materialized views 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.
PAGE 215
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 216
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 217
internal settings to determine if a REORG operation is necessary. If you specify OVERRIDE, as the following example shows, ESAM skips the internal settings and initiates a REORG operation: MAINTAIN TABLE t1, REORG ', OVERRIDE'; 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.
PAGE 218
maintain table t1_090, all, display; • Displays all tasks that are done on table t1_090 but shows the actual execution: maintain table t1_090, all; • Refreshes mvgroup MVG_090: maintain mvgroup MVG_090, refresh; • 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
PAGE 219
Table001: Table002: Rate: Slack: Status: Completed: CAT.SCH.T1 CAT.SCH.T2 -1 -1 COMPLETED -001-00-00 00:00:00.000000 TableName: Partition: Location: Status: Segment: Cpu: PctDone: CAT.SCH.T1 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.
PAGE 220
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 (NCI). The POPULATE INDEX utility loads Neoview SQL indexes. POPULATE INDEX index ON table [index-option] index-option is ONLINE | OFFLINE Syntax Description of POPULATE INDEX index is an SQL identifier that specifies the simple name for the index.
PAGE 221
• • 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 222
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 (NCI). 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 242).
PAGE 223
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 224
REPLICATE Command • • • “Syntax Description of REPLICATE ” “Considerations for REPLICATE ” “Examples of REPLICATE” The REPLICATE command is a syntax-based utility that can be executed from any interface where an SQL command can be executed. The REPLICATE command allows an Neoview installation to replicate a table or an entire schema from one Neoview system to another. The REPLICATE command behaves like any other SQL command in that it can be issued anywhere an SQL command can be issued.
PAGE 225
Syntax Description of REPLICATE src name of the source table. schema name of the source schema. node target name in the format \node (backslash (\) followed by target system master segment name, for example, \NEO0101). query_id query ID of the replicate object. pattern pattern same as what is used in LIKE clause. object-specification • The SOURCE TABLE src can be specified in a single table name format or as a quoted in-list. The name does not need to be fully qualified.
PAGE 226
object definitions, you can replicate the data for these objects. For additional information, see “Replicating an Entire Schema” (page 226). If the PURGEDATA TARGET option is specified with the COPY DDL clause, target objects will be dropped before replicating the source object definitions. • The COPY STATISTICS option will replicate statistics for all objects in the schema to the target system. Existing statistics in the target schema will be deleted before the replication.
PAGE 227
REPLICATE SOURCE SCHEMA , target system \node; • This command copies statistics: REPLICATE SOURCE SCHEMA , target system \node, copy statistics; Single-Table Replication Single table replication replicates data and dependent indexes. Dependent objects, such as views, materialized views, synonyms, and privileges are not replicated. To get the data from materialized views, you must perform the REFRESH command on the target table.
PAGE 228
Replicating Statistics Histogram statistics for objects in a schema can be replicated with the COPY STATISTICS option. All the relevant histogram statistics for the objects in the specified schema will be collected on the source system, copied to the target system, and then inserted into the histogram tables on the target system. Existing statistics on the target system will be deleted before inserting the source statistics.
PAGE 229
replicate source schema neo.SCH, copy ddl, target system \tin0101; SrcSchema: SCH TgtSchema: SCH SrcSystem: EXE0201 TgtSystem: TIN0101 StartTime: 2010-05-05 17:16:48.601352 EndTime: 2010-05-05 17:18:39.907549 ElapsedTime: 00:01:51.306 *** ERROR[11205] Java class 'employee' was not found in external path '/home'. *** ERROR[1231] User-defined routine NEO.SCH.PROC_EMPLOYEE could not be created. *** ERROR[8659] BDR Listener process on TIN0101 received an error from CLI. --- SQL operation failed with errors.
PAGE 230
• • • A failed replicate operation on any partition will cause the REPLICATE command to abort. Restart after any failure will not be supported. A failed REPLICATE operation will purge data from all target partitions. You will need to reissue the REPLICATE command to replicate again. An aborted REPLICATE command also purges data from all target partitions. Schema DDL (COPY DDL) and statistics (COPY STATISTICS) replication can only be done for all objects in the specified schema.
PAGE 231
REPLICATE SOURCE SCHEMA sch, COPY STATISTICS, TARGET SYSTEM \exe0101; • Copies statistics for the objects. Return status of a replicate operation in progress: REPLICATE GET STATUS FOR QID 'MXID01123456789212131901967007490000000000204USER00_37'; SrcObject: TgtObject: ObjType: SrcSystem: TgtSystem: NumPartns: StartTime: EndTime: ElapsedTime: TotalBlocks: BlocksReplicated: PercentDone: Status: • NEO.SCH.T1 NEO.SCH.T1 TABLE \EXE0101 \DOC0101 32 2010-02-11 15:40:16.302597 2010-02-11 15:40:18.
PAGE 232
PAGE 233
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 234
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 235
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 236
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 237
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 238
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 239
Cursors The ANSI SQL standard defines a “cursor”. Neoview uses a SQL cursor to manage an ODBC or JDBC result set, which is created by executing a SQL statement. A cursor can be open or closed. A cursor is opened after the SQL statement is executed. The ODBC SQLFetch() or JDBC next() methods implicitly use the open cursor to retrieve rows from the result set. When the cursor is closed before ODBC or JDBC has retrieved all rows from the result set, the result set is discarded.
PAGE 240
"WHERE salary > ? AND jobcode= ?" ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT); To specify a non-holdable cursor in a JDBC client application, specify CLOSE_CURSORS_AT_COMMIT as a resultSetHoldability parameter to the createStatement or prepareStatement method. In Neoview SQL, the default is a non-holdable cursor.
PAGE 241
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 242
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 241). 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 243
Data Types Neoview SQL data types are character, datetime, interval, or numeric (exact or approximate): “Character String Data Types” (page 248) Fixed-length and variable-length character data types. “Datetime Data Types” (page 250) DATE, TIME, and TIMESTAMP data types. “Interval Data Types” (page 251) Year-month intervals (years and months) and day-time intervals (days, hours, minutes, seconds, and fractions of a second). “Numeric Data Types ” (page 253) Exact and approximate numeric data types.
PAGE 244
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 245
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 246
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 247
NUMERIC(19,4) sum. But because there are no user-specified extended precision columns, the system casts the sum back to the user-specified type of NUMERIC(18,4). CREATE TABLE T(a NUMERIC(18,4), B NUMERIC(18,4); INSERT INTO T VALUES (1.1234, 2.1234); >> SELECT A+B FROM T; (EXPR) -------------3.246 If this behavior is not acceptable, you can use one of these options: — Specify the column type as NUMERIC(19,4). For example, CREATE TABLE T(A NUMERIC(19,4), B NUMERIC(19,4)); or — Cast the sum as NUMERIC(19,4).
PAGE 248
--- SQL operation complete. >>SHOWDDL TABLE t; CREATE TABLE SCH.T ( N NUMERIC(128, 30) DEFAULT NULL ) NO PARTITION ; --- SQL operation complete. >> Character String Data Types Neoview SQL includes both fixed-length character data and variable-length character data. You cannot compare character data to numeric, datetime, or interval data.
PAGE 249
NCHAR VARYING [(length)] [UPSHIFT] [[NOT]CASESPECIFIC], NATIONAL CHAR[ACTER] VARYING (length) [UPSHIFT] [[NOT]CASESPECIFIC] specifies a column with varying-length data in the pre-defined national character set. Considerations for Character String Data Types Difference Between CHAR and VARCHAR You can specify a fixed-length character column as CHAR(n), where n is the number of characters you want to store.
PAGE 250
Datetime Data Types A value of datetime data type represents a point in time according to the Gregorian calendar and a 24-hour clock in local civil time (LCT). A datetime item can represent a date, a time, or a date and time. When a numeric value is added to or subtracted from a date type, the numeric value is automatically CASTed to an INTERVAL DAY value.
PAGE 251
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 252
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 253
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 254
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 255
Expressions An SQL value expression, referred to as an expression, can evaluate to a value with one of these: “Character Value Expressions” (page 255) Operands can be combined with the concatenation operator (||). Example: 'HOUSTON,' ||' TEXAS' “Datetime Value Expressions” (page 257) Operands can be combined in specific ways with arithmetic operators.
PAGE 256
Examples of Character Value Expressions These are examples of character value expressions: 256 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 257
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 258
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 259
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 260
-------------------------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 261
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 262
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 263
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 264
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 265
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 266
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 267
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 268
Examples of Numeric Value Expressions These are examples of numeric value expressions: 268 -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 269
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 270
mytable • MYTABLE MyTable mYtAbLe These are delimited identifiers: "mytable" "table" "2006 SALES" "CUSTOMER-BILLING-INFORMATION" Because delimited identifiers are case-sensitive, Neoview SQL treats the identifier "mytable" as different from the identifiers "MYTABLE" or "MyTable". Trailing spaces in a delimited identifier are truncated. For example, "mytable " is equivalent to "mytable". You can use reserved words as delimited identifiers.
PAGE 271
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 272
Keys Neoview SQL supports these types of keys: • • • • • “Clustering Keys” SYSKEYs “Hash Partition Keys” “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 273
Hash Partition Keys Partitioned tables have a partitioning key (HASH PARTITION BY clause), chosen from the columns of the clustering key which Neoview SQL uses to distribute rows to different disks. The list of columns defined in the partitioning key is used to determine the hash partition key. The hash partition key has the following characteristics: • Multiple columns can be chosen for the hash partition key (composite key) • The values of these columns are used to determine placement.
PAGE 274
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 274) A series of characters enclosed in single quotes. Example: 'Planning' “Datetime Literals” (page 275) Begins with keyword DATE, TIME, or TIMESTAMP and followed by a character string.
PAGE 275
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 248).
PAGE 276
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 277
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 278
'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 279
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 280
Examples of Numeric Literals • 280 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 281
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 282
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 283
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 284
Null and Expression Evaluation Comparison Expression Type 284 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.
PAGE 285
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 286
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 286) Determines whether a sequence of values is within a range of sequences of values.
PAGE 287
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 288
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 289
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 290
(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 291
... --- 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 292
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 293
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 294
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 295
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 296
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 297
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 298
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 255).
PAGE 299
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 300
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 301
Privileges These privileges are used by the GRANT and REVOKE statements. For further information on GRANT, see “GRANT Statement” (page 136), “GRANT EXECUTE Statement” (page 138), or “GRANT SCHEMA Statement” (page 140). For further information on REVOKE, see “REVOKE Statement” (page 156), “REVOKE EXECUTE Statement” (page 158), or “REVOKE SCHEMA Statement” (page 160).
PAGE 302
• • • • • 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 • • • • • • • • • • • • • • • • • • • • • • • • • • 302 SELECT - Can use SELECT statement. DELETE - Can use DELETE statement.
PAGE 303
• • • • • • 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 304
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 305
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 306
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 307
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 164). 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 308
Correlated Subqueries When Providing Comparison Values In a subquery, when you refer to columns of any table or view defined in an outer query, the reference is called an outer reference. A subquery containing an outer reference is called a correlated subquery. If you refer to a column name that occurs in more than one outer query, you must qualify the column name with the correlation name of the table or view to which it belongs.
PAGE 309
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 310
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 311
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 96), “ALTER TRIGGER Statement” (page 53), “DROP TRIGGER Statement” (page 119).
PAGE 312
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 313
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 314
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 315
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 316
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 317
-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 318
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 164). SAMPLE is a Neoview SQL extension.
PAGE 319
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 320). BALANCE If you specify a BALANCE expression, Neoview SQL performs stratified sampling.
PAGE 320
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 321
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 322
• 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 323
(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 324
--- 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 325
39 75 ... GRAPHICPRINTER, M3 LASERPRINTER, X1 ... S W ... 20000.00 42000.00 ... --- 30 row(s) selected.
PAGE 326
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 164). SEQUENCE BY is a Neoview SQL extension. SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] | DESC[ENDING]]]...
PAGE 327
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 328
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 329
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 330
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 331
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 245).
PAGE 332
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 333
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 334
• 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 335
• 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 336
PAGE 337
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 338
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 339
“SUBSTRING/SUBSTR Function” (page 471) Extracts a substring from a character string. “TRANSLATE Function” (page 479) Translates a character string from a source character set to a target character set. “TRIM Function” (page 480) Removes leading or trailing characters from a character string. “UCASE Function” (page 481) Upshifts alphanumeric characters. You can also use UPSHIFT or UPPER. “UPPER Function” (page 482) Upshifts alphanumeric characters. You can also use UPSHIFT or UCASE.
PAGE 340
“DAYOFMONTH Function” (page 389) 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 390) Returns an integer value in the range 1 through 7 that represents the corresponding day of the week. “DAYOFYEAR Function” (page 391) Returns an integer value in the range 1 through 366 that represents the corresponding day of the year.
PAGE 341
“DEGREES Function” (page 395) Converts a numeric value expression expressed in radians to the number of degrees. “EXP Function” (page 400) Returns the exponential value (to the base e) of a numeric value expression. “FLOOR Function” (page 405) Returns the largest integer less than or equal to a numeric value expression. “LOG Function” (page 414) Returns the natural logarithm of a numeric value expression. “LOG10 Function” (page 415) Returns the base 10 logarithm of a numeric value expression.
PAGE 342
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 396) Calculates differences between values of a column expression in the current row and previous rows. “DIFF2 Function” (page 398) Calculates differences between values of the result of DIFF1 of the current row and DIFF1 of previous rows.
PAGE 343
“ROWS SINCE CHANGED Function” (page 453) Returns the number of rows counted since the specified set of values last changed. “THIS Function” (page 476) 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 326) and the individual entry for each function.
PAGE 344
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 265).
PAGE 345
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 265). Examples of ACOS • The ACOS function returns the value 3.49044274380724416E-001 or approximately 0.
PAGE 346
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 257).
PAGE 347
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 255).
PAGE 348
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 265). Examples of ASIN • This function returns the value 3.49044414403046400E-001 or approximately 0.
PAGE 349
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 265). Examples of ATAN • This function returns the value 8.72766423249958272E-001 or approximately 0.8727 in radians (which is 50 degrees): ATAN (1.
PAGE 350
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 265). Example of ATAN2 This function returns the value 2.
PAGE 351
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 352
--- 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 353
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 354
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 355
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 356
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 357
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 357).
PAGE 358
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 359
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 265). Example of CEILING This function returns the integer value 3.
PAGE 360
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 361
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 362
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 363
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 364
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 365
INSERT INTO stats.
PAGE 366
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 367
----------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 368
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 369
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 265). 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 370
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 265). Example of COSH This function returns the value 1.88842387716101568E+000, or approximately 1.8884, the hyperbolic cosine of 1.
PAGE 371
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 372
----------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 373
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 377). 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 374
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 375
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 376
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 377
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 373). 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 378
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 484). 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 379
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 337). 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 380
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 337). 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 381
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 337). 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 382
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 383
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 257). DEFAULT | USA | EUROPEAN specifies a format for a datetime value.
PAGE 384
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 385
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 386
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 387
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 257).
PAGE 388
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 257).
PAGE 389
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 257).
PAGE 390
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 257).
PAGE 391
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 257).
PAGE 392
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 393
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 394
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 395
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 265). Examples of DEGREES • This function returns the value 45.0001059971939008 in degrees: DEGREES (0.78540) • This function returns the value of 45.
PAGE 396
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 326). 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 397
--- 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 398
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 326). 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 399
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 396).
PAGE 400
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 265). 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 401
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 402). 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 402
qid is a case-sensitive identifier, which represents the query ID. For example: 'QID=MXID01001011194212103659400053369000000085905admin00_2605_S1' The EXPLAIN function or statement returns the plan that was generated when the query was prepared. EXPLAIN for QID retrieves all the information from the original plan of the executing query. The plan is available until the query finishes executing and is removed or deallocated.
PAGE 403
The DETAIL_COST column of the EXPLAIN function results contains these cost factors: CPU_TIME An estimate of the number of seconds of processor time it might take to execute the instructions for this operator. A value of 1.0 is 1 second. IO_TIME An estimate of the number of seconds of I/O time (seeks plus data transfer) to perform the I/O for this operator. MSG_TIME An estimate of the number of seconds it takes for the messaging for this operator.
PAGE 404
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 257) and “Interval Value Expressions” (page 261).
PAGE 405
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 265). Examples of FLOOR This function returns the integer value 2.00000000000000000E+000, represented as a FLOAT data type: FLOOR (2.
PAGE 406
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 257).
PAGE 407
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 408
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 409
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 410
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 326). 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 411
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 416).
PAGE 412
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 413
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 440). LOCATE is a Neoview SQL extension.
PAGE 414
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 265). Example of LOG This function returns the value 6.93147180559945344E-001, or approximately 0.69315: LOG (2.
PAGE 415
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 265). Example of LOG10 This function returns the value 1.39794000867203776E+000, or approximately 1.
PAGE 416
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 417
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 255). 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 418
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 480). 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 255).
PAGE 419
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 420
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 421
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 257).
PAGE 422
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 423
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 257).
PAGE 424
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 257).
PAGE 425
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 326). 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 426
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 326). 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 427
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 326). 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 428
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 326). 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 429
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 326). 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 430
-------------------.000 15527.357 14802.016 15115.012 6036.275 --- 5 row(s) selected.
PAGE 431
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 326). 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 432
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 326). 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 433
.000 241098840.500 219099697.000 228463602.333 36436621.000 --- 5 row(s) selected.
PAGE 434
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 435
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 436
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 437
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 255).
PAGE 438
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 326). 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 439
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 440
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 413).
PAGE 441
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 265).
PAGE 442
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 257).
PAGE 443
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 265). Examples of RADIANS • Return the value 7.85398150000000000E-001, or approximately 0.78540 in degrees: RADIANS (45) • Return the value 45 in degrees.
PAGE 444
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 445
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 446
--- 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 447
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 255).
PAGE 448
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 449
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 450
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 451
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 326). 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 452
(EXPR) --------------? 1 2 1 2 (EXPR) --------------0 1 0 1 0 --- 5 row(s) selected.
PAGE 453
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 326). ROWS SINCE CHANGED is a Neoview SQL extension.
PAGE 454
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 255). 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 455
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 480). 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 255).
PAGE 456
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 326). RUNNINGAVG is a Neoview SQL extension. RUNNINGAVG (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 457
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 326). 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 458
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 326). RUNNINGMAX is a Neoview SQL extension. RUNNINGMAX (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 459
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 326). RUNNINGMIN is a Neoview SQL extension. RUNNINGMIN (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 460
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 326). RUNNINGSTDDEV is a Neoview SQL extension. RUNNINGSTDDEV (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 461
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 326). RUNNINGSUM is a Neoview SQL extension. RUNNINGSUM (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 462
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 326). RUNNINGVARIANCE is a Neoview SQL extension. RUNNINGVARIANCE (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 463
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 257).
PAGE 464
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 265).
PAGE 465
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 265). 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 466
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 265). Example of SINH This function returns the value 1.60191908030082560E+000, or approximately 1.6019, the hyperbolic sine of 1.25: SINH (1.
PAGE 467
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 468
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 265). Example of SQRT This function returns the value 5.19615242270663232E+000, or approximately 5.
PAGE 469
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 470
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 471
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 472
• • 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 473
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 474
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 265). 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 475
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 265). Example of TANH • This function returns the value 8.48283639957512960E-001 or approximately 0.8483, the hyperbolic tangent of 1.
PAGE 476
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 451). THIS is a Neoview SQL extension. THIS (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 477
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 478
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 479
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 480
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 481
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 482
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 483) or “UCASE Function” (page 481).
PAGE 483
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 484
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 378). 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 485
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 486
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 487
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 488
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 257). 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 489
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 490
PAGE 491
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 492
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 493
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 494
SELECT deptnum, empnum, AVG(SALARY) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 495
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 496
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 497
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 498
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 499
SELECT deptnum, empnum, MAX(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 500
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 501
SELECT deptnum, empnum, MIN(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 502
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 503
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 504
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 505
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 506
SELECT deptnum, empnum, SUM (salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 507
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 508
PAGE 509
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 510
Table A-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) 510 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 511
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 512
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 513
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 514
Table A-2 One-to-One UCS2 Mappings (continued) 514 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 515
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 516
Table A-3 Two-Character UCS2 Mapping (continued) 516 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 517
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 518
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 519
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 520
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 521
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 522
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 523
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 524
PAGE 525
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 526
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 527
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 528
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 529
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 530
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 531
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 532
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 533
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 534
PAGE 535
Index A ABS function examples of, 344 syntax diagram of, 344 Access options summary of, 25 DELETE statement use of, 109 DML statements use of, 25 INSERT statement use of, 144 READ COMMITTED, 26 READ UNCOMMITTED , 26 REPEATABLE READ, 26 SELECT statement use of, 172 SERIALIZABLE, 26, 27, 33 SKIP CONFLICT, 26 UPDATE statement use of, 200 Access privileges stored procedures, 138 tables, 136, 140 ACOS function examples of, 345 syntax diagram of, 345 ADD_MONTHS function examples of, 346 syntax diagram of, 346 AFT
PAGE 536
Bulk replicator REPLICATE command, 224 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, 355 examples of, 355 searched CASE form, 354 syntax diagram of, 354 CAST expression data type conversion, 357 examples of, 357 syntax diagram of, 357 valid type combinations, 357 CEILING function examples of, 359 syntax diagram of, 359 CHAR data type, 248, 249 CHAR function examples of, 360 synta
PAGE 537
Constraints CHECK, 237 description of, 237 FOREIGN KEY, 46, 77, 237 limits, 523 NOT NULL, 237 PRIMARY KEY, 237 CONVERTTIMESTAMP function examples of, 368 JULIANTIMESTAMP inverse relationship to, 368 syntax diagram of, 368 CONVERTTOHEX function examples of, 366, 367 syntax diagram of, 366 Correlation names examples of, 238 purpose of, 238 table reference use of, 238 COS function examples of, 369 syntax diagram of, 369 COSH function examples of, 370 syntax diagram of, 370 COUNT function DISTINCT clause within
PAGE 538
nullable keys, 80 restrictions, 79 SET table, example of, 94 suitable keys, 80 syntax diagram of, 72 CROSS JOIN, description of, 67, 169 CURRENT_DATE function examples of, 374 syntax diagram of, 374 CURRENT_ROLE function example of, 375 syntax diagram of, 375 CURRENT_TIME function examples of, 376 precision specification within, 376 syntax diagram of, 376 CURRENT_TIMESTAMP function examples of, 373, 377 precision specification within, 373, 377 syntax diagram of, 373, 377 CURRENT_USER function example of, 37
PAGE 539
examples of, 386 syntax diagram of, 386 DATEADD function examples of, 381 syntax diagram of, 381 DATEDIFF function examples of, 382 syntax diagram of, 382 DATEFORMAT function examples of, 383 syntax diagram of, 383 Datetime data types DATE, 250 description of, 250 examples of literals, 276 TIME, 250 TIMESTAMP, 250 Datetime functions summary of, 339 ADD_MONTHS, 346 CONVERTTIMESTAMP, 368 CURRENT, 373 CURRENT_DATE, 374 CURRENT_TIME, 376 CURRENT_TIMESTAMP, 377 DATE_ADD, 379 DATE_PART (interval), 384 DATE_PART (
PAGE 540
DISK POOL considerations for, 92 description for, 75 examples of, 93 restrictions for, 92 DISTINCT clause aggregate functions, 175, 337 AVG function use of, 351 COUNT function use of, 371 MAX function use of, 419 MAXIMUM function use of, 419 MIN function use of, 420 STDDEV function use of, 469 SUM function use of, 473 VARIANCE function use of, 485 DML statements (see Data Manipulation Language (DML) statements) Documents, related information, 22 DOUBLE PRECISION data type, 254 DROP INDEX statement authoriza
PAGE 541
Expression character (or string) value, 255 datetime value, 257, 258, 262 description of, 255 interval value, 258, 262 numeric value, 265 Extended numeric precision, 246 Extensions, statements, 34 EXTRACT function examples of, 404 syntax diagram of, 404 F File options CREATE INDEX use of, 60 CREATE TABLE use of, 72 Fixed-length character column, 249 FLOAT data type, 254 FLOOR function examples of, 405 syntax diagram of, 405 FOREIGN KEY constraint, 47, 77, 106, 237 FORMAT clause considerations for date form
PAGE 542
syntax diagram of, 408 ISO standards, 525 Isolation levels guidelines for CREATE VIEW, 103 READ COMMITTED, 32 READ UNCOMMITTED, 32 REPEATABLE READ, 33 SERIALIZABLE, 26, 27, 33 J Join CROSS, 169 FULL, 170 JOIN ON, 169 join predicate, 179 LEFT, 170 limits, 174 NATURAL, 169 NATURAL FULL, 169 NATURAL LEFT, 169 NATURAL RIGHT, 169 optional specifications, 169 RIGHT, 170 types, 169 JOIN ON join, description of, 169 JULIANTIMESTAMP function examples of, 409 syntax diagram of, 409 K Keys clustering, 272 hash parti
PAGE 543
LOWER function examples of, 416 syntax diagram of, 416 LPAD function examples of, 417 syntax diagram of, 417 LTRIM function examples of, 418 syntax diagram of, 418 M Magnitude, 266 MAINTAIN command considerations for, 216 description of, 213 examples of, 217, 218 multiple table reorg, 216 OVERRIDE option, 216 syntax diagram of, 213 uses, 212 MAINTAIN MVGROUP command, 217 Materialized aggregate view definition of, 65, 281 restricitions for, 67 Materialized join view definition of, 65, 281 restrictions for,
PAGE 544
syntax diagram of, 426 MOVINGMAX function examples of, 427 syntax diagram of, 427 MOVINGMIN function examples of, 428 syntax diagram of, 428 MOVINGSTDDEV function examples of, 429 syntax diagram of, 429 MOVINGSUM function examples of, 431 syntax diagram of, 431 MOVINGVARIANCE function examples of, 432 syntax diagram of, 432 MVGROUP, definition of, 282 N NATIONAL CHAR data type, 248 NATIONAL CHAR VARYING data type, 249 NATURAL FULL join, description of, 169 NATURAL join, description of, 169 NATURAL LEFT joi
PAGE 545
ORDER BY clause, 176 SAMPLE statement, cluster sampling, 320 updating rows, 200 Persistent sample tables, 207 PI function examples of, 439 syntax diagram of, 439 POPULATE INDEX utility considerations for, 220 examples of, 221 syntax diagram of, 220 POSITION function examples of, 440 result of, 440 syntax diagram of, 440 POWER function examples of, 441 syntax diagram of, 441 Precision, description of, 266 Predicates summary of, 286 BETWEEN, 286 comparison, 287 description of, 286 EXISTS, 291 IN, 292 LIKE, 29
PAGE 546
status, 228 syntax diagram of, 224 using JDBC or ODBC applications, 224 REQUESTS_IN, 127 Reserved schemas for HP, 70 table names, 86 words, Neoview SQL, 519 Resource control statements , 38 EXECUTE statement, 121 LOCK TABLE statement, 149 PREPARE statement, 154 UPDATE STATISTICS statement, 204 REVOKE EXECUTE statement authorization and availability requirements, 159 considerations for, 159 examples of, 159 syntax diagram of, 158 REVOKE SCHEMA statement authorization and availability requirements, 162 consid
PAGE 547
DISTINCT clause , 166 embedded delete, 168 embedded insert, 169 embedded update, 168, 169 examples of, 178 FROM clause , 167 GROUP BY clause , 171, 175 HAVING clause, 172 joined table within, 169 limit on join tables, 174 lock modes, 172 ORDER BY clause , 173, 176 RETURN list, 168 select list elements, 166 SEQUENCE BY clause, 171 simple table within, 170 SKIP CONFLICT access, 172 stream access limitations, 174 STREAM clause, 167 syntax diagram of, 164 TRANSPOSE clause, 171 UNION ALL operation, 177 union ope
PAGE 548
syntax diagram of, 468 Standards ANSI SQL, 525 character set support, 529 ISO, 525 Statement atomicity automatic, 28 description of, 28 Statements, SQL ANSI compliant, 33 Neoview SQL extensions, 34 Statistics clearing, 205 UPDATE STATISTICS statement, 207, 209 STDDEV function DISTINCT clause within, 469 examples of, 470 statistical definition of, 469 syntax diagram of, 469 STDDEV window function examples of, 504 syntax diagram of, 504 STORE BY clause, 74 Stored procedure statements, 39 CALL, 56 GRANT EXECUT
PAGE 549
Transaction control statements BEGIN WORK, 55 COMMIT WORK, 59 ROLLBACK WORK, 163 SET TRANSACTION statement, 188 Transaction isolation levels READ COMMITTED, 32 READ UNCOMMITTED, 32 REPEATABLE READ, 33 SERIALIZABLE, 33 Transaction management, 28 AUTOCOMMIT, effect of, 29 BEGIN WORK, 55 COMMIT WORK, 59 ROLLBACK WORK, 163 rules for DML statements, 29 SET TRANSACTION , 188 Transaction rollback mode, 33 TRANSLATE function, syntax diagram of, 479 TRANSPOSE clause cardinality of result, 331 degree of result, 330 e
PAGE 550
example of, 487 syntax diagram of, 487 window functions AVG , 493 considerations, 491 COUNT , 495 DENSE_RANK , 497 limitations, 492 MAX , 498 MIN , 500 ORDER BY clause, use of, 491 RANK , 502 ROW_NUMBER, 503 STDDEV, 504 SUM, 505 VARIANCE, 507 Y YEAR function examples of, 488 syntax diagram of, 488 Z ZEROIFNULL function example of, 489 syntax diagram of, 489 550 Index
PAGE 551
PAGE 552