Neoview SQL Reference Manual (R2.2)

ManualsBrandsHP ManualsSoftwareHP Neoview Release 2.2 Software

HP Neoview SQL Reference Manual

HP Part Number: 544582–001

Published: August 2007

Edition: HP Neoview Release 2.2

Summary of content (462 pages)

PAGE 1
HP Neoview SQL Reference Manual HP Part Number: 544582–001 Published: August 2007 Edition: HP Neoview Release 2.
PAGE 2
© Copyright 2007 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
Restrictions on Triggers..............................................................................................................84 Recompilation and Triggers.......................................................................................................84 Triggers and Primary Keys........................................................................................................84 Examples of CREATE TRIGGER.....................................................................................
PAGE 7
Syntax Description of DROP VIEW...............................................................................................100 Considerations for DROP VIEW...................................................................................................100 Authorization and Availability Requirements.........................................................................100 Example of DROP VIEW...............................................................................................................
PAGE 8
Examples of LOCK TABLE............................................................................................................128 MERGE INTO Statement....................................................................................................................129 Syntax Description of MERGE INTO............................................................................................129 Considerations for MERGE INTO.................................................................................
PAGE 9
Considerations for UNION...........................................................................................................154 Characteristics of the UNION Columns..................................................................................154 ORDER BY Clause and the UNION Operator.........................................................................155 GROUP BY Clause, HAVING Clause, and the UNION Operator ..........................................155 UNION ALL and Associativity............
PAGE 10
MAINTAIN MVGROUP................................................................................................................184 Examples of MAINTAIN...............................................................................................................184 POPULATE INDEX Utility.................................................................................................................186 Syntax Description of POPULATE INDEX......................................................................
PAGE 11
Examples of Numeric Value Expressions.................................................................................219 Identifiers............................................................................................................................................220 Regular Identifiers.........................................................................................................................220 Delimited Identifiers......................................................................
PAGE 12
Considerations for Search Condition............................................................................................248 Order of Evaluation..................................................................................................................248 Column References...................................................................................................................248 Subqueries..................................................................................................
PAGE 13
ASCII Function....................................................................................................................................289 Example of ASCII..........................................................................................................................289 ASIN Function.....................................................................................................................................290 Examples of ASIN......................................................
PAGE 14
Operands of the Expression.....................................................................................................310 Nulls.........................................................................................................................................310 Examples of COUNT.....................................................................................................................310 CURRENT Function.................................................................................
PAGE 15
Examples of DIFF2.........................................................................................................................336 EXP Function.......................................................................................................................................338 Examples of EXP............................................................................................................................338 EXPLAIN Function............................................................
PAGE 16
Example of MOD...........................................................................................................................364 MONTH Function...............................................................................................................................365 Example of MONTH......................................................................................................................365 MONTHNAME Function......................................................................
PAGE 17
Examples of RUNNINGSTDDEV..................................................................................................389 REPEAT Function................................................................................................................................390 Example of REPEAT......................................................................................................................390 REPLACE Function...................................................................................
PAGE 18
STDDEV Function...............................................................................................................................409 Considerations for STDDEV..........................................................................................................409 Definition of STDDEV..............................................................................................................409 Data Type of the Result..........................................................................
PAGE 19
A Quick Reference........................................................................................................435 B Reserved Words.........................................................................................................437 Reserved Neoview SQL Identifiers ....................................................................................................437 C Limits....................................................................................................................
PAGE 20
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.........................................................33 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 Script. For information on Neoview Script, see the Neoview Script Guide.
PAGE 24
Section New or Changed Information “POPULATE INDEX Utility” (page 186) Updated to include ONLINE | OFFLINE options. “REVOKE Statement” (page 134) Updated to include schemas. “REVOKE EXECUTE Statement” (page 136) New statement. “REVOKE SCHEMA Statement” (page 138) New statement. “Considerations for SELECT” (page 151) New consideration added. “SELECT Statement” (page 141) GROUP BY clause enhanced to allow expressions. joined-table syntax is extended to support full outer join.
PAGE 25
myfile.sh Bold Text Bold text in an example indicates user input typed at the terminal. For example: ENTER RUN CODE ?123 CODE RECEIVED: 123.00 The user must press the Return key after typing the input. [ ] Brackets Brackets enclose optional syntax items. For example: DATETIME [start-field TO] end-field A group of items enclosed in brackets is a list from which you can choose one item or none.
PAGE 26
Punctuation Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. For example: DAY (datetime-expression) @script-file Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example: "{" module-name [, module-name]... "}" Item Spacing Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma.
PAGE 27
• • Neoview Script Online Help Command-line help that describes the interface commands supported in the current operating mode of Neoview Script. Neoview Query Guide Information about reviewing query execution plans and investigating query performance of Neoview databases. Visual Query Planner An application that allows you to graphically display query execution plans generated by the Neoview optimizer.
PAGE 28
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
User-Defined and System-Defined Transactions User-Defined Transactions Transactions you define are called user-defined transactions. To ensure that a sequence of statements executes successfully or not at all, you can define one transaction consisting of these statements by using the BEGIN WORK Statement and COMMIT WORK Statement. You can abort a transaction by using the ROLLBACK WORK Statement. System-Defined Transactions In some cases, Neoview SQL defines transactions for you.
PAGE 34
Table 1-2 Concurrent DDL/Utility and DML Operations DML Operation in Progress DDL Operations You Can Start SELECT UNCOMMITTED SELECT SHARE SELECT EXCLUSIVE UPDATE/ INSERT/ DELETE ALTER TABLE attributes Allowed1 Allowed Allowed Allowed ALTER TABLE other Allowed1 Waits Waits Waits CREATE INDEX with Allowed1 POPULATE Allowed Waits Waits CREATE INDEX NO Allowed POPULATE Allowed Allowed Allowed CREATE TRIGGER subject table Allowed Allowed Waits Waits CREATE TRIGGER referenced table A
PAGE 35
Table 1-3 Concurrent DML and DDL Operations (continued) DML Operations You Can Start REVOKE Allowed1 Allowed Waits Waits UPDATE STATISTICS Allowed Allowed Allowed Allowed2 1 2 DDL operation aborts the DML operation. Allowed except during commit phase.
PAGE 36
the isolation level is SERIALIZABLE. If you want to override this automatic choice, the access mode can be set to READ WRITE using a SET TRANSACTION statement. If AUTOCOMMIT is OFF, select statements will execute under a READ WRITE transaction by default. If you want to execute them under a READ ONLY transaction, a SET TRANSACTION must be issued. READ WRITE If a transaction is executing with the READ WRITE access mode, statements within the transaction can read, insert, delete, or update data in tables.
PAGE 37
Transaction Rollback Mode The Rollback Mode for a transaction can be set to either ON or OFF. A setting of ON denotes that the system will rollback the effects of a transaction it aborts. A setting of OFF denotes that the system does not need to perform rollback on abort, rather you are responsible for continuing after the abort. The default is OFF.
PAGE 38
• • • • • • 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
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). In this example, the SQLCODE value is 1000.
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 Script. For information on Neoview Script, see the Neoview Script Guide.
PAGE 42
“DROP VIEW Statement” (page 100) Drops a view. “GRANT Statement” (page 115) Grants access privileges for a table or view to specified roles. “GRANT EXECUTE Statement” (page 117) Grants privileges for executing a stored procedure in Java (SPJ) to specified roles. “GRANT SCHEMA Statement” (page 119) Grants access privileges for a schema to specified roles. “REVOKE Statement” (page 134) 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 164) 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 163) 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 196). 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 254). ALTER MVGROUP mv-group-name {ADD mv-name [, mv-name] ...
PAGE 46
ALTER SYNONYM Statement • • • • “Syntax Description of ALTER SYNONYM ” “Considerations” “Versioning 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.
PAGE 47
ALTER TABLE Statement • • • “Syntax Description of ALTER TABLE” “Considerations for ALTER TABLE” “Example of ALTER TABLE”\ The ALTER TABLE statement adds a column to a Neoview SQL table. See “Database Object Names” (page 196).
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 196).
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 196). 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 196). 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. For an IN parameter argument, use one of these SQL expressions: • • • • • • Literal SQL function (including CASE and CAST expressions) Arithmetic or concatenation operation Scalar subquery Dynamic parameter (for example, ?) in an application Named (for example, ?param) or unnamed (for example, ?) parameter in Neoview Script For an INOUT parameter argument, you can use only a dynamic, named, or unnamed parameter.
PAGE 55
---------- -------------- --------------- ---------- ------------------ 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. Order/Num ---------- Part/Num -------- Unit/Price ------------ Qty/Ord ---------- Part Description ------------------ 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 196). 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
The INSERT privilege is used to authorize the incremental REFRESH. You must have INSERT privileges on a materialized view to be able to perform an incremental REFRESH on it. You must have full access (SELECT, INSERT, and DELETE privileges) to the materialized view to perform either initialize or recompute on the materialized view. The INSERT and DELETE privileges on a materialized view can be granted by the creator of the materialized view or any user that has a grant option.
PAGE 64
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 254)and “MAINTAIN MVGROUP” (page 184). CREATE MVGROUP mv-group-name mv-group-name specifies the materialized view group name to create.
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 247). 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” “Versioning 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.
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
attributes of a SELECT query and populates the table using the data returned by the SELECT query. See “Database Object Names” (page 196). CREATE [VOLATILE] TABLE table (table-element [,table-element]...) | table-spec | like-spec } [NO PARTITION | HASH PARTITION BY (partitioning-column, partitioning-column...)] [MAX TABLE SIZE megabytes] [DISK POOL number] [ATTRIBUTE {NO INSERTLOG | INSERTLOG}] [{ALIGNED | PACKED}] FORMAT AS select-query table-spec is: (table-element [,table-element]...
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
disk at the time of table creation. When a partitioned table is created, the table is automatically partitioned across all the disk volumes on the system. • For a non-partitioned table, the space allocated after you enter the first row is 100 MB. DISK POOLpool_number causes the table to be created in the specified pool number. The value of pool_number is an unsigned integer. Zero (0) is not a valid pool number.
PAGE 73
The PRIMARY KEY value in each row of the table must be unique within the table. Columns within a PRIMARY KEY cannot contain nulls. A PRIMARY KEY defined for a set of columns implies that the column values are unique and not null. You can specify PRIMARY KEY only once on any CREATE TABLE statement. Neoview SQL uses the primary key as the clustering key of the table in order to avoid creating a separate, unique index to implement the primary key constraint.
PAGE 74
Considerations for CREATE VOLATILE TABLE • • • • • • Volatile temporary tables are very similar to persistent tables, except that the life span of the table is limited to the session that created the table. Once the session ends, the tables are automatically dropped. Volatile temporary table are closely linked to the session.
PAGE 75
Tables and Triggers If you plan to create triggers on a table, its primary key length cannot exceed 2032 bytes. A table which will not have triggers can have a primary key of 2048 bytes. For details about this limit, see “Triggers and Primary Keys” (page 84). Calculating Row Size The row size required for Neoview SQL tables is dependent on the number of variable length columns and number of columns that can be null. The following table has four nullable columns and one variable length column.
PAGE 76
RULES • • • • Only one IDENTITY column can be used in a table. Values for the IDENTITY column are generated by default. If you specify a value for the IDENTITY column, the system uses that value and does not generate a unique value for that row. The IDENTITY column must have a NOT NULL constraint. If not specified, the system will implicitly add the constraint. The IDENTITY column property is retained on the target table for CREATE TABLE LIKE... statements.
PAGE 77
name CHAR (256) NOT NULL, order_number INT UNSIGNED NOT NULL, primary key (surrogate_key,order_number) ) HASH PARTITION BY(surrogate_key, order_number); create unique index sk_idx on t_id(surrogate_key); • This example shows the IDENTITY column surrogate_key as the partitioning key. Note that for this release, the surrogate key column must have a unique index on it: NOTE: In Neoview SQL, the partitioning key must be a subset of the clustering key.
PAGE 78
• • • By default, tables are assigned to disk pools in a round robin fashion. The default number of partitions created are as many number of disks in a disk pool. A non partitioned table can be created within a disk pool using the NO PARTITION clause. Restrictions for DISK POOL DISK POOL cannot be used with volatile tables, materialized views, indexes, and CREATE TABLE LIKE. Examples of CREATE TABLE • This example creates a table. The clustering key is the primary key. CREATE TABLE SALES.
PAGE 79
• A row is not returned in this example. Constant ‘A’ is case sensitive, whereas column ‘a’ is insensitive. SELECT * FROM T WHERE a = ‘A’; • The row is returned in this example. Both sides are case sensitive. SELECT * FROM T WHERE a = ‘A’ (not casesensitive); • A row is not returned in this example. A case sensitive comparison is done since column ‘b’ is case sensitive. SELECT * FROM T WHERE b = ‘A’; • A row is not returned in this example.
PAGE 80
create table t(a int, b) as select c,d from t1 An error is returned. This section shows the file attributes, such as partitioning information and clustering keys, which can be specified for the table being created. All the file options that can be specified during a regular CREATE statement can be specified during a CREATE TABLE AS statement. • If table-attributes are not specified, the table is created as a single partitioned table.
PAGE 81
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 82
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 83
1. 2. 3. BEFORE triggered statements Triggering statement AFTER triggered statements Execution of a statement is considered to be complete only when all cascaded triggers are complete. 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).
PAGE 84
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 85
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 86
record.vital_id), (SELECT record.id FROM record WHERE sample.id = record.
PAGE 87
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 254). CREATE VIEW view [(column-name ] [,column-name ...
PAGE 88
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 89
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 90
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 91
STREAM (table) deletes a continuous data stream from the specified table. You cannot specify stream access for the DELETE statement if it is not embedded as a table reference in a SELECT statement. See “SELECT Statement” (page 141). [AFTER LAST ROW] causes the stream to skip all existing rows in the table and delete only rows that are published after the statement is issued. SET ON ROLLBACK set-roll-clause [,set-roll-clause]...
PAGE 92
Considerations for DELETE Authorization Requirements DELETE requires authority to read and write to the table or view being deleted from and authority to read tables or views specified in subqueries used in the search condition. Transaction Initiation and Termination The DELETE statement automatically initiates a transaction if no transaction is active. Otherwise, you can explicitly initiate a transaction with the BEGIN WORK statement.
PAGE 93
DELETE FROM sales.orders WHERE salesrep = 220 AND custnum <> 1234; --- 2 row(s) deleted. • Remove all suppliers not in Texas from the table PARTSUPP: DELETE FROM invent.partsupp WHERE suppnum IN (SELECT suppnum FROM samdbcat.invent.supplier WHERE state <> 'TEXAS'); --- 41 row(s) deleted. This statement achieves the same result: DELETE FROM invent.partsupp WHERE suppnum NOT IN (SELECT suppnum FROM samdbcat.invent.supplier WHERE state = 'TEXAS'); --- 41 row(s) deleted.
PAGE 94
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 95
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 254). 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 96
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 247). DROP SCHEMA schema Syntax Description of DROP SCHEMA schema is the name of the schema to drop. Considerations for DROP SCHEMA Authorization and Availability Requirements To drop a schema, you must own the schema.
PAGE 97
DROP SYNONYM Statement • • • • “Syntax Description of DROP SYNONYM ” “Considerations” “Versioning 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 98
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 196). DROP [VOLATILE] TABLE table Syntax Description of DROP TABLE table is the name of the table to delete.
PAGE 99
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 100
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 254). DROP VIEW view Syntax Description of DROP VIEW view is the name of the view to drop. Considerations for DROP VIEW Authorization and Availability Requirements To drop a view, you must own the schema that contains the view. Example of DROP VIEW • This example drops a view: DROP VIEW mysch.
PAGE 101
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 220).
PAGE 102
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 Script 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 103
--- 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 104
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 Neoview Script, see the HP Neoview Script Guide For more information on the EXPLAIN function, see “EXPLAIN Function” (page 339). NOTE: Formatting changes to the EXPLAIN output data might occur in the future. If so, you might need to modify any scripts that parse the output of the command.
PAGE 105
Considerations • • • • • • “Case Considerations” (page 105) “Number Considerations” (page 105) “Formatted [OPTIONS 'f'] Considerations” (page 105) “Normal User [OPTIONS 'n'] Considerations” (page 106) “Expert User [OPTIONS 'e'] Considerations” (page 108) “Machine-Readable [OPTIONS 'm'] Considerations” (page 110) 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 106
PREPARE q FROM SELECT * FROM REGION; EXPLAIN options 'f' q; The FILE_SCAN operator is the only operator in this example that shows additional information in the OPT and DESCRIPTION fields. 'fs' indicates that fast-scan optimization was used. 'fr' indicates that fast-reply data-move optimization was used. The table name is shown in the DESCRIPTION field. For information about these special optimization techniques, see the listing of each operator in the Neoview Query Guide.
PAGE 107
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 108
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 109
------------------------------------------------------------------ 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 110
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 111
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 112
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 113
SQLGetCursorName: In: StatementHandle = 0x003B1A10, CursorName = 0x001577E8, BufferLength = 300, NameLengthPtr = 0x001552C0 Return: SQL_SUCCESS=0 Out: *CursorName = "SQL_CUR_3", *NameLengthPtr = 9 SQLAllocHandle: In: HandleType = SQL_HANDLE_STMT=3, InputHandle = 0x003B1908, OutputHandlePtr = 0x00157F68 Return: SQL_SUCCESS=0 Out: *OutputHandlePtr = 0x003B2FF0 SQLExecDirect: In: Statementhandle = 0x003B2FF0, StatementText = "explain options 'f' SQL_CUR_3", Statementlength = 29 Return: SQL_SUCCESS=0 Get Data A
PAGE 114
rowNo = 0; while (rs.next()) { rowNo++; for (int j=1; j <= rsMD.getColumnCount(); j++) { System.out.println(rs.getObject(j)); } } System.out.println(""); System.out.println("--- SQL operation complete"); rs.close(); } connection.close(); } 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.
PAGE 115
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 | PUBLIC privilege is: DELETE | INSERT | SELECT | UPDATE [(column [,column]...
PAGE 116
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. That is, the privilege must have been issued to you implicitly (by being the owner of an object) or explicitly by the WITH GRANT OPTION and not revoked (owner privileges can never be revoked).
PAGE 117
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 118
Examples of GRANT EXECUTE • The SPJ owner grants 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 EXECUTE privilege on the ADJUSTSALARY procedure to other roles: GRANT EXECUTE ON PROCEDURE persnl.adjustsalary TO 'ROLE.PAYROLL', 'ROLE.FINANCE'; • The SPJ owner grants EXECUTE privilege on the TOTALPRICE procedure to all roles: GRANT EXECUTE ON sales.
PAGE 119
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 {grantee [,grantee ]... } [WITH GRANT OPTION] privilege is: EXECUTE | DELETE | INSERT | UPDATE | REFERENCES | SELECT grantee is: authid | PUBLIC Syntax Description of GRANT SCHEMA privilege[, privilege]...
PAGE 120
Considerations for GRANT SCHEMA Authorization and Availability Requirements To grant a privilege on a schema, you must have both that privilege and the right to grant that privilege. That is, the privilege must have been issued to you WITH GRANT OPTION and not revoked. If you lack authority to grant one or more of the specific privileges, the system returns a warning (and does grant any of the specified privileges that you do have authority to grant).
PAGE 121
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 122
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 191). If you do not specify target-col-list, row values from the source table are inserted into all columns in table.
PAGE 123
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 124
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 125
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 126
• 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 127
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 128
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 129
MERGE INTO Statement • • “Syntax Description of MERGE INTO” “Considerations for MERGE INTO” The MERGE INTO statement provides upsert, reflexive updates, and updates from one table into another.. MERGE INTO table [using-clause ] on-clause {[when-matched-clause] [when-not-matched-clause]} using-clause is: USING (select-query) AS derived-table-name [derived-column-names] on-clause is: ON predicate when-matched-clause is: WHEN MATCHED THEN UPDATE SET set-clause set-clause is: SET ...
PAGE 130
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. This statement is not allowed: MERGE INTO t ON a > 0 ... • The key value specified in the on-clause and the VALUE clause must be the same. This statement is not allowed: MERGE INTO t ON a = 10 WHEN NOT MATCHED THEN INSERT VALUES (20, 30) • on-clause cannot contain a subquery.
PAGE 131
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 ON a = z.a USING (SELECT * FROM t1) z(a,b) WHEN MATCHED THEN UPDATE SET b = z.b WHEN NOT MATCHED THEN INSERT VALUES (z.a, z.
PAGE 132
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 Script session. You can also use PREPARE to check the syntax of a statement without executing the statement in a Neoview Script session.
PAGE 133
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 101).
PAGE 134
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 135
grantee is authid | PUBLIC 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 136
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 137
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 creator of the stored procedure). You can revoke the PUBLIC privilege but you cannot revoke an individual privilege such as SELECT.
PAGE 138
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 140). REVOKE [GRANT OPTION FOR] {privilege [,privilege ] ...| ALL [PRIVILEGES ]} ON SCHEMA schema-name FROM {grantee [,grantee]...
PAGE 139
grantee is authid | PUBLIC authid specifies an authorization ID from which you revoke privileges. Authorization IDs identify roles during the processing of SQL statements. The authorization ID must be a valid role name, enclosed in double quotes. authid is not case-sensitive. SQL:1999 specifies two special authorization IDs: PUBLIC and SYSTEM. • • PUBLIC specifies all present and future authorization IDs. SYSTEM specifies the implicit grantor of privileges to the creators of objects.
PAGE 140
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 141
SELECT Statement • • • • • • • • “Syntax Description of SELECT” “Considerations for SELECT” “Considerations for Select List” “Considerations for SEQUENCE BY” “Considerations for GROUP BY” “Considerations for ORDER BY” “Considerations for UNION” “Examples of SELECT” SELECT Statement 141
PAGE 142
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 143
select-list is: * | select-sublist [,select-sublist]... select-sublist is: corr.*) | [corr.
PAGE 144
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 145
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 196). If you specify only one table-ref, the intermediate result table consists of rows derived from that table reference.
PAGE 146
[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 147
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 148
[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 149
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 150
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 151
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 152
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. Queries with joins that involve a larger number of tables are not guaranteed to compile. Object Names in SELECT You can use fully qualified names only in the FROM clause of a SELECT statement.
PAGE 153
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 154
• • • • 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 155
(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 156
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 157
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 158
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 159
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 160
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 161
(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 162
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 163
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 247). default-schema-name is an SQL identifier. For example, you can use MYSCHEMA or myschema or a delimited identifier "my schema".
PAGE 164
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 165
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 166
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 167
access-mode specifies the type of data access that the transaction requires, depending on whether the transaction changes the database. If the isolation-level is READ UNCOMMITTED, you cannot specify READ WRITE. The default access-mode is READ WRITE. See “Transaction Access Modes” (page 35). diagnostics-size specifies the size of the diagnostics area (as an estimate of the number of expected conditions) used to return SQL query completion and exception condition information.
PAGE 168
The exceptions (statements that are not transaction initiating) are: • • • COMMIT, FETCH, ROLLBACK, and SET TRANSACTION DML statements executing under READ UNCOMMITTED access EXECUTE or EXECUTE IMMEDIATE, which are transaction initiating only if the associated statement is transaction-initiating Explicit Transactions You can issue an explicit BEGIN WORK even if the autocommit option is on. The autocommit option is temporarily disabled until you explicitly issue COMMIT or ROLLBACK.
PAGE 169
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 170
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 141). [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 171
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 248). 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 172
Requirements for Data in Row Each row to be updated must satisfy the constraints of the table or underlying base table of the view. No column updates can occur unless all of these constraints are satisfied. (A table constraint is satisfied if the check condition is not false—that is, it is either true or has an unknown value.) In addition, a candidate row from a view created with the WITH CHECK OPTION must satisfy the view selection criteria.
PAGE 173
• More than one subquery is not allowed if multiple-column syntax is used. UPDATE t SET (a,b)=(SELECT x,y FROM z), (c,d)=(SELECT x,y FROM a)) • If a subquery is used, it must return at most one row. SET ON ROLLBACK Considerations The SET ON ROLLBACK expression is evaluated when each row is processed during execution of the UPDATE statement. The results of the evaluation are applied when and if the transaction is rolled back.
PAGE 174
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --- SQL operation complete. BEGIN WORK; --- SQL operation complete. DELETE FROM persnl.employee WHERE empnum = 23; --- 1 row(s) deleted. INSERT INTO persnl.
PAGE 175
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 176
column-list | EVERY COLUMN [, column-list] | EVERY KEY [, column-list] | EXISTING 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. Single-column group: column-name | (column-name) | column-name TO column-name | (column-name) TO (column-name) are the ways you can specify individual columns or a group of individual columns. To generate statistics for individual columns, list each column.
PAGE 177
consider a column ‘CITY’ in table SALES, which stores the city code where the item was sold, where number of cities in the sales data is 1538. Setting the number of intervals to a number greater or equal to the number of cities (that is, setting the number of intervals to 1600) guarantees that the generated histogram captures the number of rows for each city. If the specified value n exceeds the number of unique values in the column, the system generates only as many intervals as there are unique values.
PAGE 178
Generating and Clearing Statistics for Columns To generate statistics for particular columns, name each column, or name the first and last columns of a sequence of columns in the table. For example, suppose that a table has consecutive columns CITY, STATE, ZIP.
PAGE 179
--- SQL operation complete. • Suppose that a construction company has an ADDRESS table of potential sites and a DEMOLITION_SITES table that contains some of the columns of the ADDRESS table. The primary key is ZIP. Join these two tables on two of the columns in common: SELECT COUNT(AD.number), AD.street, AD.city, AD.zip, AD.state FROM address AD, demolition_sites DS WHERE AD.zip = DS.zip AND AD.type = DS.type GROUP BY AD.street, AD.city, AD.zip, AD.
PAGE 180
PAGE 181
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 182) Performs one or more maintenance tasks, such as REORG, UPDATE STATISTICS, and REFRESH, on a specified database object. “POPULATE INDEX Utility” (page 186) Loads indexes.
PAGE 182
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 183
REORG MVS reorganizes all materialized views on the specified table. task options optional string containing options specific to the individual tasks that would be passed on to that particular command at run time. 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.
PAGE 184
MAINTAIN MVGROUP MAINTAIN MVGROUP mvgroupname, REFRESH mv-refresh-option mv-refresh-option is: [RECOMPUTE | CANCEL] [{WITH | WITHOUT |DO ONLY} MVLOG CLEANUP mvgroupname is the ANSI name of the materialized view group to be maintained. It can be a one or two-part name. The REFRESH utility can be applied to both incremental MVs and recomputed MVs. The REFRESH utility optimizes the refresh of MVGROUP.
PAGE 185
maintain table t1_090, all, display detail; • Disables all tasks that are allowed to be disabled (reorg table, index, update statistics): maintain table t1_090, all, disable; The status will show up as Disabled for the tasks that were disabled by the previous command.
PAGE 186
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 Script 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 187
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 196). Considerations for PURGEDATA • • • • • • • • You must have ALL privileges on the table.
PAGE 188
PAGE 189
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 190
Character Sets The character set is automatically set to UCS2. UCS2 is a double-byte Unicode character set in UTF16 big-endian encoding. All Basic Multilingual Plan (BMP) characters are included. Surrogate characters are treated as two double-byte characters. Neoview SQL supports the Unicode 2.1 standard. This means that you get all the European languages, as well as Hebrew, Arabic, and Cyrillic (Russian) encodings.
PAGE 191
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 192
SELECT AVG (salary) AS "AVERAGE SALARY" FROM persnl.employee; "AVERAGE SALARY" ---------------49441.52 --- 1 row(s) selected.
PAGE 193
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 194
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 195
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” “SQL Tables” “Tables” “Views” Ownership In Neoview SQL, the creator of a schema owns all the objects defined in the schema.
PAGE 196
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 195). 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 197
Data Types Neoview SQL data types are character, datetime, interval, or numeric (exact or approximate): “Character String Data Types” (page 200) Fixed-length and variable-length character data types. “Examples of SET TABLE TIMEOUT” (page 165) DATE, TIME, and TIMESTAMP data types. “Interval Data Types” (page 204) Year-month intervals (years and months) and day-time intervals (days, hours, minutes, seconds, and fractions of a second).
PAGE 198
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 199
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 200
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 201
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 202
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 203
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 204
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 205
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 206
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 207
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 208
Expressions An SQL value expression, referred to as an expression, can evaluate to a value with one of these : “Character Value Expressions” (page 208) Operands can be combined with the concatenation operator (||). Example: 'HOUSTON,' ||' TEXAS' “Datetime Value Expressions” (page 210) Operands can be combined in specific ways with arithmetic operators.
PAGE 209
'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 210
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 211
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 212
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 213
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 214
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 215
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 216
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 217
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 218
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 219
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 220
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 221
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 222
Keys Neoview SQL supports these types of keys: • • • • “Clustering Keys” “Index Keys” “Primary Keys” “Literals” 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 223
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 223) A series of characters enclosed in single quotes. Example: 'Planning' “Datetime Literals” (page 225) Begins with keyword DATE, TIME, or TIMESTAMP and followed by a character string.
PAGE 224
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. space is space sequences that can be added before or after hex-code-value for readability. The encoding for space must be the TERMINAL_CHARSET for an interactive interface and the SQL module character set for the programmatic interface.
PAGE 225
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 226
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 227
'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 228
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 229
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 230
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 231
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 232
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 233
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 233) Determines whether a sequence of values is within a range of sequences of values.
PAGE 234
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 235
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 236
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 237
(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 238
... --- 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 239
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 240
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 241
------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 242
Note that the rvc IS NOT NULL predicate is not equivalent to NOT rvc IS NULL.
PAGE 243
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 250). 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 244
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 245
Privileges These privileges are used by the GRANT and REVOKE statements. For further information on GRANT, see “GRANT Statement” (page 115), “GRANT EXECUTE Statement” (page 117), or “GRANT SCHEMA Statement” (page 119). For further information on REVOKE, see “REVOKE Statement” (page 134), “REVOKE EXECUTE Statement” (page 136), or “REVOKE SCHEMA Statement” (page 138).
PAGE 246
General Rules for column-list Names the columns to which the SELECT, UPDATE, or REFERENCES privileges apply. If you specify SELECT, UPDATE or REFERENCES without column names, the privileges apply to all columns of the table or view (regular or materialized).
PAGE 247
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 248
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 249
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 250
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 141). 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 251
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 252
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 253
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 82). See also “CREATE TRIGGER Statement” (page 81), “ALTER TRIGGER Statement” (page 50), “DROP TRIGGER Statement” (page 99).
PAGE 254
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 255
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 256
PAGE 257
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 258
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 259
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 260
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 141). SAMPLE is a Neoview SQL extension.
PAGE 261
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 261). BALANCE If you specify a BALANCE expression, Neoview SQL performs stratified sampling.
PAGE 262
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 263
--- 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 264
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 265
--- 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 266
• 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 267
--- 30 row(s) selected.
PAGE 268
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 141). SEQUENCE BY is a Neoview SQL extension. SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] | DESC[ENDING]]]...
PAGE 269
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 270
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 271
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 272
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 273
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 274
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 275
(EXPR) -------------------2 2 2 --- 3 row(s) selected. • This query shows how multiple TRANSPOSE clauses can be used in the same query.
PAGE 276
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 277
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 277
PAGE 278
PAGE 279
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 280
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: 280 “ASCII Function” (page 289) Returns the ASCII code value of the first character of a ISO88591 character value expression.
PAGE 281
“TRIM Function” (page 418) Removes leading or trailing characters from a character string. “UCASE Function” (page 419) Upshifts single-byte characters. You can also use UPSHIFT or UPPER. “UPPER Function” (page 427) Upshifts single-byte characters. You can also use UPSHIFT or UCASE. “UPSHIFT Function” (page 428) Upshifts single-byte characters. You can also use UPPER or UCASE. See the individual entry for the function.
PAGE 282
“DAYOFYEAR Function” (page 329) Returns an integer value in the range 1 through 366 that represents the corresponding day of the year. “EXTRACT Function” (page 342) Returns a specified datetime field from a datetime value expression or an interval value expression. “HOUR Function” (page 344) Returns an integer value in the range 0 through 23 that represents the corresponding hour of the day. “JULIANTIMESTAMP Function” (page 347) Converts a datetime value to a Julian timestamp.
PAGE 283
“LOG10 Function” (page 353) Returns the base 10 logarithm of a numeric value expression. “MOD Function” (page 364) Returns the remainder (modulus) of an integer value expression divided by an integer value expression. “NULLIFZERO Function” (page 378) Returns the value of the operand unless it is zero, in which case it returns NULL. “PI Function” (page 382) Returns the constant value of pi as a floating-point value.
PAGE 284
“MOVINGCOUNT Function” (page 368) Returns the number of nonnull values of a column expression in the current window. “MOVINGMAX Function” (page 370) Returns the maximum of nonnull values of a column expression in the current window. “MOVINGMIN Function” (page 371) Returns the minimum of nonnull values of a column expression in the current window. “MOVINGSTDDEV Function” (page 372) Returns the standard deviation of nonnull values of a column expression in the current window.
PAGE 285
Other Functions and Expressions Use these other functions and expressions in an SQL value expression: “CASE (Conditional) Expression” (page 295) A conditional expression. The two forms of the CASE expression are simple and searched. “CAST Expression” (page 298) Converts a value from one data type to another data type that you specify.
PAGE 286
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 217).
PAGE 287
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 217).. Examples of ACOS • This function returns the value 3.49044274380724352E-001 or approximately 0.
PAGE 288
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 210).
PAGE 289
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 208).
PAGE 290
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 217). Examples of ASIN • This function returns the value 3.49044414403046464E-001 or approximately 0.
PAGE 291
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 217). Examples of ATAN • This function returns the value 8.72766423249958400E-001 or approximately 0.8727 in radians (which is 50 degrees): ATAN (1.
PAGE 292
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 217). Example of ATAN2 This function returns the value 2.
PAGE 293
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 294
--- 1 row(s) selected. • Return the average salary by department: SELECT deptnum, AVG (salary) AS "AVERAGE SALARY" FROM persnl.employee WHERE deptnum < 3000 GROUP BY deptnum; Dept/Num -------1000 2000 1500 2500 "AVERAGE SALARY" --------------------52000.17 50000.10 41250.00 37000.00 --- 4 row(s) selected.
PAGE 295
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 296
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 297
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 298
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 299
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 300
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 217). Example of CEILING This function returns the integer value 3.
PAGE 301
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 302
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 208).
PAGE 303
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 304
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 305
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 306
VALUES (002, 'Executed at ' || CAST (CURRENT_TIMESTAMP AS CHAR(26))); 306 SQL Functions and Expressions
PAGE 307
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 308
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 217). 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 309
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 217). Example of COSH This function returns the value 1.88842387716101616E+000, or approximately 1.8884, the hyperbolic cosine of 1.
PAGE 310
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 311
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 312
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 316). 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 313
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 314
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 315
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 316
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 312). 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 317
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 429). Example of CURRENT_USER This example retrieves the user name value for the current user SELECT CURRENT_USER FROM logfile; (EXPR) ----------------------DCS.TSHAW ...
PAGE 318
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 279). 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 319
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 279). 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 320
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 217). datetime_expr is an expression that evaluates to a datetime value of type DATE or TIMESTAMP.
PAGE 321
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 322
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 210). DEFAULT | USA | EUROPEAN specifies a format for a datetime value.
PAGE 323
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 324
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 325
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 210).
PAGE 326
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 210).
PAGE 327
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 210).
PAGE 328
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 210).
PAGE 329
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 210).
PAGE 330
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 331
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 332
This statement is equivalent and will not produce an error: SELECT decode( (?p1 || ?p2), trim(?p1), ‘Hi’ ) from emp; 332 SQL Functions and Expressions
PAGE 333
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 217). Examples of DEGREES • This function returns the value 45 in degrees: DEGREES (0.78540) • This function returns the value 45.
PAGE 334
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 268). 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 335
--- 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 336
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 268). 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 337
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 334).
PAGE 338
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 217). 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 339
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 339). 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 340
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 341
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 342
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 210) and “Interval Value Expressions” (page 213).
PAGE 343
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 217). Examples of FLOOR This function returns the integer value 2.00000000000000040E+000, represented as a FLOAT data type: FLOOR (2.
PAGE 344
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 210).
PAGE 345
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 346
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 347
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 348
SELECT LASTNOTNULL (I1) AS LASTNOTNULL FROM mining.seqfcn SEQUENCE BY TS; LASTNOTNULL ----------6215 6215 19058 19058 11966 --- 5 row(s) selected.
PAGE 349
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 350
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 208).
PAGE 351
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 352
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 217). Example of LOG This function returns the value 6.93147180559945504E-001, or approximately 0.69315: LOG (2.
PAGE 353
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 217). Example of LOG10 This function returns the value 1.39794000867203792E+000, or approximately 1.
PAGE 354
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 355
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) 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 019F
PAGE 356
Table 6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) 356 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
PAGE 357
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) 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 0398
PAGE 358
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 --- 17 row(s) selected. See “UPPER Function” (page 427). 358 SQL Functions and Expressions (EXPR) -----------------...
PAGE 359
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 208). 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 360
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 208). 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 361
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 362
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 363
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 210).
PAGE 364
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 365
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 210).
PAGE 366
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 210).
PAGE 367
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 268). 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 368
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 268). 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 369
--- 5 row(s) selected.
PAGE 370
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 268). 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 371
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 268). 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 372
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 268). 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 373
.000 15527.357 14802.016 15115.012 6036.275 --- 5 row(s) selected.
PAGE 374
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 268). 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 375
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 268). 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 376
241098840.500 219099697.000 228463602.333 36436621.000 --- 5 row(s) selected.
PAGE 377
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 378
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 379
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 380
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 208).
PAGE 381
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 268). 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 382
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 383
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 384
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 217).
PAGE 385
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 210).
PAGE 386
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 217). Examples of RADIANS • Return the value 7.85398150000000160E-001, or approximately 0.78540 in degrees: RADIANS (45) • Return the value 45 in degrees.
PAGE 387
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 388
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 389
----------? ? 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 390
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 391
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 392
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 393
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 394
--- 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 268). ROWS SINCE CHANGED is a Neoview SQL extension.
PAGE 395
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 396
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 397
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 268). RUNNINGMAX is a Neoview SQL extension.
PAGE 398
SELECT RUNNINGMIN (I1) AS MIN_I1 FROM mining.seqfcn SEQUENCE BY TS; MIN_I1 -----------6215 6215 6215 4597 4597 --- 5 row(s) selected.
PAGE 399
RUNNINGRANK Function See “RANK/RUNNINGRANK Function” (page 387).
PAGE 400
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 268). RUNNINGSTDDEV is a Neoview SQL extension. RUNNINGSTDDEV (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 401
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 268). RUNNINGSUM is a Neoview SQL extension. RUNNINGSUM (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 402
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 268). RUNNINGVARIANCE is a Neoview SQL extension. RUNNINGVARIANCE (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 403
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 210).
PAGE 404
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 217).
PAGE 405
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 217). 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 406
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 217). Example of SINH This function returns the value 1.60191908030082600E+000, or approximately 1.6019, the hyperbolic sine of 1.25: SINH (1.
PAGE 407
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 408
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 217). Example of SQRT This function returns the value 5.19615242270663312E+000, or approximately 5.
PAGE 409
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 410
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 411
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 412
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 413
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 414
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 217). 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 415
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 217). Example of TANH • This function returns the value 8.48283639957513088E-001 or approximately 0.8483, the hyperbolic tangent of 1.
PAGE 416
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 392). THIS is a Neoview SQL extension. THIS (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
PAGE 417
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 418
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 419
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 420
Table 6-2 One-to-One UCS2 Mappings (continued) 420 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 05
PAGE 421
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) 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 0555 1E
PAGE 422
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 423
Table 6-3 Two-Character UCS2 Mapping (continued) 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 0x1F09
PAGE 424
Table 6-3 Two-Character UCS2 Mapping (continued) 424 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
PAGE 425
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 426
----------------... Hotel Oregon ------------------... HOTEL OREGON -----------------... hotel oregon --- 17 row(s) selected. See “LCASE Function” (page 349). For more examples of when to use the UCASE function, see “UPSHIFT Function” (page 428).
PAGE 427
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 428
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 429
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 430
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 431
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 432
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 210).
PAGE 433
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 210). 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 434
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 435
A Quick Reference This appendix provides a quick, alphabetic reference to commands, statements, and utilities. For other topics, see the Index.
PAGE 436
P “POPULATE INDEX Utility” (page 186) “PURGEDATA Utility” (page 187) “PREPARE Statement” (page 132) R “REVOKE Statement” (page 134) “REVOKE EXECUTE Statement” (page 136) “REVOKE SCHEMA Statement” (page 138) “ROLLBACK WORK Statement” (page 140) S “SELECT Statement” (page 141) “SET SCHEMA Statement” (page 163) “SET TABLE TIMEOUT Statement” (page 164) “SET TRANSACTION Statement” (page 166) U “UPDATE Statement” (page 169) “UPDATE STATISTICS Statement” (page 175) 436 Quick Reference
PAGE 437
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 438
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 439
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 440
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 441
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 REFERENCE or PRIMARY KEY constraint is 2048 bytes. DROP SCHEMA (check this CASCADE transactions You might need to increase the number of locks per volume, or DROP SCHEMA CASCADE can fail. FROM clause of the SELECT statement Neoview SQL generates good plans up to approximately 40 tables. Beyond that, executor performance is affected.
PAGE 442
PAGE 443
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 444
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 445
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 446
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 447
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 448
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 449
Index A ABS function examples of, 286, 288 syntax diagram of, 286 Access options summary of, 30 DELETE statement use of, 91 DML statements use of, 30 INSERT statement use of, 121 READ COMMITTED, 30 READ UNCOMMITTED , 30 REPEATABLE READ, 31 SELECT statement use of, 150 SERIALIZABLE, 31, 36 SKIP CONFLICT, 31 UPDATE statement use of, 171 Access privileges stored procedures, 117 tables, 115, 119 ACOS function examples of, 287 syntax diagram of, 287, 288 AFTER LAST ROW clause, 145 Aggregate functions summary of,
PAGE 450
syntax diagram of, 300 CHAR data type, 200, 201 CHAR function examples of, 301 syntax diagram of, 301 CHAR VARYING data type, 201 CHAR_LENGTH function examples of, 302 syntax diagram of, 302 Character sets setting default, 223 support standards, 448 Character string data types CHAR and VARCHAR, differences, 201 examples of literals, 224 maximum storage lengths, 201 Character string functions summary of, 280 ASCII, 289 CHAR, 301 CHAR_LENGTH , 302 COALESCE, 303 CODE_VALUE, 304 CONCAT, 305 INSERT, 345 LCASE, 3
PAGE 451
authorization and availability requirements , 59 examples of, 59 limits on indexes, 59, 441 syntax diagram of, 58 CREATE MATERIALIZED VIEW statement considerations for, 62 example of, 64 syntax diagram of, 60 CREATE MVGROUP statement example of, 66 CREATE MVGROUP statement, syntax description of, 66 CREATE SCHEMA statement considerations for, 67 examples of, 67 syntax diagram of, 67 CREATE SYNONYM statement, 68 considerations of, 68 example of, 68 syntax diagram of, 68 versioning considerations, 68 CREATE T
PAGE 452
DECIMAL, 206 descriptions of, 205 INTEGER, 206 LARGEINT, 206 NUMERIC, 205 PICTURE, 206 SMALLINT, 206 fixed length character CHAR, 200 NATIONAL CHAR, 201 NCHAR, 201 PIC, 201 interval, 204 literals, examples of character string literals, 224 datetime literals, 225 interval literals, 228 numeric literals, 229 varying-length character CHAR VARYING, 201 NATIONAL CHAR VARYING, 201 NCHAR VARYING, 201 VARCHAR, 201 Database object names, 196 Database object namespace, 196 Database objects, 195 DATE_ADD function synt
PAGE 453
Derived column names examples of, 191 syntax of, 191 DETAIL_COST in EXPLAIN output CPU_TIME, 111, 340 IDLETIME, 111, 341 IO_TIME, 111, 340 MSG_TIME, 111, 340 PROBES, 111, 341 DIFF1 function equivalent definitions, 334 examples of, 334 syntax diagram of, 334 DIFF2 function equivalent definitions, 336 examples of, 336 syntax diagram of, 336 DISK POOL , 77 considerations for, 77 restrictions for, 78 DISTINCT clause aggregate functions, 152, 279 AVG function use of, 293 COUNT function use of, 310 MAX function u
PAGE 454
displayed, 104 operators, 104 OPTIONS ’e’ considerations, 108 OPTIONS ’f’ considerations, 105 OPTIONS ’m’ considerations, 110 OPTIONS ’n’ considerations, 106 output, 106 reviewing query execution plans, 104 syntax, 104 Expression character (or string) value, 208 datetime value, 210, 211, 214 description of, 208 interval value, 211, 214 numeric value, 217 Extensions reserved words, 437 statements, 38 EXTRACT function examples of, 342 syntax diagram of, 342 F File options CREATE INDEX use of, 58 CREATE TABLE
PAGE 455
limits, 152, 441 NATURAL, 147 NATURAL LEFT, 147 NATURAL RIGHT, 147 optional specifications, 147 RIGHT, 147 types, 147 JOIN ON join, description of, 147 JULIANTIMESTAMP function examples of, 347 syntax diagram of, 347 K Keys clustering, 222 index, 222 primary, 222 L LARGINT data type, 206 LASTNOTNULL function examples of, 347 syntax diagram of, 347 LCASE function examples of, 349 syntax diagram of, 349 LEFT function examples of, 350 syntax diagram of, 350 LEFT join, description of, 147 Limits constraints,
PAGE 456
ASIN, 290 ATAN, 291 ATAN2, 292 CEILING, 300 COS, 308 COSH, 309 DEGREES, 333 EXP, 338 FLOOR, 343 LOG, 352 LOG10, 353 MOD, 364 PI, 382 POWER, 384 RADIANS, 386 SIGN, 404 SIN, 405 SINH, 406 SQRT, 408 TAN, 414 TANH, 415 MAV restrictions for, 64 MAV, definition of, 62 MAX function considerations for, 361 DISTINCT clause within, 361 examples of, 361 syntax diagram of, 361 MAXIMUM function considerations for, 361 DISTINCT clause within, 361 examples of, 361 syntax diagram of, 361 merge from one table to another, 13
PAGE 457
examples of, 219 syntax diagram of, 217 NVL function examples of, 379 syntax diagram of, 379 O Object names, 196 Object namespace;Namespace, 196 Object naming statements, 43 object-privilege, general rules, 245 Objects description of, 196 logical names, 196 OCTET_LENGTH function CHAR_LENGTH similarity to, 380 examples of, 380 syntax diagram of, 380 OFFSET function examples of, 381 syntax diagram of, 381 Operators in query execution plan, 104 OPTIONS ’e’ on Explain statement, 104 OPTIONS ’f’ on Explain stat
PAGE 458
and SERIALIZABLE, 30 description of, 36 REPLACE function examples of, 391 syntax diagram of, 390 REQUESTS_IN, 106 reserved table names, 74 Reserved words Neoview SQL, 437 Resource control, statements for, 42 REVOKE EXECUTE statement authorization and availability requirements, 137 examples of, 137 syntax diagram of, 136 REVOKE SCHEMA statement authorization and availability requirements, 139 examples of, 139 syntax diagram of, 138 REVOKE statement authorization and availability requirements, 135 examples of
PAGE 459
joined table within, 147 lock modes, 150 ORDER BY clause , 151, 154 RETURN list, 145 select list elements, 144 SEQUENCE BY clause, 149 simple table within, 147 SKIP CONFLICT access, 150 stream access limitations, 152 STREAM clause, 145 syntax diagram of, 142 table reference within, 145 TRANSPOSE clause, 148 union operation within, 150, 154 views and, 151 WHERE clause, 148 self-referencing INSERT, 123 SEQUENCE BY clause examples of, 269 SELECT statement use of, 268 syntax diagram of, 268 Sequence functions s
PAGE 460
correlated, 238, 251 description of, 250 inner query, 250 outer query, 250 outer reference, 251 row BETWEEN predicate, 233 comparison predicate, 235 IN predicate, 239 NULL predicate, 241 quantified comparison predicate, 242 scalar BETWEEN predicate , 233 comparison predicate , 235 DELETE statement, 91, 170 IN predicate , 239 NULL predicate, 241 quantified comparison predicate, 242 UPDATE statement, 170 table, 239 SUBSTR function examples of, 412 operand requirements, 411 syntax diagram of, 411 SUBSTRING fun
PAGE 461
table row count, 177 UPPER function examples of, 427 syntax diagram of, 427 upsert using rowsets, 130 using single row, 129 upsert functionality, 129 UPSHIFT function syntax diagram of, 428 USER function example of, 429 syntax diagram of, 429 Utilities privileges required to execute, 181 PURGEDATA, 187 V Value expression, 208 Value expressions summary of, 285 CASE (Conditional) expression, 295 CAST expression, 298 VARCHAR data type, 201 Variable-length character column, 201 VARIANCE function DISTINCT claus
PAGE 462