Neoview SQL Reference Manual (R2.3)

ManualsBrandsHP ManualsSoftwareHP Neoview Release 2.3 Software

HP Neoview SQL Reference Manual

HP Part Number: 544710-001

Published: April 2008

Edition: HP Neoview Release 2.3

Summary of content (484 pages)

PAGE 1
HP Neoview SQL Reference Manual HP Part Number: 544710-001 Published: April 2008 Edition: HP Neoview Release 2.
PAGE 2
© Copyright 2008 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.......................................................................................................23 Supported Release Version Updates (RVUs)........................................................................................23 Intended Audience................................................................................................................................23 New and Changed Information in This Edition...............................................
PAGE 4
2 SQL Statements.............................................................................................................41 Categories.............................................................................................................................................41 Data Definition Language (DDL) Statements.................................................................................41 Data Manipulation Language (DML) Statements..............................................................
PAGE 5
Considerations for CREATE INDEX...............................................................................................59 Authorization and Availability Requirements...........................................................................59 Limits on Indexes.......................................................................................................................59 Example of CREATE INDEX....................................................................................................
PAGE 6
Considerations for CREATE TRIGGER...........................................................................................83 Authorization and Availability Requirements...........................................................................83 Trigger Types..............................................................................................................................83 Restrictions on Triggers..................................................................................................
PAGE 7
Syntax Description of DROP TRIGGER........................................................................................101 Considerations for DROP TRIGGER.............................................................................................101 Authorization and Availability Requirements.........................................................................101 Examples of DROP TRIGGER.......................................................................................................
PAGE 8
Using Compatible Data Types..................................................................................................127 Examples of INSERT......................................................................................................................128 Examples of Self-Referencing Inserts.......................................................................................129 LOCK TABLE Statement................................................................................................
PAGE 9
Stream Access Restrictions.......................................................................................................155 Restrictions on Embedded Inserts............................................................................................155 DISTINCT Aggregate Functions..............................................................................................155 Limitations of DISTINCT Aggregates......................................................................................
PAGE 10
Reporting of Updates...............................................................................................................182 Updating Character Values......................................................................................................182 SET Clause Restrictions and Error Cases.................................................................................183 SET ON ROLLBACK Considerations......................................................................................
PAGE 11
Character Data Types...............................................................................................................209 Datetime Data Types................................................................................................................209 Interval Data Types...................................................................................................................210 Numeric Data Types......................................................................................
PAGE 12
Comparison Predicates..................................................................................................................244 Considerations for Comparison Predicates..............................................................................245 Examples of Comparison Predicates........................................................................................246 EXISTS Predicate..............................................................................................................
PAGE 13
Degree and Column Order of the TRANSPOSE Result...........................................................282 Data Type of the TRANSPOSE Result......................................................................................282 Cardinality of the TRANSPOSE Result....................................................................................283 Examples of TRANSPOSE.............................................................................................................
PAGE 14
CHAR_LENGTH Function.................................................................................................................313 Considerations for CHAR_LENGTH............................................................................................313 CHAR and VARCHAR Operands............................................................................................313 Examples of CHAR_LENGTH......................................................................................................
PAGE 15
Examples of DATE_TRUNC..........................................................................................................335 DAY Function......................................................................................................................................336 Example of DAY.............................................................................................................................336 DAYNAME Function.......................................................................
PAGE 16
Examples of LOCATE....................................................................................................................362 LOG Function......................................................................................................................................363 Example of LOG............................................................................................................................363 LOG10 Function....................................................................
PAGE 17
Similarity to CHAR_LENGTH Function..................................................................................391 Example of OCTET_LENGTH.......................................................................................................391 OFFSET Function................................................................................................................................392 Example of OFFSET..................................................................................................
PAGE 18
RUNNINGMAX Function..................................................................................................................408 Example of RUNNINGMAX.........................................................................................................408 RUNNINGMIN Function...................................................................................................................408 Example of RUNNINGMIN......................................................................................
PAGE 19
Examples of TRIM.........................................................................................................................429 UCASE Function.................................................................................................................................430 Considerations for UCASE............................................................................................................430 Examples of UCASE.......................................................................
PAGE 20
C Limits...........................................................................................................................463 D Standard SQL and Neoview SQL............................................................................465 ANSI SQL Standards...........................................................................................................................465 ISO Standards.............................................................................................................
PAGE 21
List of Tables 1-1 1-2 1-3 1-4 2-1 2-2 2-3 2-4 2-5 6-1 6-2 6-3 6-4 B-1 Concurrent DDL/Utility Operation and File Access Modes.........................................................34 Concurrent DDL/Utility and DML Operations.............................................................................34 Concurrent DML and DDL Operations .......................................................................................34 Operations Effect on Table Timestamps ...........................................
PAGE 22
PAGE 23
About This Document This manual describes reference information about the syntax of SQL statements, functions, and other SQL language elements supported by the Neoview database software. The Neoview SQL statements and utilities are entered interactively or from script files using the client-based utility, Neoview Command Interface (NCI). For information on NCI, see the Neoview Command Interface (NCI) Guide.
PAGE 24
Document Organization • • • • • • • • • • • Chapter 1 (page 29), introduces Neoview SQL and covers topics such as database security, data consistency and integrity, transaction management, and ANSI compliance. Chapter 2 (page 41), describes the SQL statements supported by Neoview SQL. Chapter 3 (page 191), describes utilities that perform tasks such as maintenance, populating indexes, purging data from tables and indexes, and reorganizing and reloading data in a table or index.
PAGE 25
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 26
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 27
Neoview Reports Online Help Help topics that describe how to use the HP Neoview Reports Tool. Neoview Transporter User Guide Information about processes and commands for loading data into your Neoview platform or extracting data from it. Neoview Workload Management Information about using Neoview Workload Management Services (WMS) to Services Guide manage workload and resources on a Neoview data warehousing platform.
PAGE 28
Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
PAGE 29
1 Introduction The Neoview SQL database software allows you to use SQL DML statements, which comply closely to ANSI SQL:1999, to access SQL databases.
PAGE 30
Data Consistency and Access Options Access options for DML statements affect the consistency of the data that your query accesses. For any DML statement, you specify access options by using the FOR option ACCESS clause and, for a SELECT statement, by using this same clause, you can also specify access options for individual tables referenced in the FROM clause.
PAGE 31
SERIALIZABLE or REPEATABLE READ This option locks all data accessed through the DML statement and holds the locks on data in tables until the end of any containing transaction. SERIALIZABLE (or REPEATABLE READ) provides the highest level of data consistency. A statement executing with this access option does not allow dirty reads, nonrepeatable reads, or phantoms. SKIP CONFLICT This option allows transactions to skip rows locked in a conflicting mode by another transaction.
PAGE 32
Lock mode is sometimes determined by Neoview SQL. SQL ensures that an exclusive lock is in effect for write operations and usually acquires a shared lock for operations that access data without modifying it. You choose lock mode in these instances: • • On the LOCK TABLE statement, you can choose EXCLUSIVE or SHARE. On the SELECT statement, you can specify IN EXCLUSIVE MODE or IN SHARE MODE.
PAGE 33
— — — — — — • • A publish/subscribe query with stream access A CALL statement A holdable cursor A SELECT statement with an embedded UPDATE or DELETE A DDL statement An UPDATE STATISTICS statement The query plan does not choose VSBB inserts or Executor Server Process (ESP) parallelism. The AUTOCOMMIT option must be set to ON. If these conditions are not met, the transaction is aborted by Neoview SQL if a failure occurs. This behavior occurs for all INSERT, UPDATE, or DELETE statements in Neoview SQL.
PAGE 34
Table 1-1 Concurrent DDL/Utility Operation and File Access Modes Access Mode DDL Operations You Can Start READ UNCOMMITTED READ COMMITTED SERIALIZABLE ALTER TABLE attributes Allowed1 Allowed1 Waits 1 DDL operation aborts the DML operation.
PAGE 35
Table 1-3 Concurrent DML and DDL Operations (continued) DML Operations You Can Start CREATE TRIGGER subject table Allowed Allowed Waits Waits CREATE TRIGGER reference table Allowed Allowed Allowed Allowed CREATE VIEW Allowed Allowed Allowed Allowed GRANT Allowed1 Waits Waits Waits POPULATE INDEX Allowed1 Allowed2 Allowed2 Waits REVOKE Allowed1 Allowed Waits Waits UPDATE STATISTICS Allowed Allowed Allowed Allowed2 1 2 DDL operation aborts the DML operation.
PAGE 36
If the transaction isolation level is READ UNCOMMITTED, the default access mode is READ ONLY. Further, for READ COMMITTED, you can specify only READ ONLY explicitly by using the SET TRANSACTION statement. 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.
PAGE 37
The default isolation level is READ COMMITTED. SERIALIZABLE or REPEATABLE READ This option locks all data accessed through the transaction and holds the locks on data until the end of the transaction. SERIALIZABLE provides the highest level of data consistency. A transaction executing with this isolation level does not allow dirty reads, nonrepeatable reads, or phantoms. Transaction Rollback Mode The Rollback Mode for a transaction can be set to either ON or OFF.
PAGE 38
• • • • • • • • • • • • • • DROP VIEW statement GRANT statement GRANT EXECUTE statement GRANT SCHEMA statement INSERT statement PREPARE statement REVOKE statement REVOKE EXECUTE statement REVOKE SCHEMA statement ROLLBACK WORK statement SELECT statement SET SCHEMA statement SET TRANSACTION statement UPDATE statement Statements That Are Neoview SQL Extensions These statements are Neoview SQL extensions to the ANSI standard.
PAGE 39
• • • • • • 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. The message number is the SQLCODE value (without the sign).
PAGE 40
PAGE 41
2 SQL Statements This section describes the syntax and semantics of Neoview SQL statements. The Neoview SQL statements and utilities are entered interactively or from script files using the command-line interface, Neoview Command Interface (NCI). For information on Neoview Command Interface, see the Neoview Command Interface (NCI) Guide.
PAGE 42
“DROP VIEW Statement” (page 102) Drops a view. “GRANT Statement” (page 118) Grants access privileges for a table or view to specified roles. “GRANT EXECUTE Statement” (page 120) Grants privileges for executing a stored procedure in Java (SPJ) to specified roles. “GRANT SCHEMA Statement” (page 122) Grants access privileges for a schema to specified roles. “REVOKE Statement” (page 137) Revokes access privileges for a table or view from specified roles.
PAGE 43
Control Statements Use these statements to control the execution default options, plans, and performance of DML statements: “SET TABLE TIMEOUT Statement” (page 168) Specifies a dynamic timeout value in the run-time environment of the current session. Object Naming Statements Use these statements to specify default ANSI names for the schema: “SET SCHEMA Statement” (page 166) Sets the default ANSI schema for unqualified object names for the current session.
PAGE 44
ALTER MATERIALIZED VIEW Statement • • “Syntax Description of ALTER MATERIALIZED VIEW” (page 44) “Considerations for ALTER MATERIALIZED VIEW” (page 44) The ALTER MATERIALIZED VIEW statement changes a materialized view.. See “Database Object Names” (page 206). 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 45
ALTER MVGROUP Statement • • “Syntax Description of ALTER MATERIALIZED VIEW” “Considerations for ALTER MATERIALIZED VIEW” The ALTER MVGROUP statement allows you to add or remove a member from the materialized view group (MVGROUP). The ADD clause should be used when adding one or more MVs to the MVGROUP. The REMOVE clause should be used when removing one or more MVs from an MVGROUP. For information on MVGROUPS, see “MVGROUPs ” (page 264). ALTER MVGROUP mv-group-name {ADD mv-name [, mv-name] ...
PAGE 46
ALTER SYNONYM Statement • • • “Syntax Description of ALTER SYNONYM ” “Considerations” “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. The advantage of this is to change the object being referenced without changing the query.
PAGE 47
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 206).
PAGE 48
NO INSERTLOG | INSERTLOG specifies whether update and delete operations are recorded in the table’s IUD log. When a table is defined with the INSERTLOG attribute, logging operations ignore delete and update operations and log inserts only. ADD [COLUMN] column-definition adds a column to table. This option is valid only for the ALTER TABLE statement. The clauses for the column-definition are: column-name specifies the name for the new column in the table. column-name is an SQL identifier.
PAGE 49
column data-type specifies the name and data type for a column in the table. column is an SQL identifier. column must be unique among column names in the table. If the name is a Neoview SQL reserved word, you must delimit it by enclosing it in double quotes. Such delimited parts are case-sensitive. For example: "join". data-type is the data type of the values that can be stored in column. A default value must be of the same type as the column, including the character set for a character column.
PAGE 50
ALTER TRIGGER Statement • • “Syntax Description of ALTER TRIGGER” “Considerations for ALTER TRIGGER” The ALTER TRIGGER statement is used to enable or disable triggers, individually or by SQL tables. ALTER TRIGGER { ENABLE trigger-name | ENABLE ALL OF table-name | DISABLE trigger-name | DISABLE ALL OF table-name}; Syntax Description of ALTER TRIGGER trigger-name specifies the name of the trigger to alter. See “Database Object Names” (page 206).
PAGE 51
ALTER VIEW Statement • • “Syntax Description of ALTER VIEW” “Example of ALTER VIEW” The ALTER VIEW statement adds a column to a Neoview SQL table. See “Database Object Names” (page 206). 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 206). RENAME new-name changes the logical name of the object within the same schema.
PAGE 52
BEGIN WORK Statement The BEGIN WORK statement enables you to start a transaction explicitly—where the transaction consists of the set of operations defined by the sequence of SQL statements that begins immediately after BEGIN WORK and ends with the next COMMIT or ROLLBACK statement. See “Transaction Management” (page 32). BEGIN WORK is a Neoview SQL extension.
PAGE 53
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 54
Input Parameter Arguments You pass data to an SPJ by using IN or INOUT parameters.
PAGE 55
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 56
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 32). COMMIT [WORK] WORK is an optional keyword that has no effect. COMMIT WORK has no effect outside of an active transaction.
PAGE 57
COMMIT WORK; --- SQL operation complete.
PAGE 58
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 206). CREATE INDEX is a Neoview SQL extension.
PAGE 59
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. If an index is created with the intention of using it for a constraint, you must populate it before creating the constraint.
PAGE 60
CREATE MATERIALIZED VIEW Statement • • • “Syntax Description of CREATE MATERIALIZED VIEW” “Considerations for CREATE MATERIALIZED VIEW” “Example of CREATE MATERIALIZED VIEW” The CREATE MATERIALIZED VIEW statement creates a materialized view. 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 61
column-name-list specifies names for the columns in the materialized view, as well as headings for the columns. Column names in the list correspond directly to columns in the query-expr. If you omit this clause, columns in the materialized view will have the same names as the corresponding columns in the query-expr.
PAGE 62
Hash partitioning is the only partitioning scheme supported for materialized views. This clause defines the partitioning keys of the materialized view. non-aggregate-column-expr col-expr is a single column name or a derived column. A derived column (derived-name) is an SQL value expression; its operands can be numeric, string, datetime, or interval literals, columns, functions defined on columns, scalar subqueries, CASE expressions, or CAST expressions.
PAGE 63
view must have the grant option on the underlying objects. In order to create a materialized view in the schema, the creator must be the schema owner, services ID, or have the CREATE privilege The INSERT privilege is used to authorize the incremental REFRESH. You must have INSERT privileges on a materialized view or be the services ID to perform an incremental REFRESH on it. The INSERT privilege can be at the object or schema level.
PAGE 64
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 65
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.storekey; • This is an example of a MAV on and explicit join: CREATE MATERIALIZED VIEW sales_by_day_category REFRESH ON REQUEST INITIALIZE ON REFRESH AS SELECT part.category, time.
PAGE 66
CREATE MVGROUP Statement The CREATE MVGROUP statement groups together materialized views that are defined on a common table to preserve database consistency. For information on MVGROUPS, see “MVGROUPs ” (page 264)and “MAINTAIN MVGROUP” (page 194). CREATE MVGROUP mv-group-name mv-group-name specifies the materialized view group name to create. Authorization and Availability Requirements The schema owner or anyone having the CREATE privilege can create an MV group.
PAGE 67
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 257). 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 68
CREATE SYNONYM Statement • • • “Syntax Description of CREATE SYNONYM ” “Considerations” “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. The advantage of this is to change the object being referenced without changing the query.
PAGE 69
CREATE TABLE Statement • • • “Syntax Description of CREATE TABLE” “Considerations for CREATE TABLE” “Examples of CREATE TABLE” The CREATE TABLE statement creates a Neoview SQL table. The CREATE VOLATILE TABLE statement creates a Neoview SQL table in a SQL session. Volatile tables are dropped automatically when the session ends.
PAGE 70
query. A CREATE SET TABLE is like any other normal table with the additional property of discarding duplicate rows. See “Database Object Names” (page 206). CREATE [SET][VOLATILE] TABLE table (table-element [,table-element]...) | table-spec | like-spec } [NO PARTITION | HASH PARTITION BY (partitioning-column, partitioning-column...
PAGE 71
default is: literal | NULL | CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP column-constraint is: NOT NULL | PRIMARY KEY [ASC[ENDING] | DESC[ENDING]] | CHECK (condition) column-list is: column-name [,column-name]... table-constraint is: PRIMARY KEY (key-column-list) |CHECK (condition) [NOT CASESPECIFIC] key-column-list is: column-name [ASC[ENDING] | DESC[ENDING]] [,column-name [ASC[ENDING] | DESC[ENDING]]]... like-spec is: LIKE source-table [include-option]...
PAGE 72
MAX TABLE SIZE megabytes specifies the table size as a number of megabytes. If you do not specify MAX TABLE SIZE, a table is created with the following default characteristics: • For a partitioned table, the space allocated after you enter the first row is 100 MB times number of partitions. The maximum size a partitioned table can reach is the size of the disk at the time of table creation.
PAGE 73
NOT NULL is a column constraint that specifies that the column cannot contain nulls. If you omit NOT NULL, nulls are allowed in the column. If you specify both NOT NULL and NO DEFAULT, each row inserted in the table must include a value for the column. See “Null” (page 240). PRIMARY KEY [ASC[ENDING] | DESC[ENDING]] or PRIMARY KEY (key-column-list) is a column that specifies a column or set of columns as the primary key for the table.
PAGE 74
new table partitions do not inherit partition names from the original table. Instead, Neoview SQL generates new names based on the physical file location. If you specify the LIKE clause and the PARTITION file-option, you cannot specify WITH PARTITIONS. Considerations for CREATE TABLE You can create partitioned and non-partitioned tables. To create a non-partitioned table, specify the NO PARTITION option with the CREATE TABLE command.
PAGE 75
Reserved Table Names Table names prefixed by the name of a user metadata table are reserved. You cannot create tables with such names. For example, you cannot create tables that are prefixed by these names: • • • • • HISTOGRAMS HISTOGRAM_INTERVALS MVS_TABLE_INFO_UMD MVS_UMD MVS_USED_UMD Authorization and Availability Requirements To create a table, you must be the schema owner or have the CREATE or CREATE_TABLE privilege.
PAGE 76
primary key(orderKey, itemNum) ; To calculate this for your specific table, use this formula: (num of variable fields * 8) + (num of nullable fields * 2) + (num of ‘not null droppable’ fields * 2) + 4 + (size of all fields based on type) Columns declared as ‘not null’ have no additional size other then the size of the data type declared. Creating Partitions Automatically When creating a table users can specify that the table is not partitioned using the NO PARTITION clause.
PAGE 77
These restrictions apply to a column defined as an IDENTITY column. Appropriate error messages are generated for each of these restrictions. • • • • • • You cannot add an IDENTITY column using the ALTER TABLE statement. INSERT...SELECT operations are supported. You cannot define a trigger to insert into an IDENTITY column. An IDENTITY column can only be defined on a LARGEINT column. You can specify a SIGNED LARGEINT value if you want to use the negative values.
PAGE 78
• This statement fails with an error stating that only LARGEINT columns can be defined as an IDENTITY column. CREATE TABLE T (surrogate_key CHAR(64) GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, name CHAR (256) NOT NULL, order_number INT UNSIGNED NOT NULL) HASH PARTITION BY(surrogate_key); • This statement fails with an error stating that a table can have only one IDENTITY column.
PAGE 79
• This example creates a volatile table: CREATE VOLATILE TABLE vtable (a int); • This is an example of one-part name usage: CREATE VOLATILE TABLE vtable(a int); INSERT INTO vtable values(1); SELECT * from vtable; CREATE VOLATILE INDEX vindex on vtable(a); DROP VOLATILE INDEX vindex; DROP VOLATILE TABLE vtable; • This is an example of two-part name usage: CREATE VOLATILE TABLE “mysch”.vtable(a int); INSERT INTO mysch.vtable values(1); SELECT * from mysch.vtable; CREATE VOLATILE INDEX vindex on mysch.
PAGE 80
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. SELECT * FROM T WHERE b = ‘A’ (not casespecific); Examples of CREATE SET TABLE This is an example of creating a SET table: >>Create SET table T (a int not null, b int, primary key (a)); --- SQL operation complete. >>insert into T values(1,2); --- 1 row(s) inserted. >>insert into T values(1,2); --- 0 row(s) inserted.
PAGE 81
• If column-attributes are specified and they only contain column-name, then the specified column-name override any name that was derived from the select query. create table t(c,d) as select a,b from t1 Table t has 2 columns, c and d, which has the data attributes of columns a and b from table t1. • If column-attributes are specified, then they must contain attributes corresponding to all select list items in the select-query. An error is returned, if there is a mismatch.
PAGE 82
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.
PAGE 83
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 84
2. 3. Triggering statement AFTER triggered statements Execution of a statement is considered to be complete only when all cascaded triggers are complete. When multiple triggers are activated by the same event (that is, a conflict set), the next trigger from the original conflict set is considered only after the execution of cascaded triggers of a specific trigger is complete (depth-first execution). Within a conflict set, the order of execution is by timestamp of creation of the corresponding trigger.
PAGE 85
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.) • To create a trigger on a given table, the name of the table should be at least six characters less than the maximum length of a valid table name (128 characters). There is a compile time limit of 256 triggers for each statement.
PAGE 86
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 87
record.vital_id), (SELECT record.id FROM record WHERE sample.id = record.
PAGE 88
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 264). CREATE VIEW view [(column-name ] [,column-name ...
PAGE 89
column-name specifies the name for a column in the view. column-name is an SQL identifier. column-name must be unique among column names in the view and cannot be a reserved word. It can contain a reserved word if it is delimited. If you do not specify this clause, columns in the view have the same names as the columns in the select list of query-expr.
PAGE 90
To define an updatable view, a query expression must also meet these requirements: • • • • • • It cannot contain a JOIN, UNION, or EXCEPT clause. It cannot contain a GROUP BY or HAVING clause. It cannot directly contain the keyword DISTINCT. The FROM clause must refer to exactly one table or one updatable view. It cannot contain a WHERE clause that contains a subquery. The select list cannot include expressions or functions or duplicate column names.
PAGE 91
DELETE Statement • • • “Syntax Description of DELETE” “Considerations for DELETE” “Examples of DELETE” The DELETE statement is a DML statement that deletes a row or rows from a table or an updatable view. Deleting rows from a view deletes the rows from the table on which the view is based. DELETE does not remove a table or view, even if you delete the last row in the table or view.
PAGE 92
TRANSACTION statement to enable the NO ROLLBACK option for the transaction. See “SET TRANSACTION Statement” (page 170). 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 206).
PAGE 93
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 94
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 95
DROP INDEX Statement • • • “Syntax Description of DROP INDEX” “Considerations for DROP INDEX” “Example of DROP INDEX” The DROP INDEX statement deletes a Neoview SQL index. See “Database Object Names” (page 206). DROP INDEX is a Neoview SQL extension. DROP [VOLATILE] INDEX index [RESTRICT|CASCADE] Syntax Description of DROP INDEX index is the index to drop. For information, see “Identifiers” (page 230).
PAGE 96
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 97
DROP MVGROUP Statement • • “Considerations for DROP MVGROUP” “Example of DROP MVGROUP” The DROP MVGROUP statement allows you to drop materialized view groups. For information on MVGROUPS, see “MVGROUPs ” (page 264). DROP MVGROUP mv-group-name mv-group-name specifies the materialized view group name to drop. Considerations for DROP MVGROUP • • Dropping an MVGROUP does not affect the MVs that were part of the group. You do not need to cleanup the contents of the group prior to dropping the MVGROUP.
PAGE 98
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 257). 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 99
DROP SYNONYM Statement • • • “Syntax Description of DROP SYNONYM ” “Considerations” “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. 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.
PAGE 100
DROP TABLE Statement • • • “Syntax Description of DROP TABLE” “Considerations for DROP TABLE” “Example 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 206). DROP [VOLATILE] TABLE table [RESTRICT|CASCADE] Syntax Description of DROP TABLE table is the name of the table to delete.
PAGE 101
DROP TRIGGER Statement • • • “Syntax Description of DROP TRIGGER” “Considerations for DROP TRIGGER” “Examples of DROP TRIGGER” The DROP TRIGGER statement is used to drop a trigger on an SQL table. DROP TRIGGER trigger-name; Syntax Description of DROP TRIGGER trigger-name specifies the name of the trigger to be dropped, of the form: [[catalog-name.]schema-name.]trigger-name where each part of the name is a valid SQL identifier with a maximum of 128 characters.
PAGE 102
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 264). 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 103
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 230).
PAGE 104
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 105
--- 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 106
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 350). NOTE: Formatting changes to the EXPLAIN output data might occur in the future.
PAGE 107
Considerations • • • • • • “Case Considerations” (page 107) “Number Considerations” (page 107) “Formatted [OPTIONS 'f'] Considerations” (page 107) “Normal User [OPTIONS 'n'] Considerations” (page 108) “Expert User [OPTIONS 'e'] Considerations” (page 110) “Machine-Readable [OPTIONS 'm'] Considerations” (page 112) Case Considerations In most cases, words in the commands can be in uppercase or lowercase. The options letter must be single quoted and in lowercase.
PAGE 108
The FILE_SCAN operator is the only operator in this example that shows additional information in the OPT and DESCRIPTION fields. 'fs' indicates that fast-scan optimization was used. 'fr' indicates that fast-reply data-move optimization was used. The table name is shown in the DESCRIPTION field. For information about these special optimization techniques, see the listing of each operator in the Neoview Query Guide.
PAGE 109
Table 2-4 Node Listing Information (continued) Field Description EST_TOTAL_COST Estimated cost associated with execution of the current operator and all children. DESCRIPTION Additional information about the operation. For example, in the case of a scan, the description field shows scan_type, scan_direction, lock_mode, access_mode, columns_retrieved, optimization information, and so on. For details about all operators and their description fields, see the Neoview Query Guide.
PAGE 110
buffer_size ....... 31,000 record_length ........ 184 space_usage ............ 8:32:100:144 bottom_partitioning_fun logphys partitioned(grouping, PAPA with 64 PA(s), log=exactly 1 partition, phys=hash2 partitioned 64 ways on (TPCH2X_CAT.TPCH2X_SCH.REGION.R_REGIONKEY)) bottom_node_map ........ (\SQA0101:0-15, \SQA0102:0-15, \SQA0103:0-15, \SQA0104:0-15) begin_part_no_expr ..... \:_sys_hostVarPAPartNo_1539575856 end_part_no_expr .......
PAGE 111
------------------------------------------------------------------ NODE LISTING ROOT ====================================== SEQ_NO 4 ONLY CHILD 3 REQUESTS_IN .............. 1 ROWS_OUT ................. 5 EST_OPER_COST ............ 0.0001 EST_TOTAL_COST ........... 0.0092 cpu_cost ............... 0.0003 io_cost ................ 0.0089 msg_cost ............... 0 idle_cost .............. 1.2005 DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type ..........
PAGE 112
FILE_SCAN ================================= SEQ_NO 1 TABLE_NAME ............... TPCH2X_CAT.TPCH2X_SCH.REGION REQUESTS_IN .............. 1 ROWS_OUT ................. 5 EST_OPER_COST ............ 0.0092 EST_TOTAL_COST ........... 0.0092 cpu_cost ............... 0.0003 io_cost ................ 0.0089 msg_cost ............... 0 idle_cost .............. 1.1025 DESCRIPTION fragment_id ............ parent_frag ............ fragment_type .......... scan_type .............. scan_direction ......... lock_mode .......
PAGE 113
Column Name Data Type Description TNAME CHAR(60) For operators in scan group, full name of base table, truncated on the right if too long for column. If correlation name differs from table name, simple correlation name first and then table name in parentheses. CARDINALITY REAL Estimated number of rows that are returned by the current operator. Cardinality appears as ROWS/REQUEST in some forms of EXPLAIN output.
PAGE 114
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 115
Get Data All: "EXPLAIN OUTPUT(FORMATTED)" " " "LC RC OP OPERATOR "--- --- --- -------------------" " "3 . 4 root "2 . 3 split_top "1 . 2 partition_access ". . 1 file_scan 8 rows fetched from 1 column. OPT -------- DESCRIPTION -------------------- 1.76E+003" 1.76E+003" 1.76E+003" 1.76E+003" 1:64(hash2) fs fr CARD " ---------" EMPLOYEE Example of EXPLAIN Statement Using JDBC import common.*; import java.sql.
PAGE 116
catch (SQLException e) { SQLException nextException; nextException = e; do { System.out.println(nextException.getMessage()); System.out.println("SQLState " + nextException.getSQLState()); System.out.println("Error Code " + nextException.getErrorCode()); } while ((nextException = nextException.
PAGE 117
GET SERVICE Statement The GET SERVICE statement shows the service that is in effect for queries in the current session and whether query plans and SQL text are enabled for the service. Neoview Workload Management Services (WMS) manages queries associated with a service according to the priority and threshold limits of the service. For information about WMS, see the Neoview Workload Management Services Guide.
PAGE 118
GRANT Statement • • • “Syntax Description of GRANT” “Considerations for GRANT” “Examples of GRANT” The GRANT statement grants access privileges for a Neoview SQL table, view, or materialized view to specified roles or through a synonym of a table, view, or materialized view. GRANT { privilege [,privilege]... | ALL [PRIVILEGES] } ON [TABLE] object TO {grantee [,grantee ]... } [WITH GRANT OPTION] grantee is: authid privilege is: DELETE | INSERT | SELECT | UPDATE [(column [,column]...
PAGE 119
WITH GRANT OPTION specifies that roles to whom privileges are granted have the right to grant the same privilege to other roles. Considerations for GRANT Authorization and Availability Requirements To grant a privilege on an object, you must have both that privilege and the right to grant that privilege.
PAGE 120
GRANT EXECUTE Statement • • • “Syntax Description of GRANT EXECUTE” “Considerations for GRANT EXECUTE” “Examples of GRANT EXECUTE” The GRANT EXECUTE statement grants privileges for executing a stored procedure in Java (SPJ) to one or more specified roles. GRANT EXECUTE ON [PROCEDURE] procedure-ref TO {grantee [,grantee ]... } [WITH GRANT OPTION] procedure-ref is: [schema-name.
PAGE 121
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 122
GRANT SCHEMA Statement • • • “Syntax Description of GRANT SCHEMA” “Considerations for GRANT SCHEMA” “Examples of GRANT SCHEMA” The GRANT SCHEMA statement grants access privileges for a schema to specified roles. GRANT {privilege [,privilege]... | all [PRIVILEGES] } ON SCHEMA schema-name TO {PUBLIC | grantee [,grantee ]...
PAGE 123
UPDATE Can use UPDATE statement. ALL PRIVILEGES All of the privileges above. Can have all privileges that apply to the object type. ON SCHEMA schema-name specifies a schema on which to grant privileges. TO {grantee [,grantee]... } specifies one or more roles to whom you grant privileges. grantee specifies an authorization ID to whom you grant privileges. Authorization IDs identify roles during the processing of SQL statements. The authorization ID must be a valid role name, enclosed in double quotes.
PAGE 124
INSERT Statement • • • “Syntax Description of INSERT” “Considerations for INSERT” “Examples of INSERT” The INSERT statement is a DML statement that inserts rows in a table or view. INSERT [NOMVLOG] [WITH NO ROLLBACK] INTO table [(target-col-list)] insert-source target-col-list is: colname [,colname]...
PAGE 125
position is determined by the columns in the table derived from the evaluation of the query expression (query-expr). If you do not specify all of the columns in table in the target-col-list, column default values are inserted into the columns that do not appear in the list. See “Column Default Settings” (page 201). If you do not specify target-col-list, row values from the source table are inserted into all columns in table.
PAGE 126
ASC | DESC specifies the sort order. The default is ASC. For ordering the source table on a column that can contain null, nulls are considered equal to one another but greater than nonnulls. DEFAULT VALUES specifies a query expression of the form VALUES (DEFAULT, ... ). The value of each DEFAULT is the default value defined in the column descriptor of colname, which is contained in the table descriptor of table. Each default value is inserted into its column to form a new row.
PAGE 127
Isolation Levels of Transactions and Access Options of Statements The isolation level of a Neoview SQL transaction defines the degree to which the operations on data within that transaction are affected by operations of concurrent transactions. When you 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.
PAGE 128
In addition to inserting values with specific data types, you might want to insert nulls. To insert null, use the keyword NULL. Examples of INSERT • Insert a row into the CUSTOMER table and supply the value 'A2' for the CREDIT column: INSERT INTO sales.customer VALUES (4777, 'ZYROTECHNIKS', '11211 40TH ST.', 'BURLINGTON', 'MASS.', '01803', 'A2'); --- 1 row(s) inserted. The column name list is not specified for this INSERT statement.
PAGE 129
• The PROJECT table consists of five columns using the data types numeric, varchar, date, timestamp, and interval. Insert values by using these types: INSERT INTO persnl.project VALUES (1000, 'SALT LAKE CITY', DATE '1996-10-02', TIMESTAMP '1996-12-21:08:15:00.00', INTERVAL '30' DAY); --- 1 row(s) inserted. • Suppose that CUSTLIST is a view of all columns of the CUSTOMER table except the credit rating.
PAGE 130
LOCK TABLE Statement • • • “Syntax Description of LOCK TABLE” “Considerations for LOCK TABLE” “Examples of LOCK TABLE” The LOCK TABLE statement locks a table (or the underlying tables of a view) and its indexes, limiting other access to the table and its indexes while your process executes DML statements. See “Database Integrity and Locking” (page 31). LOCK TABLE is a Neoview SQL extension.
PAGE 131
Indexes LOCK TABLE attempts to lock all indexes of any table it locks. If an index is not available or if the lock request times out, LOCK TABLE displays a warning and continues to request locks on other indexes. Examples of LOCK TABLE • Lock a table with an exclusive lock (at a time when few users need access to the database) to perform a series of updates: BEGIN WORK; LOCK TABLE persnl.employee IN EXCLUSIVE MODE; UPDATE persnl.employee SET salary = salary * 1.
PAGE 132
MERGE INTO Statement • • “Syntax Description of MERGE INTO” “Considerations for MERGE INTO” The MERGE INTO statement:. • Updates a table if the row exists and inserts if it does not. This is upsert functionality. • Performs reflexive updates where columns are updated incrementally. • Updates (merges) matching rows from one table to another. • Upserts, reflexive updates, and multi column set updates using rowsets.
PAGE 133
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 or constraints. Merge is not allowed with SET ON ROLLBACK. on-clause must be unique on the clustering key of the table. The clustering key can be a single or multi-column key. This statement is not allowed: MERGE INTO t ON a > 0 ...
PAGE 134
updated. If it is not found, the insert is done. The restrictions are the same as those for “Upsert Using Single Row” (page 132). Example This query extracts derived columns 'a' and 'b' from the USING query as derived table 'z' and use each row to join to the merged table 't' based on the ON clause. For each matched row, column 'b' in table 't' is updated using column 'b' in derived table 'z'. For rows that are not matched, values z.a and z.b are inserted. MERGE INTO t USING a = z.
PAGE 135
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 136
SALARY ---------32000.00 33000.50 40000.00 32000.00 45000.00 --- 5 row(s) selected. SQL> • Prepare a SELECT statement with a named parameter (?param-name) and later run EXECUTE on it: SQL>prepare findsal from +>select salary from persnl.employee +>where jobcode = ?job; --- SQL command prepared. SQL>set param ?job 450 SQL>execute findsal; SALARY ---------32000.00 33000.50 40000.00 32000.00 45000.00 --- 5 row(s) selected. SQL> For more information, see the “EXECUTE Statement” (page 103).
PAGE 137
REVOKE Statement • • • “Syntax Description of REVOKE” “Considerations for REVOKE” “Examples of REVOKE” The REVOKE statement revokes access privileges for a Neoview SQL table, view, or materialized view from specified roles or through a synonym of a table, view, or materialized view. REVOKE [GRANT OPTION FOR] {privilege [,privilege ]...| ALL [PRIVILEGES]} ON [TABLE] object FROM {grantee [,grantee ]...
PAGE 138
grantee is authid authid specifies an authorization ID to whom you revoke privileges. Authorization IDs identify roles during the processing of SQL statements. The authorization ID must be a valid role name, enclosed in double quotes. authid is not case-sensitive. SQL:1999 specifies two special authorization IDs: PUBLIC and SYSTEM. • • PUBLIC specifies all present and future authorization IDs. SYSTEM specifies the implicit grantor of privileges to the creators of objects.
PAGE 139
REVOKE EXECUTE Statement • • • “Syntax Description of REVOKE EXECUTE” “Considerations for REVOKE EXECUTE” “Examples of REVOKE EXECUTE” The REVOKE EXECUTE statement removes privileges for executing a stored procedure in Java (SPJ) from one or more specified roles. REVOKE [GRANT OPTION FOR] EXECUTE ON [PROCEDURE] procedure-ref FROM {grantee [,grantee ]... } [RESTRICT | CASCADE] procedure-ref is: [schema-name.
PAGE 140
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 141
REVOKE SCHEMA Statement • • • “Syntax Description of REVOKE SCHEMA” “Considerations for REVOKE SCHEMA” “Examples of REVOKE SCHEMA” The REVOKE SCHEMA statement revokes access privileges for a Neoview SQL schema from specified roles. See also“ROLLBACK WORK Statement” (page 143). REVOKE [GRANT OPTION FOR] {privilege [,privilege ] ...| all [PRIVILEGES ]} ON SCHEMA schema-name FROM {PUBLIC | grantee [,grantee]...
PAGE 142
DELETE Revokes the DELETE statement. EXECUTE Revokes the EXECUTE privilege on a schema. INSERT Revokes the INSERT statement. UPDATE Revokes the UPDATE statement. REFERENCES Can no longer create constraints that reference the schema. SELECT Revokes the SELECT statement. ALL PRIVILEGES Revokes all privileges that apply to the schema. ON SCHEMA schema-name specifies a schema on which to revoke privileges. FROM {authid [,authid]...
PAGE 143
ROLLBACK WORK Statement • • “Considerations for ROLLBACK WORK” “Examples of ROLLBACK WORK” The ROLLBACK WORK statement undoes all database modifications to objects made during the current transaction, releases all locks on objects held by the transaction, and ends the transaction. See “Transaction Management” (page 32). ROLLBACK [WORK] WORK is an optional keyword that has no effect. ROLLBACK WORK has no effect if there is no active transaction.
PAGE 144
SELECT Statement • • • • • • • • 144 “Syntax Description of SELECT” “Considerations for SELECT” “Considerations for Select List” “Considerations for SEQUENCE BY” “Considerations for GROUP BY” “Considerations for ORDER BY” “Considerations for UNION” “Examples of SELECT” SQL Statements
PAGE 145
The SELECT statement is a DML statement that retrieves values from tables, views, derived tables determined by the evaluation of query expressions, or joined tables. size-and-index is: | SELECT [col-expr] [[ANY N] | [FIRST N]] [ALL | DISTINCT] select-list FROM table-ref [,table-ref]... [WHERE search-condition] [SAMPLE sampling-method] [TRANSPOSE transpose-set [transpose-set]... [KEY BY key-colname]]... [SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] | DESC[ENDING]]]...
PAGE 146
select-list is: * | select-sublist [,select-sublist]... select-sublist is: corr.*) | [corr.
PAGE 147
THEN percent-result PERCENT [ROWS] [WHEN condition THEN percent-result PERCENT [ROWS]]... [ELSE percent-result PERCENT [ROWS]] END rows-size is: number-rows ROWS | BALANCE WHEN condition THEN number-rows ROWS [WHEN condition THEN number-rows ROWS]... [ELSE number-rows ROWS] END transpose-set is: transpose-item-list AS transpose-col-list transpose-item-list is: expression-list | (expression-list) [,(expression-list)]... expression-list is: expression [,expression]...
PAGE 148
FROM table-ref [,table-ref]... specifies a list of tables, views, derived tables, or joined tables that determine the contents of an intermediate result table from which Neoview SQL returns the columns you specify in select-list. To refer to a table or view, use the ANSI logical name. See “Database Object Names” (page 206). If you specify only one table-ref, the intermediate result table consists of rows derived from that table reference.
PAGE 149
[OLD.]* specifies the row from the old table exposed by the embedded delete. The old table refers to column values before the delete operation. NEW is not allowed. An implicit OLD.* return list is assumed for a delete operation that does not specify a return list. col-expr [[AS] name] specifies a derived column determined by the evaluation of an SQL value expression in the list. Any column referred to in a value expression is from the row in the old table exposed by the delete.
PAGE 150
table-ref [NATURAL] [join-type] JOIN table-ref [join-spec] join-type is: CROSS |INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] is a joined table. You specify the join-type by using the CROSS, INNER, OUTER, LEFT, RIGHT, and FULL keywords. If you omit the optional OUTER keyword and use LEFT, RIGHT, or FULL in a join, Neoview SQL assumes the join is an outer join. If you specify a CROSS join as the join-type, you cannot specify a NATURAL join or a join-spec.
PAGE 151
[GROUP BY [col-expr]{colname | colnum} [,{colname | colnum}]...] [HAVING search-condition] [[FOR] access-option ACCESS] [IN {SHARE | EXCLUSIVE} MODE] A simple-table can be a table value constructor. It starts with the VALUES keyword followed by a sequence of row value constructors, each of which is enclosed in parentheses. A row-value-const is a list of expressions (or NULL) or a row subquery (a subquery that returns a single row of column values).
PAGE 152
transpose-item-list AS transpose-col-list specifies a transpose-set. You can use multiple transpose sets within a TRANSPOSE clause. The TRANSPOSE clause generates, for each row of the source table derived from the table reference or references in the FROM clause, a row for each item in each transpose-item-list of all the transpose sets.
PAGE 153
HAVING search-condition specifies a search-condition to apply to each group of the grouped table resulting from the preceding GROUP BY clause in the SELECT statement. The GROUP BY clause, if one exists, must precede the HAVING clause in the SELECT statement. To comply with ANSI standards, Neoview SQL does not move aggregate predicates from the WHERE clause to a HAVING clause and does not move non-aggregate predicates from the HAVING clause to the WHERE clause.
PAGE 154
ORDER BY {colname | colnum} [ASC[ENDING] | DESC[ENDING]] [,{colname | colnum} [ASC[ENDING] | DESC[ENDING]]]... specifies the order in which to sort the rows of the final result table. colname names a column in select-list or a column in a table reference in the FROM clause of the SELECT statement. colname is optionally qualified by a table, view, or correlation name; for example, CUSTOMER.CITY. If a column has been aliased to another name you must use the alias name.
PAGE 155
For example, suppose that the view named AVESAL includes column A defined as AVG (X). The SELECT statement that contains MAX (A) in its select list is invalid because the select list actually contains MAX (AVG (X)), and an aggregate function cannot have an argument that includes another aggregate function. Join Limits NOTE: HP recommends that you limit the number of tables in a join to a maximum of 64, which includes base tables of views referenced in joins.
PAGE 156
Limitations of DISTINCT Aggregates • • There is no limit to the number of distinct aggregates. Distinct STDDEV and distinct VARIANCE are not supported with multiple distinct aggregates. For example, this statement will result in an error.
PAGE 157
• • • • You can specify GROUP BY using ordinals to refer to the relative position within the SELECT list. For example, GROUP BY 3, 2, 1. If you do not include a GROUP BY clause but you specify an aggregate function in the select-list, all rows of the result table form the one and only group. The result of AVG, for example, is a single value for the entire table. The GROUP BY clause must precede a HAVING clause. The RANDOM function cannot be part of the GROUP BY clause of a sampling method.
PAGE 158
(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 159
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 160
600 900 900 . . . 1500 2500 1000 JIMMY MIRIAM SUE SCHNEIDER KING CRAMER 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.00 25100.00 --- 2 row(s) selected.
PAGE 161
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 162
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 163
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 164
(SELECT O.ordernum FROM sales.orders O WHERE custnum IN (SELECT custnum FROM sales.customer WHERE state = 'CALIFORNIA')) GROUP BY OD.ordernum; ORDERNUM (EXPR) ---------- --------------------200490 1030.00 300350 71025.00 300380 28560.00 --- 3 row(s) selected. The price for the total quantity ordered is computed for each order number. • Show employees, their salaries, and the percentage of the total payroll that their salaries represent.
PAGE 165
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 166
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 257). default-schema-name is an SQL identifier. For example, you can use MYSCHEMA or myschema or a delimited identifier "my schema".
PAGE 167
SET SERVICE Statement The SET SERVICE statement sets the service for subsequent queries in the current session. Neoview Workload Management Services (WMS) manages queries associated with a service according to the priority and threshold limits of the service. For information about WMS, see the Neoview Workload Management Services Guide. Syntax SET SERVICE service-name service-name represents a user-defined service for managing queries and can be a maximum of 24 characters.
PAGE 168
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 169
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 170
SET TRANSACTION Statement • • • “Syntax Description of SET TRANSACTION” “Considerations for SET TRANSACTION” “Example of SET TRANSACTION” The SET TRANSACTION statement sets attributes for transactions. It stays in effect until the end of the session or until the next SET TRANSACTION statement, whichever comes first. Therefore, the SET TRANSACTION statement can set the attributes of all subsequent transactions in the session.
PAGE 171
Syntax Description of SET TRANSACTION transaction mode specifies the attributes that you can set. You cannot specify any of the options—isolation level, access mode, size of the diagnostics area, or autocommit—more than once within one SET TRANSACTION statement. You cannot use the AUTOCOMMIT option with any other option. isolation-level specifies the level of data consistency defined for the transaction and the degree of concurrency the transaction has with other transactions that use the same data.
PAGE 172
RESET Reverts to the segment-wide autoabort interval configured. autobegin-option specifies whether transactions should occur automatically. The default setting is ON. If this option is set to OFF and Neoview SQL needs a transaction and one is not already running, an error is returned. The AUTOBEGIN option in a SET TRANSACTION can only be specified on one SET TRANSACTION command. This means that you can only issue the SET TRANSACTION AUTOBEGIN OFF statement by itself.
PAGE 173
SET TRANSACTION MULTI COMMIT ON; DELETE FROM persnl.employee WHERE empnum = 23; --- 1 row(s) deleted. SET TRANSACTION MULTI COMMIT OFF; Restrictions • This are restrictions for the MULTI COMMIT option: Can be specified in the SET TRANSACTION statement along with other transaction-mode. For example, SET TRANSACTION ISOLATION LEVEL READ COMMITTED, MULTI COMMIT ON, READ WRITE; • • • • Not compatible with the READ UNCOMMITTED access option. Not compatible with READ ONLY access mode.
PAGE 174
Degree of Concurrency The SET TRANSACTION statement affects the degree of concurrency available to the transaction . Concurrent processes take place within the same interval of time and share resources.
PAGE 175
SHOWDDL Statement • • • • • “Syntax Description of SHOWDDL” (page 175) “Considerations for SHOWDDL” (page 175) “Differences Between SHOWDDL Output and Original DDL” (page 175) “PRIVILEGES Option” (page 176) “Examples of SHOWDDL” (page 176) The SHOWDDL describes details about objects in its display. SHOWDDL {[PROCEDURE] procedure-name }| [TABLE] object-name} [, PRIVILEGES] procedure-name is: [schema-name.]procedure-name object-name is: [schema-name.
PAGE 176
• • SHOWDDL always generates a Java signature for the SPJ. Privileges acquired by the object or procedure from the schema are displayed and preceded by the comments “— Schema level privileges”. The corresponding GRANT statements are also commented out. Schema level privileges only need to be applied at the schema level. PRIVILEGES Option The PRIVILEGES option includes the GRANT statements as they apply to the option.
PAGE 177
-- create a procedure create procedure T100_io_nn(IN IN1 numeric(9,3), OUT OUT2 numeric(9,3)) external name 't100.T100_io_nn' language java parameter style java no sql external path ‘/usr/roberta/regress/catman’ grant execute on procedure t100_io_nn to “role_user1”; -- showddl SHOWDDL SCH.T100_IO_NN, PRIVILEGES -- showddl output CREATE PROCEDURE SCH.T100_IO_NN ( IN IN1 NUMERIC(9,3) , OUT OUT2 NUMERIC(9,3) ) EXTERNAL NAME 't100.T100_io_nn (java.math.BigDecimal,java.math.
PAGE 178
, , , , , EMPL_ADDRESS VARCHAR(50) CHARACTER SET ISO88591 NO DEFAULT -- NOT NULL DEPT_NO INT NO DEFAULT -- NOT NULL SALARY NUMERIC(10, 2) DEFAULT 1000.00 CONSTRAINT SCH.EMPL_836528881_1454 PRIMARY KEY (EMPL_NO ASC) CONSTRAINT SCH.EMPL_314277881_1454 CHECK (SCH.EMPL.EMPL_NO IS NOT NULL AND SCH.EMPL.EMPL_NAME IS NOT NULL AND SCH.EMPL.EMPL_ADDRESS IS NOT NULL AND SCH.EMPL.DEPT_NO IS NOT NULL) ) HASH PARTITION BY (EMPL_NO) MAX TABLE SIZE 2000 ; CREATE INDEX EMPL_DEPT_NO ON SCH.
PAGE 179
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 180
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 144). [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 181
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 258). 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 182
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 170).
PAGE 183
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 184
(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 185
UPDATE STATISTICS Statement • • • “Syntax Description of UPDATE STATISTICS” “Considerations for UPDATE STATISTICS” “Examples of UPDATE STATISTICS” The UPDATE STATISTICS statement updates the histogram statistics for one or more groups of columns within a table. These statistics are used to devise optimized access plans. In addition to histogram statistics, UPDATE STATISTICS generates physical statistics (index level, nonempty block count, and EOF) for Neoview SQL tables.
PAGE 186
stored in histogram tables. If you omit it, physical statistics are generated for Neoview SQL tables, and SQL returns a warning message. See “Using Statistics” (page 188). column-list | EVERY COLUMN [, column-list] | EVERY KEY [, column-list] | EXISTING COLUMN[S] [, column-list], NECESSARY COLUMN[S] [, column-list] specifies the ways in which column-group-list can be defined. The column list represents both a single-column group and a multi-column group.
PAGE 187
NECESSARY COLUMN[S] The NECESSARY COLUMN[S] keyword causes UPDATE STATISTICS to determine what existing histograms, if any, need to be updated, then updates them. If no histograms are found to need regeneration, UPDATE STATISTICS issues a warning. The NECESSARY COLUMN[S] keyword ignores the SAMPLE clause and internally determines the sample percent to use. histogram-option GENERATE n INTERVALS The GENERATE n INTERVALS option for UPDATE STATISTICS now accepts values between 1 and 10,000.
PAGE 188
defined by the number of rows specified for period. The value period must be an integer that is greater than zero (period > 0). SET ROWCOUNT c is an optional clause that specifies the number of rows in the table. The value c must be an integer that is greater than or equal to zero (c > 0). If the ROWCOUNT clause is not specified, Neoview SQL can, in most cases, determine the actual table row count. See “SAMPLE Clause” (page 270).
PAGE 189
ON CITY, STATE TO ZIP ON (CITY), (STATE) TO (ZIP) ON CITY TO STATE, ZIP ON (CITY) TO (STATE), (ZIP) The TO specification is useful when a table has many columns, and you want histograms on a subset of columns. Do not confuse (CITY) TO (ZIP) with (CITY, STATE, ZIP), which refers to a multi-column histogram. You can clear statistics in any combination of columns you specify, not necessarily with the column-group-list you used to create statistics.
PAGE 190
UPDATE STATISTICS FOR TABLE demolition_sites ON (zip, type); • This example removes all histograms for table DEMOLITION_SITES: UPDATE STATISTICS FOR TABLE demolition_sites CLEAR; • This example selectively removes histograms for column STREET in table ADDRESS: UPDATE STATISTICS FOR TABLE address ON street CLEAR; 190 SQL Statements
PAGE 191
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, and reorganizing and reloading data in a table or index. This section describes the Neoview SQL utilities: “MAINTAIN Command” (page 192) Performs one or more maintenance tasks, such as REORG, UPDATE STATISTICS, and REFRESH, on a specified database object. “POPULATE INDEX Utility” (page 197) Loads indexes.
PAGE 192
MAINTAIN Command • • “Syntax Description of MAINTAIN” “Examples of MAINTAIN” 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: • • • • • • For users to specify one or more maintenance tasks for a specific object. For specifying a specific maintenance task for a particular object. To synchronously return status information as each task is executed.
PAGE 193
There are three ways that tasks could be specified. the group of tasks to be executed depend on the object-type. If ALL is specified, all tasks relevant to that object-type are performed. The detailed tasks for each object-type are: • • • • • TABLE — The object-name specified is the table that needs to be maintained.
PAGE 194
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. 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 STATUS displays detailed status message from each of the individual tasks. The default is OFF. DISPLAY displays the tasks that are scheduled to be executed without actually executing them.
PAGE 195
RECOMPUTE when used, the MVs in the MVGROUP are cleared and repopulated from scratch. Contrast this with incremental refresh when the RECOMPUTE option is not used. In this case, only changes since the last refresh are applied. CANCEL helps recover from failure situations. In certain cases, reissuing the MAINTAIN operation may not work. For example, when the system has a disk space issue and the MAINTAIN operation keeps failing due to insufficient disk space.
PAGE 196
maintain table t1, reorg ', rate 100'; Task: REORG_TABLE STATUS: Started Object: CAT.SCH.T1 Task: REORG_TABLE STATUS: Ended ET: 00:00:00.031 --SQL operation complete. maintain table t1, reorg ', rate 100', display detail; Task: REORG_TABLE STATUS: scheduled Command: REORG.TABLE.CAT.SCH.
PAGE 197
POPULATE INDEX Utility • • • “Syntax Description of POPULATE INDEX” “Considerations for POPULATE INDEX” “Examples of POPULATE INDEX” POPULATE INDEX is a syntax-based utility that can be executed in the Neoview Command Interface. The POPULATE INDEX utility loads Neoview SQL indexes. POPULATE INDEX index ON table [index-option] index-option is ONLINE | OFFLINE Syntax Description of POPULATE INDEX index is an SQL identifier that specifies the simple name for the index.
PAGE 198
PURGEDATA Utility • • • “Syntax Description of PURGEDATA” “Considerations for PURGEDATA” “Examples of PURGEDATA” The PURGEDATA utility deletes all data from a Neoview SQL table and its related indexes. PURGEDATA table [IGNORE_TRIGGER] [NOLOG] Syntax Description of PURGEDATA table is the name of the table from which to purge the data. See “Database Object Names” (page 206). Considerations for PURGEDATA • • • • • • • You must have ALL privileges on the table.
PAGE 199
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 200
Character Sets For detailed information about how the encoding of character set data, including multibyte characters, is handled on the client and server sides of the Neoview platform, see the Neoview Character Sets Administrator's Guide.
PAGE 201
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 202
SELECT AVG (salary) AS "AVERAGE SALARY" FROM persnl.employee; "AVERAGE SALARY" ---------------49441.52 --- 1 row(s) selected.
PAGE 203
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 204
Correlation Names A correlation name is a name you can associate with a table reference that is a table, 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 205
Database Objects A database object is an SQL entity that exists in a namespace, maps to a file in most cases, and is registered in the system catalog. DML statements can access Neoview SQL objects. The subsections listed below describe these Neoview SQL objects. “Constraints” “Indexes” “Tables” “Views” Ownership In Neoview SQL, the creator of a schema owns all the objects defined in the schema.
PAGE 206
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 205). Logical Names for SQL Objects You must refer to an SQL table or view by using a two-part logical name, also called an ANSI name: schema-name.
PAGE 207
Data Types Neoview SQL data types are character, datetime, interval, or numeric (exact or approximate): “Character String Data Types” (page 210) Fixed-length and variable-length character data types. “Examples of SET TABLE TIMEOUT” (page 169) DATE, TIME, and TIMESTAMP data types. “Interval Data Types” (page 214) Year-month intervals (years and months) and day-time intervals (days, hours, minutes, seconds, and fractions of a second).
PAGE 208
Type SQL Designation Description Size or Range (1) 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.7976931348623157e+308; stored in 8 byte Decimal number DECIMAL (1,scale) to DECIMAL (18,scale) Decimal number with optional scale; stored as ASCII characters; signed or unsigned for 1 to 9 digits; signed required for 10 or more digits 1 to 18 digits.
PAGE 209
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 210
TIMESTAMP '1997-09-28 00:00:00' > TIMESTAMP '1997-06-26 00:00:00' Interval Data Types Values of type INTERVAL are mutually comparable and mutually assignable only if the types are either both year-month intervals or both day-time intervals. For example, this predicate is true: INTERVAL '02-01' YEAR TO MONTH > INTERVAL '00-01' YEAR TO MONTH The field components of the INTERVAL do not have to be the same.
PAGE 211
PIC[TURE] X[(length)] [DISPLAY] [char-set] [collate-clause] [UPSHIFT] specifies a column with fixed-length character data. You can specify the number of characters in a PIC X column by specify either length or multiple Xs, with each X representing one character position. DISPLAY does not change the meaning of the clause. PIC is a Neoview SQL extension. CHAR[ACTER] VARYING (length) [CHARACTERS] [char-set] specifies a column with varying-length character data.
PAGE 212
In Neoview SQL, the NCHAR type specification is equivalent to: • • • NATIONAL CHARACTER NATIONAL CHAR CHAR ... CHARACTER SET ..., where the character set is the default character set for NCHAR Similarly, you can use NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, and VARCHAR ... CHARACTER SET ... . 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).
PAGE 213
ss Second, from 00 to 59 msssss Microsecond, from 000000 to 999999 When you specify datetime_value (FORMAT ‘string’) and the specified format is ‘mm/dd/yyyy’,’MM/DD/YYYY’, or ‘yyyy/mm/dd’ or ‘yyyy-mm-dd’, the datetime type is automatically cast. If the TITLE clause is present, it is ignored.
PAGE 214
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 215
Within the definition of an interval range (other than a single field), the start-field and end-field can be any of the specified fields with these restrictions: • • An interval range is either year-month or day-time—that is, if the start-field is YEAR, the end-field is MONTH; if the start-field is DAY, HOUR, or MINUTE, the end-field is also a time field. The start-field must precede the end-field within the hierarchy: YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
PAGE 216
SMALLINT [SIGNED|UNSIGNED] specifies an exact numeric column—a two-byte binary integer, SIGNED or UNSIGNED. The column stores integers in the range unsigned 0 to 65535 or signed -32768 to +32767. The default is SIGNED. INT[EGER] [SIGNED|UNSIGNED] specifies an exact numeric column—a four-byte binary integer, SIGNED or UNSIGNED. The column stores integers in the range unsigned 0 to 4294967295 or signed -2147483648 to +2147483647. The default is SIGNED.
PAGE 217
An IEEE DOUBLE PRECISION data type is stored in 8 bytes with 52 bits of binary precision and 11 bits of exponent. The minimum and maximum range is from +/2.2250738585072014e-308 through +/-1.7976931348623157e+308.
PAGE 218
Expressions An SQL value expression, referred to as an expression, can evaluate to a value with one of these : “Character Value Expressions” (page 218) Operands can be combined with the concatenation operator (||). Example: 'HOUSTON,' ||' TEXAS' “Datetime Value Expressions” (page 220) Operands can be combined in specific ways with arithmetic operators.
PAGE 219
'ABILENE ' ||' TEXAS' The concatenation of two string literals. 'ABILENE ' ||' TEXAS' || x’55 53 41 The concatenation of three string literals to form the literal: 'ABILENE TEXAS USA' 'Customer ' || custname The concatenation of a string literal with the value in column CUSTNAME. CAST (order_date AS CHAR) CAST function applied to a DATE value.
PAGE 220
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 221
Considerations for Datetime Value Expressions Data Type of Result In general, the data type of the result is the data type of the datetime-primary part of the datetime expression. For example, datetime value expressions include: CURRENT_DATE + INTERVAL '1' DAY The sum of the current date and an interval value of one day. CURRENT_DATE + est_complete The sum of the current date and the interval value in column EST_COMPLETE.
PAGE 222
The next examples use these values in the PROJECT table: PROJCODE START_DATE SHIP_TIMESTAMP EST_COMPLETE 1000 1996-04-10 1996-04-21 08:15:00.00 15 945 1996-10-20 1996-12-21 08:15:00.00 30 920 1996-02-21 1996-03-12 09:45:00.00 20 134 1996-11-20 1997-01-01 00:00:00.00 30 • Add an interval value qualified by YEAR to a datetime value: SELECT start_date + INTERVAL '1' YEAR FROM persnl.project WHERE projcode = 1000; (EXPR) ---------1997-04-10 --- 1 row(s) selected.
PAGE 223
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 224
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. If the start-field is YEAR, the end-field is MONTH; if the start-field is DAY, HOUR, or MINUTE, the end-field is also a time field. The start-field must precede the end-field within the hierarchy YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
PAGE 225
Operands Result type Timestamp + Numeric or Numeric + Timestamp Timestamp Timestamp - Numeric Timestamp Timestamp – Interval Timestamp year-month Interval + year-month Interval year-month Interval day-time Interval + day-time Interval day-time Interval year-month Interval – year-month Interval year-month Interval day-time Interval – day-time Interval day-time Interval Time – Time Interval Timestamp – Timestamp Interval Interval * Number or Number * Interval Interval Interval / Number
PAGE 226
4000 1996-09-21 1996-10-21:10:15:00.0000 30 5000 1996-09-28 1996-10-28:09:25:01.1111 30 • Suppose that the CURRENT_TIMESTAMP is 2000-01-06 11:14:41.748703. Find the number of days, hours, minutes, seconds, and fractional seconds in the difference of the current timestamp and the SHIP_TIMESTAMP in the PROJECT table: SELECT projcode, (CURRENT_TIMESTAMP - ship_timestamp) DAY(4) TO SECOND(6) FROM samdbcat.persnl.
PAGE 227
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 228
Numeric expressions are evaluated according to these additional rules: • • • • An expression with a numeric operator evaluates to null if any of the operands is null. Dividing by 0 causes an error. Exponentiation is allowed only with numeric data types. If the first operand is 0 (zero), the second operand must be greater than 0, and the result is 0. If the second operand is 0, the first operand cannot be 0, and the result is 1.
PAGE 229
Examples of Numeric Value Expressions These are examples of numeric value expressions: -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 230
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 231
Indexes An index is an ordered set of pointers to rows of a table. Each index is based on the values in one or more columns. There is always a one-to-one correspondence between index rows and base table rows. SQL Indexes Each row in a Neoview SQL index contains: • • The columns specified in the CREATE INDEX statement The clustering key of the underlying table (the user-defined clustering key) An index name is an SQL identifier.
PAGE 232
Keys Neoview SQL supports these types of keys: • • • “Clustering Keys” “Index Keys” “Primary Keys” Clustering Keys Neoview SQL organizes records of a table or index by using a b-tree based on the “clustering key”. Values of the clustering key act as logical row-ids. The primary key is the clustering key and it must be unique. Index Keys There is always a one-to-one correspondence between index rows and base table rows.
PAGE 233
Literals A literal is a constant you can use in an expression, in a statement, or as a parameter value. Literals are stored in columns of tables according to how you specify the column definitions in a CREATE TABLE statement. An SQL literal can be one of these data types: “Character String Literals” (page 233) A series of characters enclosed in single quotes. Example: 'Planning' “Datetime Literals” (page 235) Begins with keyword DATE, TIME, or TIMESTAMP and followed by a character string.
PAGE 234
'hex-code-value' represents the code value of a character in hexadecimal form enclosed in single quotes. It must contain an even number of hexadecimal digits. For UCS2, each hex-code-value must be of four hexadecimal digits long. For ISO88591, each value must be two digits long. If hex-code-value is improperly formatted (for example, it contains an invalid hexadecimal digit or an odd number of hexadecimal digits), an error is returned.
PAGE 235
Datetime Literals • “Examples of Datetime Literals” A datetime literal is a DATE, TIME, or TIMESTAMP constant you can use in an expression, in a statement, or as a parameter value. Datetime literals have the same range of valid values as 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.
PAGE 236
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 237
'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 238
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 239
Examples of Numeric Literals • These are all numeric literals, along with their display format: Literal Display Format 477 477 580.45 580.45 +005 5 -.3175 -.3175 1300000000 1300000000 99. 99 -0.123456789012345678 -.123456789012345678 99E-2 9.9000000E-001 12.3e+5 1.
PAGE 240
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 241
Null and Expression Evaluation Comparison Expression Type Condition Result Boolean operators (AND, OR, NOT) Either operand is null. For AND, the result is null. For OR, the result is true if the other operand is true, or null if the other operand is null or false. For NOT, the result is null. Arithmetic operators Either or both operands are null. The result is null. NULL predicate The operand is null. The result is true. Aggregate (or set) functions (except COUNT) Some rows have null columns.
PAGE 242
SQL Tables For more information, see “CREATE TABLE Statement” (page 69), “CREATE INDEX Statement” (page 58), and “ALTER VIEW Statement” (page 51). Automatically Creating Partitions When creating a table users can specify that the table is not partitioned using the NO PARTITION clause. The default is for the table to be partitioned. Users may also specify the MAX TABLE SIZE clause that will be used to decide on the table extent sizes. If this option is not specified Neoview SQL will decide.
PAGE 243
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 243) Determines whether a sequence of values is within a range of sequences of values.
PAGE 244
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 245
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 246
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 247
(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 248
... --- 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 249
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 250
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 251
------186 -----------------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.partsupp); • Find those employees in EMPLOYEE whose last name and job code match the list of last names and job codes: SELECT empnum, last_name, first_name FROM persnl.
PAGE 252
Note that the rvc IS NOT NULL predicate is not equivalent to NOT rvc IS NULL.
PAGE 253
table-subquery provides the values for the comparison. The number of values returned by the row-value-constructor must be equal to the number of values specified by the table-subquery, and the data types of values returned by the row-value-constructor must be comparable to the data types of values returned by the table-subquery. See “Subquery” (page 260). Considerations for ALL, ANY, SOME Let R be the result of the row-value-constructor, T the result of the table-subquery, and RT a row in T.
PAGE 254
EMPNUM -----1 23 29 ... 343 557 568 FIRST_NAME --------------ROGER JERRY JANE LAST_NAME -------------------GREEN HOWARD RAYMOND ALAN BEN JESSICA TERRY HENDERSON CRINER SALARY ----------175500.00 137000.10 136000.00 39500.00 65000.00 39500.00 --- 23 row(s) selected. • This predicate is true if the part number is equal to any part number with more than five units in stock: partnum = ANY (SELECT partnum FROM sales.
PAGE 255
Privileges These privileges are used by the GRANT and REVOKE statements. For further information on GRANT, see “GRANT Statement” (page 118), “GRANT EXECUTE Statement” (page 120), or “GRANT SCHEMA Statement” (page 122). For further information on REVOKE, see “REVOKE Statement” (page 137), “REVOKE EXECUTE Statement” (page 139), or “REVOKE SCHEMA Statement” (page 141).
PAGE 256
• • • • • • • • ALTER - Can modify the definition of the objects in the schema. ALTER_TABLE - Can add and drop constraints; add, populate, and drop indexes; add, alter, and drop triggers; modify file attributes; add columns, and rename tables for any table in the schema. CREATE - Can create objects in the schema and become the object's owner. CREATE_TABLE - Can create tables in the schema and be the owner of the table. CREATE_VIEW — Can create regular views in the schema and become the view owner.
PAGE 257
Schemas The ANSI SQL:1999 schema name is an SQL identifier that is unique for a given ANSI catalog name. Neoview SQL automatically qualifies a schema name with the current default catalog name, which is always Neo. The logical name of the form schema.object is an ANSI name. The part schema denotes the ANSI-defined schema. To be compliant with ANSI SQL:1999, Neoview SQL provides support for ANSI object names. By using these names, you can develop ANSI-compliant applications that access all SQL objects.
PAGE 258
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 259
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 '1998-11-01' AND O.ordernum = OD.ordernum; ORDERNUM DELIV_DATE QTY_ORDERED ---------- ---------- ----------100210 1997-04-10 3 100210 1997-04-10 3 100210 1997-04-10 6 100250 1997-06-15 4 101220 1997-12-15 3 ... --- 28 row(s) selected.
PAGE 260
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 144). 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 261
Correlated Subqueries When Providing Comparison Values In the search condition of a subquery, when you refer to columns of any table or view defined in an outer query, the reference is called an outer reference. A subquery containing an outer reference is called a correlated subquery. If you refer to a column name that occurs in more than one outer query, you must qualify the column name with the correlation name of the table or view to which it belongs.
PAGE 262
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 263
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 “Considerations for CREATE TRIGGER” (page 83). See also “CREATE TRIGGER Statement” (page 82), “ALTER TRIGGER Statement” (page 50), “DROP TRIGGER Statement” (page 101).
PAGE 264
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 265
Although MVGROUPs are automatically created, if you decide to group together materialized views that share the same refresh frequency rate, you will need to manually create them.
PAGE 266
PAGE 267
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. “SAMPLE Clause”. Specifies the sampling method used to select a subset of the intermediate result table of a SELECT statement. “SEQUENCE BY Clause”.
PAGE 268
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 269
Examples of DEFAULT • This example uses DEFAULT clauses on CREATE TABLE to specify default column values : CREATE TABLE items ( item_id CHAR(12) ,description CHAR(50) ,num_on_hand INTEGER • NO DEFAULT DEFAULT NULL DEFAULT 0 NOT NULL; This example uses DEFAULT clauses on CREATE TABLE to specify default column values: CREATE TABLE persnl.
PAGE 270
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 144). SAMPLE is a Neoview SQL extension.
PAGE 271
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 271). BALANCE If you specify a BALANCE expression, Neoview SQL performs stratified sampling.
PAGE 272
stored. The number of blocks in a cluster is specified in the CLUSTERS subclause of the SAMPLE RANDOM clause. For example: SELECT * FROM customers SAMPLE RANDOM 1 PERCENT CLUSTERS OF 2 BLOCKS; This query randomly selects one percent of the clusters in the CUSTOMERS table and then adds each row in all selected clusters to the result table. In other words, think of the CUSTOMERS table as a sequence of disk blocks, where each two blocks in the sequence is a cluster.
PAGE 273
--- 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 274
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. Therefore, it is possible that no rows are returned, and the result is null. • This query illustrates sampling after execution of the WHERE clause has chosen the qualifying rows.
PAGE 275
--- 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 276
• 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 277
--- 30 row(s) selected.
PAGE 278
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 144). SEQUENCE BY is a Neoview SQL extension. SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] | DESC[ENDING]]]...
PAGE 279
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 280
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 281
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 282
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. colname is an SQL identifier that specifies a column name. It identifies the column consisting of the values in expression-list. For example, in the transpose set TRANSPOSE A,B,C AS V, the column V corresponds to the values of the expressions A,B, and C. (colname-list) specifies a list of column names enclosed in parentheses.
PAGE 283
Cardinality of the TRANSPOSE Result The items in each transpose-item-list are enumerated from 1 to N, where N is the total number of items in all the item lists in the transpose sets. In this example with a single transpose set, the value of N is 3: TRANSPOSE (A,X),(B,Y),(C,Z) AS (V1,V2) In this example with two transpose sets, the value of N is 5: TRANSPOSE (A,X),(B,Y),(C,Z) AS (V1,V2) L,M AS V3 The values 1 to N are the key values ki. The items in each transpose-item-list are the expression values vi.
PAGE 284
The result table of the TRANSPOSE query is: • A B C D E F KEYCOL VALCOL 1 10 100 d1 e1 f1 1 1 1 10 100 d1 e1 f1 2 10 1 10 100 d1 e1 f1 3 100 2 20 200 d2 e2 f2 1 2 2 20 200 d2 e2 f2 2 20 2 20 200 d2 e2 f2 3 200 This query shows that the items in the transpose item list can be any valid scalar expressions: SELECT KEYCOL, VALCOL, A, B, C FROM mytable TRANSPOSE A + B, C + 3, 6 AS VALCOL KEY BY KEYCOL; The result table of the TRANSPOSE query is: • KEYC
PAGE 285
(EXPR) -------------------2 2 2 --- 3 row(s) selected. • This query shows how multiple TRANSPOSE clauses can be used in the same query.
PAGE 286
The result table of the TRANSPOSE query is: KEYCOL VALCOL1 VALCOL2 1 1 ? 2 10 ? 3 100 ? 4 ? d1 5 ? e1 6 ? f1 1 2 ? 2 20 ? 3 200 ? 4 ? d2 5 ? e2 6 ? f2 A question mark (?) in a value column indicates no value for the given KEYCOL.
PAGE 287
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 abc 2 20 200 d2 e2 f2 2 20 xyz TRANSPOSE Clause 287
PAGE 288
PAGE 289
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 290
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. These functions use a character value expression as an argument or return a result of character data type. For more information on character string functions, see the Neoview Character Sets Administrator's Guide.
PAGE 291
“TRANSLATE Function” (page 428) Translates a character string from a source character set to a target character set. “TRIM Function” (page 429) Removes leading or trailing characters from a character string. “UCASE Function” (page 430) Upshifts single-byte characters. You can also use UPSHIFT or UPPER. “UPPER Function” (page 438) Upshifts single-byte characters. You can also use UPSHIFT or UCASE. “UPSHIFT Function” (page 439) Upshifts single-byte characters. You can also use UPPER or UCASE.
PAGE 292
“DAYOFWEEK Function” (page 339) Returns an integer value in the range 1 through 7 that represents the corresponding day of the week. “DAYOFYEAR Function” (page 340) Returns an integer value in the range 1 through 366 that represents the corresponding day of the year. “EXTRACT Function” (page 353) Returns a specified datetime field from a datetime value expression or an interval value expression.
PAGE 293
“LOG Function” (page 363) Returns the natural logarithm of a numeric value expression. “LOG10 Function” (page 364) Returns the base 10 logarithm of a numeric value expression. “MOD Function” (page 375) Returns the remainder (modulus) of an integer value expression divided by an integer value expression. “NULLIFZERO Function” (page 389) Returns the value of the operand unless it is zero, in which case it returns NULL.
PAGE 294
“MOVINGAVG Function” (page 378) Returns the average of nonnull values of a column expression in the current window. “MOVINGCOUNT Function” (page 379) Returns the number of nonnull values of a column expression in the current window. “MOVINGMAX Function” (page 381) Returns the maximum of nonnull values of a column expression in the current window. “MOVINGMIN Function” (page 382) Returns the minimum of nonnull values of a column expression in the current window.
PAGE 295
Other Functions and Expressions Use these other functions and expressions in an SQL value expression: “BITAND Function” (page 304) Performs 'and' operation on corresponding bits of the two operands. “CASE (Conditional) Expression” (page 306) A conditional expression. The two forms of the CASE expression are simple and searched. “CAST Expression” (page 309) Converts a value from one data type to another data type that you specify.
PAGE 296
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 227).
PAGE 297
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 227).. Examples of ACOS • This function returns the value 3.49044274380724352E-001 or approximately 0.
PAGE 298
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 220).
PAGE 299
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 218).
PAGE 300
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 227). Examples of ASIN • This function returns the value 3.49044414403046464E-001 or approximately 0.
PAGE 301
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 227). Examples of ATAN • This function returns the value 8.72766423249958400E-001 or approximately 0.8727 in radians (which is 50 degrees): ATAN (1.
PAGE 302
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 227). Example of ATAN2 This function returns the value 2.
PAGE 303
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 304
--- 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. 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.
PAGE 305
--- 1 row(s) selected >>select 1 & 3 from (values(1)) x(a); (EXPR) -------------1 --- 1 row(s) selected >>select bitand(1,3) + 0 from (values(1)) x(a); (EXPR) -------------1 --- 1 row(s) selected BITAND Function 305
PAGE 306
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 307
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 308
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 309
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 310
Examples of CAST • In this example, the fractional portion is discarded: 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) • The PROJECT table contains a column START_DATE of data type DATE and a column SHIP_TIMESTAMP of data type TIMESTAMP.
PAGE 311
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 227). Example of CEILING This function returns the integer value 3.
PAGE 312
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 or UCS2. The returned character will be associated with the character set specified by char-set-name with the DEFAULT collation. The default is ISO88591.
PAGE 313
CHAR_LENGTH Function The CHAR_LENGTH function returns the number of characters in a string. You can also use CHARACTER_LENGTH. 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. See “Character Value Expressions” (page 218).
PAGE 314
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 315
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 any character sets allowed. CODE_VALUE is a Neoview SQL extension. CODE_VALUE(character-value-expression) character-value-expression is a character string.
PAGE 316
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. The result of the CONCAT function is the concatenation of character-expr-1 with character-expr-2.
PAGE 317
VALUES (002, 'Executed at ' || CAST (CURRENT_TIMESTAMP AS CHAR(26))); CONCAT Function 317
PAGE 318
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 Relationship to the JULIANTIMESTAMP Function The operand of CONVERTTIMESTAMP is a Julian timestamp, and the function result is a value of data type TIMESTAMP.
PAGE 319
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 227). Example of COS This function returns the value 9.39680940386503936E-001, or approximately 0.9397, the cosine of 0.3491 (which is 20 degrees): COS (0.
PAGE 320
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 227). Example of COSH This function returns the value 1.88842387716101616E+000, or approximately 1.8884, the hyperbolic cosine of 1.
PAGE 321
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 322
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 323
CURRENT Function The CURRENT function returns a value of type TIMESTAMP based on the current local date and time. You can also use “CURRENT_TIMESTAMP Function” (page 327). 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 324
CURRENT_DATE Function The CURRENT_DATE function returns the local current date as a value of type DATE. CURRENT_DATE The CURRENT_DATE function returns the current date, such as 1997-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.orders WHERE deliv_date >= CURRENT_DATE; • The PROJECT table has a column EST_COMPLETE of type INTERVAL DAY.
PAGE 325
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() Example of CURRENT_ROLE Logon with role of role1.role1 and with an alias of abcd. >>SELECT CURRENT_ROLE FROM (values(1)) x(a); (EXPR) ------------------------role1.role1 --- 1 row(s) selected. >>select user from (values(1)) x(a); (EXPR) ------------------------abcd --- 1 row(s) selected.
PAGE 326
CURRENT_TIME Function The CURRENT_TIME function returns the current local time as a value of type TIME. 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. For example, the function CURRENT_TIME (2) returns the current time as a value of data type TIME, where the precision of the fractional seconds is 2—for example, 14:01:59.30.
PAGE 327
CURRENT_TIMESTAMP Function The CURRENT_TIMESTAMP function returns a value of type TIMESTAMP based on the current local date and time. You can also use the “CURRENT Function” (page 323). 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. The default is 6.
PAGE 328
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 440). Example of CURRENT_USER This example retrieves the user name value for the current user SELECT CURRENT_USER FROM logfile; (EXPR) ----------------------DCS.TSHAW ...
PAGE 329
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 289). 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 330
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 289). 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 331
num_expr is an SQL exact numeric value expression that specifies how many datepart units of time are to be added to datetime_expr. If num_expr has a fractional portion, it is ignored. If num_expr is negative, the return value precedes datetime_expr by the specified amount of time. See “Numeric Value Expressions” (page 227). datetime_expr is an expression that evaluates to a datetime value of type DATE or TIMESTAMP.
PAGE 332
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. DATEDIFF (datepart, startdate, enddate) datepart is YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER, WEEK, or one of the following abbreviations YY and YYYY M and MM D and DD HH MI and M SS and S Q and QQ WW and WK startdate may be of type DATE or TIMESTAMP.
PAGE 333
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 220). DEFAULT | USA | EUROPEAN specifies a format for a datetime value.
PAGE 334
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 335
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 336
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 220).
PAGE 337
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 220).
PAGE 338
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 220).
PAGE 339
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 220).
PAGE 340
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 220).
PAGE 341
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 342
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 343
This statement is equivalent and will not produce an error: SELECT decode( (?p1 || ?p2), trim(?p1), ‘Hi’ ) from emp; DECODE Function 343
PAGE 344
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 227). Examples of DEGREES • This function returns the value 45 in degrees: DEGREES (0.78540) • This function returns the value 45.
PAGE 345
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 278). 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 346
--- 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 347
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 278). 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 348
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 345).
PAGE 349
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 227). 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 350
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 350). 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 351
A node of an operator tree is a point in the tree that represents an event (involving an operator) in a plan. Each node might have subordinate nodes—that is, each event might generate a subordinate event or events in the plan. Column Name Data Type Description MODULE_NAME CHAR(60) Reserved for future use. STATEMENT_ NAME CHAR(60) Statement name; truncated on the right if longer than 60 characters.
PAGE 352
IDLETIME An estimate of the number of seconds to wait for an event to happen. The estimate includes the amount of time to open a table or start an ESP process. PROBES The number of times the operator will be executed. Usually, this value is 1, but it can be greater when you have, for example, an inner scan of a nested-loop join.
PAGE 353
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 220) and “Interval Value Expressions” (page 223).
PAGE 354
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 227). Examples of FLOOR This function returns the integer value 2.00000000000000040E+000, represented as a FLOAT data type: FLOOR (2.
PAGE 355
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 220).
PAGE 356
INSERT Function The INSERT function returns a character string where a specified number of characters within the character string have been deleted beginning at a specified start position and then another character string has been inserted at the start position. INSERT is a Neoview SQL extension. INSERT (char-expr-1, start, length, char-expr-2) char-expr-1, char-expr-2 are SQL character value expressions (of data type CHAR or VARCHAR) that specify two strings of characters.
PAGE 357
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 358
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. JULIANTIMESTAMP is a Neoview SQL extension. JULIANTIMESTAMP (datetime-expression) datetime-expression is an expression that evaluates to a value of type DATE, TIME, or TIMESTAMP.
PAGE 359
SELECT LASTNOTNULL (I1) AS LASTNOTNULL FROM mining.seqfcn SEQUENCE BY TS; LASTNOTNULL ----------6215 6215 19058 19058 11966 --- 5 row(s) selected.
PAGE 360
LCASE Function The LCASE function downshifts characters. 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. LCASE returns a string of fixed-length or variable-length character data, depending on the data type of the input string.
PAGE 361
LEFT Function The LEFT function returns the leftmost specified number of characters from a character expression . 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. See “Character Value Expressions” (page 218).
PAGE 362
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. The result returned by the LOCATE function is equal to the result returned by the POSITION function. LOCATE is a Neoview SQL extension. LOCATE (substring-expression,source-expression) substring-expression is an SQL character value expression that specifies the substring to search for in source-expression.
PAGE 363
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 227). Example of LOG This function returns the value 6.93147180559945504E-001, or approximately 0.69315: LOG (2.
PAGE 364
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 227). Example of LOG10 This function returns the value 1.39794000867203792E+000, or approximately 1.
PAGE 365
LOWER Function • • “Considerations for LOWER” “Example of LOWER” The LOWER function downshifts characters. 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 . The result returned by the LOWER function is equal to the result returned by the LCASE function.
PAGE 366
Table 6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) 366 x L (x) x L(x) x L(x) x L(x) x L(x) x L(x) 0051 0071 0196 0269 0410 0430 0546 0576 1E7C 1E7D 1F9B 1F93 0052 0072 0197 0268 0411 0431 0547 0577 1E7E 1E7F 1F9C 1F94 0053 0073 0198 0199 0412 0432 0548 0578 1E80 1E81 1F9D 1F95 0054 0074 019C 026F 0413 0433 0549 0579 1E82 1E83 1F9E 1F96 0055 0075 019D 0272 0414 0434 054A 057A 1E84 1E85 1F9F 1F97 0056 0076
PAGE 367
Table 6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) x L (x) x L(x) x L(x) x L(x) x L(x) x L(x) 00DB 00FB 01DE 01DF 0468 0469 10B3 10E3 1ECE 1ECF 1FFC 1FF3 00DC 00FC 01E0 01E1 046A 046B 10B4 10E4 1ED0 1ED1 2160 2170 00DD 00FD 01E2 01E3 046C 046D 10B5 10E5 1ED2 1ED3 2161 2171 00DE 00FE 01E4 01E5 046E 046F 10B6 10E6 1ED4 1ED5 2162 2172 0100 0101 01E6 01E7 0470 0471 10B7 10E7 1ED6 1ED7 2163 2173 0102 0103 01E8
PAGE 368
Table 6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) 368 x L (x) x L(x) x L(x) x L(x) x L(x) x L(x) 0141 0142 0393 03B3 04BE 04BF 1E22 1E23 1F28 1F20 24C9 24E3 0143 0144 0394 03B4 04C1 04C2 1E24 1E25 1F29 1F21 24CA 24E4 0145 0146 0395 03B5 04C3 04C4 1E26 1E27 1F2A 1F22 24CB 24E5 0147 0148 0396 03B6 04C7 04C8 1E28 1E29 1F2B 1F23 24CC 24E6 014A 014B 0397 03B7 04CB 04CC 1E2A 1E2B 1F2C 1F24 24CD 24E7 014C 014D
PAGE 369
Example of LOWER Suppose that your CUSTOMER table includes an entry for Hotel Oregon. Select the column CUSTNAME and return the result in uppercase and lowercase letters by using the UPPER and LOWER functions: SELECT custname,UPPER(custname),LOWER(custname) FROM sales.customer; (EXPR) ----------------... Hotel Oregon (EXPR) ------------------... HOTEL OREGON (EXPR) -----------------... hotel oregon --- 17 row(s) selected. See “UPPER Function” (page 438).
PAGE 370
LPAD Function The LPAD function pads the left side of a string with the specified string. LPAD is a Neoview extension. LPAD (str, len [,padstr]) str can be an expression. See “Character Value Expressions” (page 218). len can be an expression but must be an integral value. If len is equal to the length of the string, no change is made. If len is smaller than the string size, the string is truncated. pad-character can be an expression and may be a string.
PAGE 371
LTRIM Function The LTRIM function removes leading spaces from a character string. 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 218). Considerations for LTRIM Result of LTRIM The result is always of type VARCHAR, with maximum length equal to the fixed length or maximum variable length of character-expression.
PAGE 372
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 373
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 374
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 220).
PAGE 375
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 376
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 220).
PAGE 377
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 220).
PAGE 378
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 278). 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 379
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 278). 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 380
--- 5 row(s) selected.
PAGE 381
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 278). 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 382
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 278). 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 383
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 278). 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 384
.000 15527.357 14802.016 15115.012 6036.275 --- 5 row(s) selected.
PAGE 385
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 278). 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 386
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 278). 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 387
241098840.500 219099697.000 228463602.333 36436621.000 --- 5 row(s) selected.
PAGE 388
NULLIF Function The NULLIF function compares the value of two expressions. Both expressions must be of comparable types. The return value is the value of the first argument when that value is not NULL. 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 389
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 390
NVL Function The NVL function determines if the selected column has a null value and then returns the new-operand value; otherwise the operand value is returned. NVL (operand, ) operand specifies a value expression. new-operand specifies a value expression. operand and new-operand must be comparable data types. If operand is a null value, NVL returns new-operand. If operand is not a null value, NVL returns operand.
PAGE 391
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 218).
PAGE 392
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 278). 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 393
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 394
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. The result returned by the POSITION function is equal to the result returned by the LOCATE function. POSITION (substring-expression IN source-expression) substring-expression is an SQL character value expression that specifies the substring to search for in source-expression.
PAGE 395
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 227).
PAGE 396
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 220).
PAGE 397
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 227). Examples of RADIANS • Return the value 7.85398150000000160E-001, or approximately 0.78540 in degrees: RADIANS (45) • Return the value 45 in degrees.
PAGE 398
RANK/RUNNINGRANK Function The RUNNINGRANK function is a sequence function that returns the rank of the given value of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. RANK is an alternative syntax for RUNNINGRANK. RUNNINGRANK is a Neoview extension. RUNNINGRANK(expression) | RANK(expression) expression specifies the expression on which to perform the rank. RUNNINGRANK returns the rank of the expression within the intermediate result table.
PAGE 399
SELECT I1, RUNNINGRANK (I1) AS RANK FROM cat.sch.seqfcn SEQUENCE BY I1; I1 ----------1 2 3 4 5 6 8 10 RANK -------------------1 2 3 4 5 6 7 8 --- 8 row(s) selected. Return the rank of I1 descending: SELECT I1, RUNNINGRANK (I1) AS RANK FROM cat.sch.seqfcn SEQUENCE BY I1 DESC; I1 RANK ----------- -------------------- 10 8 6 5 4 3 2 1 1 2 3 4 5 6 7 8 --- 8 row(s) selected. Return the rank of I2, using the alternative RANK syntax: SELECT I2, RANK (I2) AS RANK FROM cat.sch.
PAGE 400
----------? ? 300 200 200 200 100 100 -------------------1 1 3 4 4 4 7 7 --- 8 row(s) selected. Return the rank of I2 descending, excluding NULL values: SELECT I2, RANK (I2) AS RANK FROM cat.sch.seqfcn WHERE I2 IS NOT NULL SEQUENCE BY I2 DESC; I2 RANK ----------- -------------------300 1 200 2 200 2 200 2 100 5 100 5 --- 6 row(s) selected.
PAGE 401
1.48020166531456112E+004 1.25639147428923072E+004 1.09258501408357232E+004 --- 5 row(s) selected. Note that you can use the CAST function for display purposes. For example: SELECT CAST(RUNNINGSTDDEV (I1) AS DEC (18,3)) FROM mining.seqfcn SEQUENCE BY TS; (EXPR) -------------------.000 15527.357 14802.016 12563.914 10925.850 --- 5 row(s) selected. REPEAT Function The REPEAT function returns a character string composed of the evaluation of a character expression repeated a specified number of times.
PAGE 402
Example of REPLACE • Use the REPLACE function to change job descriptions so that occurrences of the company name are updated: SELECT jobdesc FROM persnl.job; Job Description -----------------MANAGER COMNET PRODUCTION COMNET ASSEMBLER COMNET SALESREP COMNET SYSTEM ANAL COMNET ... --- 10 row(s) selected. UPDATE persnl.job SET jobdesc = REPLACE (jobdesc, 'COMNET', 'TDMNET'); Job Description -----------------MANAGER TDMNET PRODUCTION TDMNET ASSEMBLER TDMNET SALESREP TDMNET SYSTEM ANAL TDMNET ...
PAGE 403
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 404
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. Otherwise, the condition is evaluated beginning with the previous row. If you specify INCLUSIVE and the condition is true in the current row, ROWS SINCE returns 0. condition specifies a condition to be considered for each row in the result table. Each column in condition must be a column that exists in the result table.
PAGE 405
--- 5 row(s) selected. 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 278). ROWS SINCE CHANGED is a Neoview SQL extension.
PAGE 406
rpad('kite', 7) • This function returns 'ki'. rpad('kite', 2) • This function returns 'kite0000'. rpad('kite', 8, '0') • This function returns 'go fly a kite' rpad('go fly a kite', 13, 'z') • This function returns 'go fly a kitez' rpad('go fly a kite', 14, 'z') • This function returns 'kitegoflygoflygof' rpad('kite', 17, 'gofly' ) RTRIM Function The RTRIM function removes trailing spaces from a character string. RTRIM is a Neoview SQL extension.
PAGE 407
Considerations for RUNNINGAVG Equivalent Result The result of RUNNINGAVG is equivalent to: RUNNINGSUM(column-expr) / RUNNINGCOUNT(*) Example of RUNNINGAVG Return the average of nonnull values of I1 up to and including the current row: SELECT RUNNINGAVG (I1) AS AVG_I1 FROM mining.seqfcn SEQUENCE BY TS; AVG_I1 -------------------6215 17194 11463 9746 10190 --- 5 row(s) selected.
PAGE 408
SEQUENCE BY TS; COUNT_I1 -----------1 2 2 3 4 --- 5 row(s) selected. 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 278). RUNNINGMAX is a Neoview SQL extension.
PAGE 409
SELECT RUNNINGMIN (I1) AS MIN_I1 FROM mining.seqfcn SEQUENCE BY TS; MIN_I1 -----------6215 6215 6215 4597 4597 --- 5 row(s) selected.
PAGE 410
RUNNINGRANK Function See “RANK/RUNNINGRANK Function” (page 398).
PAGE 411
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 278). RUNNINGSTDDEV is a Neoview SQL extension. RUNNINGSTDDEV (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 412
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 278). RUNNINGSUM is a Neoview SQL extension. RUNNINGSUM (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 413
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 278). RUNNINGVARIANCE is a Neoview SQL extension. RUNNINGVARIANCE (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 414
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 220).
PAGE 415
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 227).
PAGE 416
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 227). Example of SIN This function returns the value 3.42052233254419920E-001, or approximately 0.3420, the sine of 0.3491 (which is 20 degrees): SIN (0.
PAGE 417
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 227). Example of SINH This function returns the value 1.60191908030082600E+000, or approximately 1.6019, the hyperbolic sine of 1.25: SINH (1.
PAGE 418
SPACE Function The SPACE function returns a character string consisting of a specified number of spaces. 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. length cannot exceed 4096. char-set-name can be ISO88591 or UCS2. The default is ISO88591.
PAGE 419
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 227). Example of SQRT This function returns the value 5.19615242270663312E+000, or approximately 5.
PAGE 420
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 421
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 422
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 maximum length equal to the fixed length or maximum variable length of the character expression. SUBSTR is equivalent to SUBSTRING. SUBSTRING (character-expr FROM start-position [FOR length]) or: SUBSTRING (character-expr,start-position,length) character-expr specifies the source string from which to extract the substring.
PAGE 423
SUBSTRING('Robert John Smith' FROM 0 FOR 3) SUBSTR('Robert John Smith' FROM 0 FOR 3) • Extract 'John': SUBSTRING ('Robert John Smith' FROM 8 FOR 4) SUBSTR ('Robert John Smith' FROM 8 FOR 4) • Extract 'John Smith': SUBSTRING ('Robert John Smith' FROM 8) SUBSTR ('Robert John Smith' FROM 8) • Extract 'Robert John Smith': SUBSTRING ('Robert John Smith' FROM 1 FOR 17) SUBSTR ('Robert John Smith' FROM 1 FOR 17) • Extract 'John Smith': SUBSTRING ('Robert John Smith' FROM 8 FOR 15) SUBSTR ('Robert John Smit
PAGE 424
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 425
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 227). Example of TAN • This function returns the value 3.64008908293626896E-001, or approximately 0.3640, the tangent of 0.3491 (which is 20 degrees): TAN (0.
PAGE 426
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 227). Example of TANH • This function returns the value 8.48283639957513088E-001 or approximately 0.8483, the hyperbolic tangent of 1.
PAGE 427
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 403). THIS is a Neoview SQL extension. THIS (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 428
TRANSLATE Function The TRANSLATE function translates a character string from a source character set to a target character set. TRANSLATE(character-value-expression USING translation-name) character-value-expression is a character string. translation-name is one of these translation names: Translation Name Source Character Set Target Character Set Comments ISO8859XToUCS2 (X in 1) ISO8859X UCS2 No data loss is possible. UCS2ToISO8859X (X in 1) UCS2 ISO8859X No data loss is possible.
PAGE 429
TRIM Function The TRIM function removes leading and trailing characters from a character string. 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. trim_char is an SQL character value expression and specifies the character to be trimmed from trim-source.
PAGE 430
UCASE Function • • “Considerations for UCASE” “Examples of UCASE” The UCASE function upshifts characters. 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. The result returned by the UCASE function is equal to the result returned by the UPPER or UPSHIFT function.
PAGE 431
Table 6-2 One-to-One UCS2 Mappings (continued) x U (x) x U(x) x U(x) x U(x) x U(x) x U(x) 006D 004D 0199 0198 03E7 03E6 04EF 04EE 1E8D 1E8C 1F80 1F88 006E 004E 01A1 01A0 03E9 03E8 04F1 04F0 1E8F 1E8E 1F81 1F89 006F 004F 01A3 01A2 03EB 03EA 04F3 04F2 1E91 1E90 1F82 1F8A 0070 0050 01A5 01A4 03ED 03EC 04F5 04F4 1E93 1E92 1F83 1F8B 0071 0051 01A8 01A7 03EF 03EE 04F9 04F8 1E95 1E94 1F84 1F8C 0072 0052 01AD 01AC 03F0 039A 0561 0531 1E
PAGE 432
Table 6-2 One-to-One UCS2 Mappings (continued) 432 x U (x) x U(x) x U(x) x U(x) x U(x) x U(x) 00F6 00D6 01F3 01F1 044C 042C 0580 0550 1EDD 1EDC 2171 2161 00F8 00D8 01F5 01F4 044D 042D 0581 0551 1EDF 1EDE 2172 2162 00F9 00D9 01FB 01FA 044E 042E 0582 0552 1EE1 1EE0 2173 2163 00FA 00DA 01FD 01FC 044F 042F 0583 0553 1EE3 1EE2 2174 2164 00FB 00DB 01FF 01FE 0451 0401 0584 0554 1EE5 1EE4 2175 2165 00FC 00DC 0201 0200 0452 0402 0585 05
PAGE 433
Table 6-2 One-to-One UCS2 Mappings (continued) x U (x) x U(x) x U(x) x U(x) x U(x) x U(x) 0137 0136 0345 0399 0493 0492 1E3B 1E3A 1F27 1F2F 24E5 24CB 013A 0139 03AC 0386 0495 0494 1E3D 1E3C 1F30 1F38 24E6 24CC 013C 013B 03AD 0388 0497 0496 1E3F 1E3E 1F31 1F39 24E7 24CD 013E 013D 03AE 0389 0499 0498 1E41 1E40 1F32 1F3A 24E8 24CE 0140 013F 03AF 038A 049B 049A 1E43 1E42 1F33 1F3B 24E9 24CF 0142 0141 03B1 0391 049D 049C 1E45 1E44 1F
PAGE 434
Table 6-3 Two-Character UCS2 Mapping (continued) 434 Titlecase characters Two-character uppercase expansions 0x01F0 0x004A 0x030C 0x0587 0x0535 0x0552 0x1E96 0x0048 0x0331 0x1E97 0x0054 0x0308 0x1E98 0x0057 0x030A 0x1E99 0x0059 0x030A 0x1E9A 0x0041 0x02BE 0x1F50 0x03A5 0x0313 0x1F80 0x1F08 0x0399 0x1F81 0x1F09 0x0399 0x1F82 0x1F0A 0x0399 0x1F83 0x1F0B 0x0399 0x1F84 0x1F0C 0x0399 0x1F85 0x1F0D 0x0399 0x1F86 0x1F0E 0x0399 0x1F87 0x1F0F 0x0399 0x1F88 0x1F08 0x0399 0x1F89
PAGE 435
Table 6-3 Two-Character UCS2 Mapping (continued) Titlecase characters Two-character uppercase expansions 0x1F9C 0x1F2C 0x0399 0x1F9D 0x1F2D 0x0399 0x1F9E 0x1F2E 0x0399 0x1F9F 0x1F2F 0x0399 0x1FA0 0x1F68 0x0399 0x1FA1 0x1F69 0x0399 0x1FA2 0x1F6A 0x0399 0x1FA3 0x1F6B 0x0399 0x1FA4 0x1F6C 0x0399 0x1FA5 0x1F6D 0x0399 0x1FA6 0x1F6E 0x0399 0x1FA7 0x1F6F 0x0399 0x1FA8 0x1F68 0x0399 0x1FA9 0x1F69 0x0399 0x1FAA 0x1F6A 0x0399 0x1FAB 0x1F6B 0x0399 0x1FAC 0x1F6C 0x0399 0x1FAD 0x1F6D
PAGE 436
Table 6-3 Two-Character UCS2 Mapping (continued) Titlecase characters Two-character uppercase expansions 0x1FF6 0x03A9 0x0342 0x1FFC 0x03A9 0x0399 0xFB00 0x0046 0x0046 0xFB01 0x0046 0x0049 0xFB02 0x0046 0x004C 0xFB05 0x0053 0x0054 0xFB06 0x0053 0x0054 0xFB13 0x0544 0x0546 0xFB14 0x0544 0x0535 0xFB15 0x0544 0x053B 0xFB16 0x054E 0x0546 0xFB17 0x0544 0x053D Table 6-4 Three-Character UCS2 Mapping Titlecase characters Three-Character Uppercase Expansions 0x0390 0x0399 0x0308 0x0301
PAGE 437
----------------... Hotel Oregon ------------------... HOTEL OREGON -----------------... hotel oregon --- 17 row(s) selected. See “LCASE Function” (page 360). For more examples of when to use the UCASE function, see “UPSHIFT Function” (page 439).
PAGE 438
UPPER Function The UPPER function upshifts characters. 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 or UCASE function.
PAGE 439
UPSHIFT Function The UPSHIFT function upshifts characters. 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. The result returned by the UPSHIFT function is equal to the result returned by the UPPER or UCASE function.
PAGE 440
USER Function The USER function returns the username 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 Example of USER Logon with role of role1.role1 and with an alias of abcd. >>SELECT CURRENT_ROLE FROM (values(1)) x(a); (EXPR) ------------------------role1.role1 --- 1 row(s) selected.
PAGE 441
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 442
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 443
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. WEEK is a Neoview SQL extension. WEEK (datetime-expression) datetime-expression is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. See “Datetime Value Expressions” (page 220).
PAGE 444
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 220). 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 445
ZEROIFNULL Function The ZEROIFNULL function returns a value of zero 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 446
PAGE 447
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 448
| unsigned-integer PRECEDING following-row is: UNBOUNDED FOLLOWING | unsigned-integer FOLLOWING 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. DISTINCT is not supported for windows functions. expression specifies a numeric or interval value expression that determines the values to average.
PAGE 449
UNBOUNDED PRECEDING | unsigned-integer PRECEDING following-row is: UNBOUNDED FOLLOWING | unsigned-integer FOLLOWING ALL | DISTINCT specifies whether duplicate values are included in the computation of the COUNT of the expression. The default option is ALL, which causes duplicate values to be included. DISTINCT is not supported for windows functions. expression specifies a value expression that is to be counted. See “Expressions” (page 218).
PAGE 450
optional ORDER BY clause. The PARTITION BY clause specifies how the intermediate result is partitioned and the ORDER BY clause specifies how the rows are ordered within each partition. Examples of DENSE_RANK Window Function • Return the dense rank for each employee based on employee number: SELECT DENSE_RANK() OVER (ORDER BY empnum), * FROM persnl.
PAGE 451
SELECT empnum, MAX(salary) OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING) FROM persnl.employee; • Return the running maximum of the SALARY column within each department: SELECT deptnum, empnum, MAX(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING) FROM persnl.employee; • Return the moving maximum of salary within each department over a window of the last 4 rows: SELECT deptnum, empnum, MAX(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 452
SELECT empnum, MIN(salary) OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING) FROM persnl.employee; • Return the running minimum of the SALARY column within each department: SELECT deptnum, empnum, MIN(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING) FROM persnl.employee; • Return the moving minimum of salary within each department over a window of the last 4 rows: SELECT deptnum, empnum, MIN(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 453
optional ORDER BY clause. The PARTITION BY clause specifies how the intermediate result is partitioned and the ORDER BY clause specifies how the rows are ordered within each partition. window-frame-clause specifies the window within the partition over which the ROW_NUMBER is computed. Examples of ROW_NUMBER Window Function • Return the row number for each row of the employee table: SELECT ROW_NUMBER () OVER(ORDER BY empnum), * FROM persnl.
PAGE 454
Examples of STDDEV • Return the standard deviation of the salary for each row of the employee table: SELECT STDDEV(salary) OVER(ORDER BY empnum ROWS UNBOUNDED PRECEDING),* FROM persnl.employee; • Return the standard deviation for each row within each department: SELECT STDDEV() OVER(PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING), * FROM persnl.
PAGE 455
SELECT empnum, SUM (salary) OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING) FROM persnl.employee; • Return the running sum of the SALARY column within each department: SELECT deptnum, empnum, SUM (salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING) FROM persnl.employee; • Return the moving sum of the SALARY column within each department over a window of the last 4 rows: SELECT deptnum, empnum, SUM (salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING) FROM persnl.
PAGE 456
window-frame-clause specifies the window within the partition over which the VARIANCE is computed. Examples of VARIANCE • Return the variance of the SALARY column:: SELECT empnum, VARIANCE (salary) OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING) FROM persnl.employee; • Return the variance of the SALARY column within each department: SELECT deptnum, empnum, VARIANCE (salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING) FROM persnl.
PAGE 457
A Quick Reference This appendix provides a quick, alphabetic reference to commands, statements, and utilities. For other topics, see the Index.
PAGE 458
P “POPULATE INDEX Utility” (page 197) “PURGEDATA Utility” (page 198) “PREPARE Statement” (page 135) R “REVOKE Statement” (page 137) “REVOKE EXECUTE Statement” (page 139) “REVOKE SCHEMA Statement” (page 141) “ROLLBACK WORK Statement” (page 143) S “SELECT Statement” (page 144) “SET SCHEMA Statement” (page 166) “SET SERVICE Statement” (page 167) “SET TABLE TIMEOUT Statement” (page 168) “SET TRANSACTION Statement” (page 170) “SHOWDDL Statement” (page 175) U “UPDATE Statement” (page 179) “UPDATE STATISTICS St
PAGE 459
B Reserved Words The words listed in this appendix are reserved for use by Neoview SQL. To prevent syntax errors, avoid using these words as identifiers in Neoview SQL. In Neoview SQL, if an HP operating system name contains a reserved word, you must enclose the reserved word in double quotes (") to access that column or object. In Table B-1, an asterisk (*) indicates reserved words that are Neoview SQL extensions. Words reserved by the ANSI standard are not marked.
PAGE 460
Table B-1 Reserved SQL Identifiers (continued) BIT_LENGTH INITIALLY SCHEMA BLOB1 INNER SCOPE BOOLEAN INOUT SCROLL BOTH INPUT SEARCH BREADTH INSENSITIVE SECOND BY INSERT SECTION CALL INT SELECT CASE INTEGER SENSITIVE1 CASCADE INTERSECT SESSION CASCADED INTERVAL SESSION_USER CAST INTO SET CATALOG IS SETS CHAR ISOLATION SIGNAL1 CHAR_LENGTH ITERATE SIMILAR1 CHARACTER JOIN SIZE CHARACTER_LENGTH KEY SMALLINT CHECK LANGUAGE SOME CLASS LARGE CLOB LAST SPECIFIC
PAGE 461
Table B-1 Reserved SQL Identifiers (continued) CUBE MINUTE SQLWARNING CURRENT MODIFIES STRUCTURE CURRENT_DATE MODIFY SUBSTRING CURRENT_PATH MODULE SUM CURRENT_ROLE MONTH SYSTEM_USER CURRENT_TIME NAMES TABLE CURRENT_TIMESTAMP NATIONAL TEMPORARY CURRENT_USER NATURAL TERMINATE CURSOR NCHAR TEST1 CYCLE NCLOB THAN1 DATE NEW THEN DATETIME1 NEXT THERE1 DAY NO TIME DEALLOCATE NONE TIMESTAMP DEC NOT TIMEZONE_HOUR DECIMAL NULL TIMEZONE_MINUTE DECLARE NULLIF TO DEFAUL
PAGE 462
Table B-1 Reserved SQL Identifiers (continued) EACH PAD VALUE PARAMETER VALUES ELSEIF PARAMETERS VARCHAR END PARTIAL VARIABLE END-EXEC PENDANT1 VARYING EQUALS POSITION VIEW ESCAPE POSTFIX VIRTUAL1 EXCEPT PRECISION VISIBLE1 EXCEPTION PREFIX WAIT1 EXEC PREORDER WHEN EXECUTE PREPARE WHENEVER EXISTS PRESERVE WHERE EXTERNAL PRIMARY WHILE1 EXTRACT PRIOR WITH FALSE PRIVATE1 WITHOUT FETCH PRIVILEGES WORK FIRST PROCEDURE WRITE FLOAT PROTECTED1 YEAR ELSE 1 ZONE 1
PAGE 463
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 464
PAGE 465
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 466
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 467
ID, Feature Level of Support E071-03 EXCEPT [DISTINCT] table operator E121 Basic cursor support Neoview SQL fully supports these subfeatures: E121-01 DECLARE CURSOR E121-02 ORDER BY columns need not be in select list E121-06 Positioned UPDATE statement E121-07 Positioned DELETE statement E121-08 CLOSE statement Neoview SQL partially supports these subfeatures: E121-04 OPEN statement (Neoview SQL syntax does not match that of SQL '99) E121-10 FETCH statement, implicit NEXT (Neoview SQL syntax does not mat
PAGE 468
ID, Feature Level of Support E161 SQL comments using leading double minus The interface does not properly process SQL comments when they are issued from the Manager F051 Basic date and time Neoview SQL fully supports these subfeatures: F051-01 DATE data type (and literal) F051-02 TIME data type (and literal) with fractional seconds precision of at least 0 F051-03 TIMESTAMP data type (and literal) with fractional seconds precision of at least 0 and 6 F051-04 Comparison predicate for DATE, TIME, and TIME
PAGE 469
ID, Feature Level of Support F311-03 CREATE VIEW (without WITH CHECK OPTION and without Feature F081 “UNION and EXCEPT in views” Neoview SQL views cannot refer to tables created in the same CREATE SCHEMA) F311-04 CREATE VIEW: WITH CHECK OPTION (Without support for Feature F081 “UNION and EXCEPT in views” Neoview SQL views cannot refer to tables created in the same CREATE SCHEMA) F311-05 GRANT statement (Neoview SQL does not support creation of a schema and its contents in a single statement, including perf
PAGE 470
ID Feature F321 User authorization (no support for SYSTEM_USER) F381-02 ALTER TABLE statement: ADD CONSTRAINT clause F381-03 ALTER TABLE statement: DROP CONSTRAINT clause F401-01 NATURAL JOIN F401-04 CROSS JOIN F461 Named character sets F491 Constraint management F561 Full value expressions T171 LIKE clause in table definition (not exact Core SQL:1999 syntax) T211 Basic trigger capability (except for T211-07 Trigger privilege) T212 Enhanced trigger capability T441 ABS and MOD functi
PAGE 471
Index A ABS function examples of, 296, 298 syntax diagram of, 296 Access options summary of, 30 DELETE statement use of, 92 DML statements use of, 30 INSERT statement use of, 124 READ COMMITTED, 30 READ UNCOMMITTED , 30 REPEATABLE READ, 31 SELECT statement use of, 153 SERIALIZABLE, 31, 37 SKIP CONFLICT, 31 UPDATE statement use of, 181 Access privileges stored procedures, 120 tables, 118, 122 ACOS function examples of, 297 syntax diagram of, 297, 298 AFTER LAST ROW clause, 148 Aggregate functions summary of,
PAGE 472
syntax diagram of, 306 CAST expression data type conversion, 309 examples of, 310 syntax diagram of, 309 valid type combinations, 309 CEILING function examples of, 311 syntax diagram of, 311 CHAR data type, 210, 211 CHAR function examples of, 312 syntax diagram of, 312 CHAR VARYING data type, 211 CHAR_LENGTH function examples of, 313 syntax diagram of, 313 Character sets setting default, 233 support standards, 470 character sets, 200 Character string data types CHAR and VARCHAR, differences, 211 examples of
PAGE 473
syntax diagram of, 319 COSH function examples of, 320 syntax diagram of, 320 COUNT function DISTINCT clause within, 321 examples of, 321 syntax diagram of, 321 COUNT window function examples of, 449 syntax diagram of, 448 CREATE INDEX statement authorization and availability requirements , 59 examples of, 59 limits on indexes, 59, 463 syntax diagram of, 58 CREATE MATERIALIZED VIEW statement considerations for, 62 example of, 64 syntax diagram of, 60 CREATE MVGROUP statement authorization and availability re
PAGE 474
DELETE statement, 91 INSERT statement, 124 SELECT statement, 145 UPDATE statement, 179 Data type conversion, CAST expression, 309 Data types approximate numeric descriptions of, 215 DOUBLE PRECISION, 216 FLOAT, 216 REAL, 216 character, 210 comparable and compatible, 209 datetime DATE, 212 TIME, 212 TIMESTAMP, 212 exact numeric DECIMAL, 216 descriptions of, 215 INTEGER, 216 LARGEINT, 216 NUMERIC, 215 PICTURE, 216 SMALLINT, 216 fixed length character CHAR, 210 NATIONAL CHAR, 211 NCHAR, 211 PIC, 211 interval,
PAGE 475
CREATE TABLE use of, 72, 268 examples of, 269 syntax diagram of, 268 DEFAULT value, using, 126, 180 DEGREES function examples of, 344 syntax diagram of, 344 DELETE statement access options, 92 SET ON ROLLBACK clause, 92 SET ROLLBACK clause, 92 SKIP CONFLICT access, 93 STREAM clause, 92, 180 syntax diagram of, 91 WHERE clause, 92 DELETE statement (embedded) searched form, 91 Delimited identifiers, 230 DENSE_RANK window function examples of, 450 syntax diagram of, 449 Derived column names examples of, 201 syn
PAGE 476
CARDINALITY, 113 DESCRIPTION, 113 DETAIL_COST, 113 EST_TOTAL_COST, 110 LEFT_CHILD_SEQ_ NUM, 112 MODULE_NAME, 112 OPERATOR, 112 OPERATOR_COST, 113 PLAN_ID, 112 REQUESTS_IN, 108 REQUESTS_IN field, 108 RIGHT_CHILD_ SEQ_NUM, 112 ROWS_OUT, 108 SEQ_NUM, 112 STATEMENT_NAME, 112 TNAME, 113 TOTAL_COST, 113 Explain statement displayed, 106 operators, 106 OPTIONS ’e’ considerations, 110 OPTIONS ’f’ considerations, 107 OPTIONS ’m’ considerations, 112 OPTIONS ’n’ considerations, 108 output, 108 reviewing query execution
PAGE 477
syntax diagram of, 223 ISNULL function examples of, 357 syntax diagram of, 357 ISO standards, 465 Isolation levels READ COMMITTED, 36 READ UNCOMMITTED, 36 REPEATABLE READ, 37 SERIALIZABLE, 31, 37 J Join CROSS, 150 FULL, 150 JOIN ON, 150 join predicate, 160 LEFT, 150 limits, 155 NATURAL, 150 NATURAL LEFT, 150 NATURAL RIGHT, 150 optional specifications, 150 RIGHT, 150 types, 150 JOIN ON join, description of, 150 JULIANTIMESTAMP function examples of, 358 syntax diagram of, 358 K Keys clustering, 232 index, 2
PAGE 478
examples of, 195 materialized aggregate view, definition of, 62 materialized join view definition of, 62 restrictions for, 64 materialized view single delta, 63 using other materialized views, 63 materialized views authorization, 62 clustering, 63 types of, 62 materialized views and indexes, 63 materialized views, restrictions, 64 Math functions summary of, 292 ABS, 296 ACOS, 297 ASIN, 300 ATAN, 301 ATAN2, 302 CEILING, 311 COS, 319 COSH, 320 DEGREES, 344 EXP, 349 FLOOR, 354 LOG, 363 LOG10, 364 MOD, 375 PI,
PAGE 479
NOT NULL constraint, 203 NULL predicate examples of, 252 syntax diagram of, 251 Null symbol, 240 NULL, using, 180 NULLIF function example of, 388 syntax diagram of, 388 NULLIFZERO function examples of, 389 syntax diagram of, 389 NUMERIC data type, 215 Numeric data types approximate numeric, 215 exact numeric, 215 literals, examples of, 239 Numeric literals approximate, 238 exact, 238 examples of, 239 Numeric value expression evaluation order, 227 examples of, 229 syntax diagram of, 227 NVL function examples
PAGE 480
INSERT statement use of, 125 SELECT statement use of, 148 syntax diagram of, 88, 124 Query specification SELECT statement use of, 151 simple table, form of , 151 Quick reference, 457 R RADIANS function examples of, 397 syntax diagram of, 397 RANK function syntax diagram of, 398 RANK window function examples of, 452 syntax diagram of, 452 READ COMMITTED, 30 READ UNCOMMITTED, 30 REAL data type, 216 RECOMPUTE materialized views, definition of, 62 reflexive updates, 134 RENAME TO clause, 48 renaming tables, 48
PAGE 481
examples of, 272 SELECT statement use of, 270 syntax diagram of, 270 Sampling, clusters, 271 Scale, 228 Schemas, description of, 257 Search condition Boolean operators within, 258 CASE expression use of, 306 DELETE statement use of, 92 description of, 260 examples of, 259 predicate within, 258 syntax diagram of, 258 UPDATE statement use of, 181 SECOND function examples of, 414 syntax diagram of, 414 SELECT statement access options, 153 authorization requirements, 154 DISTINCT clause , 147 embedded delete, 1
PAGE 482
examples of, 419 syntax diagram of, 419 Standards ANSI SQL, 465 character set support, 470 ISO, 465 Statement atomicity automatic, 32 description of, 32 Statements, SQL ANSI compliant, 37 Neoview SQL extensions, 38 Statistics clearing, 185 UPDATE STATISTICS statement considerations, 188 examples of, 189 STDDEV function DISTINCT clause within, 420 examples of, 421 statistical definition of, 420 syntax diagram of, 420 STDDEV window function syntax diagram of, 453 Stored procedure statements, 43 CALL, 53 GRANT
PAGE 483
TRANSPOSE clause cardinality of result, 283 degree of result, 282 examples of, 283 SELECT statement use of, 281 syntax diagram of, 281 Trigger statements, 43 Triggers ALTER TRIGGER statement, 50 considerations, 83 CREATE TRIGGER statement, 82 description, 263 DROP TRIGGER statement, 101 TRIM function examples of, 429 syntax diagram of, 429 U UCASE function examples of, 436 syntax diagram of, 430 Union operation associative, UNION ALL, 158 columns, characteristics of, 157 ORDER BY clause restriction, 158 SE
PAGE 484