Page 1 HP Neoview SQL Reference Manual HP Part Number: 544582–001 Published: August 2007 Edition: HP Neoview Release 2.2...
Page 2 The information contained herein is subject to change without notice. The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP shall not be liable for technical or editorial errors or omissions contained herein.
Page 3: Table Of Contents
READ WRITE..........................36 Transaction Isolation Levels......................36 READ UNCOMMITTED......................36 READ COMMITTED........................36 SERIALIZABLE or REPEATABLE READ .................36 Transaction Rollback Mode......................37 ANSI Compliance and Neoview SQL Extensions................37 ANSI-Compliant Statements......................37 Statements That Are Neoview SQL Extensions................38 ANSI-Compliant Functions......................38 Neoview SQL Error Messages......................39 Table of Contents...
Page 4 2 SQL Statements......................41 Categories.............................41 Data Definition Language (DDL) Statements.................41 Data Manipulation Language (DML) Statements................42 Transaction Control Statements.......................42 Resource Control and Optimization Statements................42 Control Statements..........................43 Object Naming Statements......................43 Trigger Statements...........................43 Stored Procedure Statements......................43 ALTER MATERIALIZED VIEW Statement..................44 Syntax Description of ALTER MATERIALIZED VIEW..............44 Considerations for ALTER MATERIALIZED VIEW...............44 ALTER MVGROUP Statement......................45 Considerations for ALTER MVGROUP..................45...
Page 5 Limits for Tables..........................74 Calculating Row Size........................75 Creating Partitions Automatically....................75 Record Format..........................75 Generating Unique Values For a Column..................75 Neoview SQL Extensions to CREATE TABLE................77 DISK POOL............................77 Considerations for DISK POOL....................77 Restrictions for DISK POOL.......................78 Examples of CREATE TABLE......................78 Examples of CREATE TABLE AS....................79 CREATE TRIGGER Statement......................81...
Page 6 Restrictions on Triggers......................84 Recompilation and Triggers.......................84 Triggers and Primary Keys......................84 Examples of CREATE TRIGGER.....................85 Before and After Triggers......................85 CREATE VIEW Statement........................87 Syntax Description of CREATE VIEW.....................87 Considerations for CREATE VIEW....................88 Reserved View Names.......................88 Effect of Adding a Column on View Definitions...............88 Authorization and Availability Requirements................88 Updatable and Non-Updatable Views..................88 Examples of CREATE VIEW......................89...
Page 7 Syntax Description of DROP VIEW....................100 Considerations for DROP VIEW....................100 Authorization and Availability Requirements.................100 Example of DROP VIEW.......................100 EXECUTE Statement...........................101 Syntax Description of EXECUTE....................101 Considerations for EXECUTE......................102 Scope of EXECUTE........................102 Examples of EXECUTE........................102 EXPLAIN Statement..........................104 Syntax.............................104 Considerations..........................105 Case Considerations.........................105 Number Considerations......................105 Formatted [OPTIONS 'f'] Considerations................105 Normal User [OPTIONS 'n'] Considerations................106 Expert User [OPTIONS 'e'] Considerations................108...
Page 8 Examples of LOCK TABLE......................128 MERGE INTO Statement........................129 Syntax Description of MERGE INTO....................129 Considerations for MERGE INTO....................129 Upsert Using Single Row......................129 Restrictions..........................130 Upsert Using Rowsets......................130 Example..........................130 MERGE From One Table Into Another..................130 Example..........................131 Reflexive Updates.......................131 PREPARE Statement...........................132 Syntax Description of PREPARE....................132 Considerations for PREPARE......................132 Availability of a Prepared Statement..................132 Examples of PREPARE........................132...
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....................155 Access Modes and the UNION Operator.................155 Examples of SELECT........................156 Examples for Embedded INSERT....................161 SET SCHEMA Statement........................163 Syntax Description of SET SCHEMA....................163 Consideration for SET SCHEMA....................163...
Page 10 MAINTAIN MVGROUP........................184 Examples of MAINTAIN.......................184 POPULATE INDEX Utility.........................186 Syntax Description of POPULATE INDEX..................186 Considerations for POPULATE INDEX..................186 Examples of POPULATE INDEX....................186 PURGEDATA Utility...........................187 Syntax Description of PURGEDATA.....................187 Considerations for PURGEDATA....................187 Examples of PURGEDATA......................187 4 SQL Language Elements...................189 Character Sets............................190 Columns..............................191 Column References........................191 Derived Column Names........................191 Column Default Settings........................191...
Page 11 Examples of Numeric Value Expressions.................219 Identifiers............................220 Regular Identifiers.........................220 Delimited Identifiers........................220 Examples of Identifiers........................220 Indexes..............................221 SQL Indexes...........................221 Keys..............................222 Clustering Keys..........................222 Index Keys.............................222 SQL Index Keys........................222 Primary Keys..........................222 Literals..............................223 Character String Literals........................223 Considerations for Character String Literals................224 Examples of Character String Literals..................224 Datetime Literals..........................225 Examples of Datetime Literals....................225 Interval Literals..........................226...
Page 12 Considerations for Search Condition....................248 Order of Evaluation........................248 Column References........................248 Subqueries..........................248 Examples of Search Condition.......................249 Subquery.............................250 SELECT Form of a Subquery......................250 Using Subqueries to Provide Comparison Values.................250 Nested Subqueries When Providing Comparison Values.............250 Correlated Subqueries When Providing Comparison Values............251 Tables..............................252 Base Tables and Views........................252 Example of a Base Table........................252 Triggers..............................253 Views..............................254...
Page 13 ASCII Function............................289 Example of ASCII..........................289 ASIN Function.............................290 Examples of ASIN..........................290 ATAN Function...........................291 Examples of ATAN........................291 ATAN2 Function..........................292 Example of ATAN2........................292 AVG Function............................293 Considerations for AVG.........................293 Data Type of the Result......................293 Operands of the Expression.....................293 Nulls............................293 Examples of AVG...........................293 CASE (Conditional) Expression......................295 Considerations for CASE.......................296 Data Type of the CASE Expression..................296 Character Data Type.........................296...
Page 14 Operands of the Expression.....................310 Nulls............................310 Examples of COUNT........................310 CURRENT Function..........................312 Example of CURRENT........................312 CURRENT_DATE Function........................313 Examples of CURRENT_DATE.....................313 CURRENT_ROLE Function........................314 Example of CURRENT_ROLE.......................314 CURRENT_TIME Function.........................315 Example of CURRENT_TIME.......................315 CURRENT_TIMESTAMP Function....................316 Example of CURRENT_TIMESTAMP...................316 CURRENT_USER Function........................317 Example of CURRENT_USER.......................317 DATE_ADD Function.........................318 Examples of DATE_ADD.......................318 DATE_SUB Function...........................319 Examples of DATE_SUB........................319...
Page 15 Examples of DIFF2.........................336 EXP Function............................338 Examples of EXP..........................338 EXPLAIN Function..........................339 Considerations for EXPLAIN Function..................339 Using a Statement Pattern......................339 Result of the EXPLAIN Function.....................339 Examples of EXPLAIN Function....................341 EXTRACT Function..........................342 Examples of EXTRACT........................342 FLOOR Function..........................343 Examples of FLOOR........................343 HOUR Function..........................344 Example of HOUR.........................344 INSERT Function..........................345 Examples of INSERT........................345 ISNULL Function..........................346...
Page 16 Example of MOD...........................364 MONTH Function..........................365 Example of MONTH........................365 MONTHNAME Function........................366 Example of MONTHNAME......................366 MOVINGAVG Function........................367 Example of MOVINGAVG......................367 MOVINGCOUNT Function........................368 Considerations for MOVINGCOUNT...................368 No DISTINCT Clause.......................368 Example of MOVINGCOUNT.......................368 MOVINGMAX Function........................370 Example of MOVINGMAX......................370 MOVINGMIN Function........................371 Example of MOVINGMIN......................371 MOVINGSTDDEV Function.......................372 Example of MOVINGSTDDEV......................372 MOVINGSUM Function........................374 Example of MOVINGSUM......................374...
Page 17 Examples of RUNNINGSTDDEV....................389 REPEAT Function..........................390 Example of REPEAT........................390 REPLACE Function..........................390 Example of REPLACE........................391 RIGHT Function..........................391 Examples of RIGHT........................391 ROUND Function..........................392 Examples of ROUND........................392 ROWS SINCE Function........................392 Considerations for ROWS SINCE....................393 Counting the Rows........................393 Examples of ROWS SINCE......................393 ROWS SINCE CHANGED Function....................394 Considerations for ROWS SINCE CHANGED................394 Counting the Rows........................394 Examples of ROWS SINCE CHANGED..................394...
Page 18 STDDEV Function..........................409 Considerations for STDDEV......................409 Definition of STDDEV......................409 Data Type of the Result......................409 Operands of the Expression.....................409 Nulls............................409 FLOAT(54) and DOUBLE PRECISION Data................409 Examples of STDDEV........................410 SUBSTRING/SUBSTR Function......................411 Alternative Forms..........................411 Considerations for SUBSTRING/SUBSTR..................411 Requirements for the Expression, Length, and Start Position..........411 Examples of SUBSTRING/SUBSTR....................411 SUM Function.............................413 Considerations for SUM........................413...
Page 19 A Quick Reference......................435 B Reserved Words......................437 Reserved Neoview SQL Identifiers ....................437 C Limits...........................441 D Standard SQL and Neoview SQL................443 ANSI SQL Standards...........................443 ISO Standards............................443 Neoview SQL Compliance........................444 Neoview SQL Extensions to Standard SQL..................447 Character Set Support.........................448 Index..........................449 Table of Contents...
Page 21 List of Tables 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 ..................35 Maximum Row Sizes Available.....................74 EXPLAIN Statement Options......................104 Plan Summary Information......................106 Node Listing Information ......................106 Cost Factors of DETAIL_COST column..................111 One-to-One Uppercase and Titlecase to Lowercase Mappings ..........354 One-to-One UCS2 Mappings ......................419...
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: Document Organization
New function. Document Organization • Chapter 1 (page 29), introduces Neoview SQL and covers topics such as database security, data consistency and integrity, transaction management, and ANSI compliance. • Chapter 2 (page 41), describes the SQL statements supported by Neoview SQL.
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: Related Documentation
For example: match-value [NOT] LIKE pattern [ESCAPE esc-char-expression] Related Documentation This manual is part of the HP Neoview customer library. Neoview Customer Library • Administration Neoview Database Administrator’s Guide Information about how to load and manage the Neoview database by using the Neoview DB Admin and other tools.
Page 27: Publishing History
544505–001 May 2007 544582–001 HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to: pubs.comments@hp.com Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
Page 29: Introduction
Security The services ID is intended to be used only to support the database without having access to the data itself. The services ID cannot manage user accounts. Only HP support has access to the super ID. User IDs and Role The system setup scripts perform initial configuration of services IDs.
Page 30: Data Consistency And Access Options
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
To protect the integrity of the database, Neoview SQL provides locks on data. For example, Neoview SQL locks a row when an executing process accesses a row to modify it. The lock ensures that no other process simultaneously modifies the same row.
Page 32: Lock Holder
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: •...
Page 33: User-Defined And System-Defined Transactions
AUTOCOMMIT is an option that can be set in a SET TRANSACTION statement. It specifies whether Neoview SQL will commit automatically, or roll back if an error occurs, at the end of statement execution. This option applies to any statement for which the system initiates a transaction.
Page 34 Table 1-2 Concurrent DDL/Utility and DML Operations DML Operation in Progress DDL Operations You SELECT SELECT SHARE SELECT EXCLUSIVE UPDATE/ INSERT/ Can Start UNCOMMITTED DELETE ALTER TABLE Allowed Allowed Allowed Allowed attributes ALTER TABLE other Allowed Waits Waits Waits CREATE INDEX with Allowed Allowed Waits...
Page 35: Transaction Access Modes
Table 1-3 Concurrent DML and DDL Operations (continued) DML Operations You Can Start REVOKE Allowed Allowed Waits Waits UPDATE Allowed Allowed Allowed Allowed STATISTICS DDL operation aborts the DML operation. Allowed except during commit phase. Table 1-4 describes the effect of various DDL and utility operations on table timestamps: Table 1-4 Operations Effect on Table Timestamps Alter Operation Timestamp Updated...
Page 36: Read Write
“READ COMMITTED”, “SERIALIZABLE or REPEATABLE READ ”. The Neoview SQL implementation for REPEATABLE READ and SERIALIZABLE is equivalent. SERIALIZABLE is used for purposes of illustration. You can set the isolation level of a transaction explicitly by using a SET TRANSACTION statement.
Page 37: Transaction Rollback Mode
For these situations, Neoview SQL raises an error during compilation. When Rollback Mode is set to OFF, Neoview SQL sets the auto abort interval for the transaction to 0, indicating that this transaction should never abort for exceeding a predefined time interval.
Page 38: Statements That Are Neoview Sql Extensions
SET TABLE TIMEOUT statement • TRANSPOSE clause • UNLOCK TABLE statement • UPDATE STATISTICS statement ANSI-Compliant Functions These functions are ANSI compliant, but some might contain Neoview SQL extensions: • AVG function • CASE expression • CAST expression • CHAR_LENGTH •...
Page 39: Neoview Sql Error Messages
Neoview SQL Error Messages Neoview SQL reports error messages and exception conditions When an error condition occurs, Neoview SQL returns a message number and a brief description of the condition. For example, Neoview SQL might display this error message: *** ERROR[1000] A syntax error occurred.
Page 41: Sql Statements
• “Stored Procedure Statements” Data Definition Language (DDL) Statements Use these DDL statements to define, delete, or modify the definition of a Neoview SQL schema, or object, or the authorization to use an object. “ALTER MATERIALIZED VIEW Statement” (page 44) Changes materialized views.
Page 42: Data Manipulation Language (Dml) Statements
Resource Control and Optimization Statements Use these statements to control access to a Neoview SQL table and its indexes on remote segments: “EXECUTE Statement” (page 101) Executes and SQL statement previously compiled by a PREPARE statement.
Page 43: Control Statements
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 44: Alter Materialized View Statement
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
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.
Page 46: Alter Synonym Statement
ALTER SYNONYM alternate-name TO object; alternate-name specifies the name of the synonym. See “Database Object Names” (page 196). object specifies Neoview SQL tables, views, and materialized views. See “Database Object Names” (page 196). Considerations • When the object being referenced by the synonym is dropped, then the synonym is also dropped.
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). ALTER TABLE name alter-action ATTRIBUTE {NO INSERTLOG | INSERTLOG}...
Page 48 SQL identifier. column-name must be unique among column names in the table. If the column name is a Neoview SQL reserved word, you must delimit it by enclosing it in double quotes. For example: "sql".myview. See “Identifiers”...
Page 49: Considerations For Alter Table
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".
Page 50: Alter Trigger Statement
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};...
Page 51: Alter View Statement
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...
Page 52: Begin Work Statement
BEGIN WORK and ends with the next COMMIT or ROLLBACK statement. See “Transaction Management” (page 32). BEGIN WORK is a Neoview SQL extension. BEGIN WORK Examples of BEGIN WORK • Group three separate statements—two INSERT statements and an UPDATE statement—that update the database within a single transaction: --- This statement initiates a transaction.
Page 53: Call Statement
“Identifiers” (page 220). If you do not fully qualify the procedure name, Neoview SQL qualifies it according to the schema of the current session. argument-list accepts arguments for IN, INOUT, or OUT parameters. The arguments consist of SQL expressions, including dynamic parameters, separated by commas: SQL-expression[{, SQL-expression}...]...
Page 54: Input Parameter Arguments
Stored Procedures in Java. Data Conversion of Parameter Arguments Neoview SQL performs an implicit data conversion when the data type of a parameter argument is compatible with but does not match the formal data type of the stored procedure. For stored procedure input values, the conversion is from the actual argument value to the formal parameter type.
Page 55 ---------- -------------- --------------- ---------- ------------------ 100210 19020.00 2006-04-10 HUGHES 100250 22625.00 2006-01-23 HUGHES 101220 45525.00 2006-07-21 SCHNABL 200300 52000.00 2006-02-06 SCHAEFFER 200320 9195.00 2006-02-17 KARAJAN 200490 1065.00 2006-03-19 WEIGL --- 13 row(s) selected. Order/Num Part/Num Unit/Price Qty/Ord Part Description ---------- -------- ------------ ----------...
Page 56: Commit Work Statement
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 57 COMMIT WORK; --- SQL operation complete. COMMIT WORK Statement...
Page 58: Create Index Statement
• “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.
Page 59: Considerations For Create Index
186). Authorization and Availability Requirements To create a Neoview SQL index, you must be the owner of the underlying table or be the services When the POPULATE option is specified, CREATE INDEX locks out INSERT, DELETE, and UPDATE operations on the table being indexed. If other processes have rows in the table locked when the operation begins, CREATE INDEX waits until its lock request is granted or timeout occurs.
Page 60: Create Materialized View Statement
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...
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: Considerations For Create Materialized View
• Materialized views cannot be created on views. Types of Materialized Views Neoview SQL defines the following three types of materialized views: • Materialized Join View (MJV): A materialized join view that holds the results of a join query with inner equi-joins of several tables. Outer joins and cross joins are not supported. These tables can be base tables or other MVs.
Page 63: Materialized Views Clustering
These indexes are not created for underlying tables with INSERTLOG attribute or those tables that are included in the MV's IGNORE CHANGES clause. Neoview SQL tries to minimize the number of secondary indexes created by the system based on the equal predicates in the query expression.
Page 64: Joins
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; •...
Page 66: Create Mvgroup Statement
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. Example of CREATE MVGROUP •...
Page 67: Create Schema Statement
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...
Page 68: Create Synonym Statement
CREATE SYNONYM alternate-name FOR object; alternate-name specifies the name of the synonym. See “Database Object Names” (page 196). object specifies Neoview SQL tables, views, and materialized views can be specified. See “Database Object Names” (page 196). Considerations • When the object being referenced by the synonym is dropped, the synonym is also dropped.
Page 69: Create Table Statement
The CREATE TABLE statement creates a Neoview SQL table. The CREATE VOLATILE TABLE statement creates a Neoview SQL table in a SQL session. Volatile tables are dropped automatically when the session ends. The CREATE TABLE AS statement creates a table based on the data...
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]...
Page 71: Syntax Description Of Create Table
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]]]...
Page 72 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".
Page 73: Considerations For Create Table
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
LIKE specification are defined as if the new table is created explicitly by the current user. If the source table has any unique or droppable primary key constraints, Neoview SQL creates indexes for them on the target table. Other indexes on the source table are not created on the target table.
Page 75: Calculating Row Size
If this clause is not specified, Neoview SQL will decide. If the table is partitioned then the table is automatically partitioned across all the disk volumes on the system.
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.
Page 77: Neoview Sql Extensions To Create Table
NOTE: In Neoview SQL, the partitioning key must be a subset of the clustering key. In the case of a table with a single column clustering key, the partitioning key must be the same as the clustering key.
Page 78: Restrictions For Disk Pool
• 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.
Page 79: Examples Of Create Table As
• 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’...
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.
Page 81: Create Trigger Statement
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: Considerations For Create Trigger
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.
Page 83 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
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).
Page 85: Examples Of Create Trigger
Examples of CREATE TRIGGER Before and After Triggers Suppose that you have a database to record patients’ vital signs and drugs prescribed for them. The database consists of these tables: • vital_signs, which records vital signs at each visit • prescription, which records prescriptions written for each patient •...
Page 86 record.vital_id), (SELECT record.id FROM record WHERE sample.id = record.vital_id), CURRENT_DATE, 'POUND OFF', '200 mg', '1 pill 1 hour before each meal', 0, 'Free sample no refills' SQL Statements...
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 ...)] AS query-expr [WITH [CASCADED] CHECK OPTION]...
Page 88: Considerations For Create View
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: Examples Of Create View
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. •...
Page 90: Delete Statement
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 A scalar subquery in expression cannot refer to the table being updated. If expression refers to columns being updated, Neoview SQL uses the original values to evaluate the expression and determine the new value.
Page 92: Considerations For Delete
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: Drop Index Statement
To drop an index, you must be the owner of the schema. Indexes That Support Constraints Neoview SQL uses indexes to implement some constraints. If you use the DROP CONSTRAINT option in an ALTER TABLE statement, Neoview SQL drops indexes that it created to implement that constraint. Example of DROP INDEX •...
Page 94: Drop Materialized View Statement
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.
Page 95: Drop Mvgroup Statement
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 •...
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.
Page 97: Drop Synonym Statement
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.
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...
Page 99: Drop Trigger Statement
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...
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.
Page 101: Execute Statement
If there are fewer parameter values in the USING clause than there are unnamed parameters in the PREPARE statement, Neoview SQL returns an error. If there are more parameter values in the USING clause than there are unnamed parameters in the PREPARE statement, Neoview SQL ignores the extra parameter values.
Page 102: Considerations For Execute
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 •...
Page 103 --- 1 row(s) selected. SQL>execute findemp using 20000.00, 300; EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY ------ --------------- -------------- ------- ------- -------- 75 TIM WALKER 3000 300 32000.00 89 PETER 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.employee...
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 105: Considerations
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.
Page 106: Normal User [Options 'N'] Considerations
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.
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.
Page 108: Expert User [Options 'E'] Considerations
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 ..\:_sys_hostVarPAPartNo_1539575856 FILE_SCAN ================================= SEQ_NO 1 NO CHILDREN TABLE_NAME ....
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: Machine-Readable [Options 'M'] Considerations
FILE_SCAN ================================= SEQ_NO 1 NO CHILDREN 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 .... 2 parent_frag .... 0 fragment_type ..
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.
Page 112: Example Of Explain Statement Using Odbc
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’...
Page 113: Example Of Explain Statement Using Jdbc
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...
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; System.out.println(nextException.getMessage()); System.out.println("SQLState " + nextException.getSQLState()); System.out.println("Error Code " + nextException.getErrorCode()); } while ((nextException = nextException.getNextException()) != null); SQL Statements...
Page 115: Grant Statement
• “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 ]...
Page 116: Considerations For Grant
WITH GRANT OPTION specifies that roles to whom privileges are granted have the right to grant the same privilege to other roles. Considerations for GRANT Authorization and Availability Requirements To grant a privilege on an object, you must have both that privilege and the right to grant that privilege.
Page 117: Grant Execute Statement
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 ]...
Page 118: Examples Of Grant Execute
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...
Page 119: Grant Schema Statement
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 ]...
Page 120: Considerations For Grant Schema
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.
Page 121: Insert Statement
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 The use of DEFAULT in a value expression is a Neoview SQL extension. If you attempt to insert NULL into a column that is defined as NOT NULL or DEFAULT into a column that is defined with NO DEFAULT, Neoview SQL returns an error. For the description of value expressions, see “Expressions”...
Page 123: Considerations For Insert
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.
Page 124: Isolation Levels Of Transactions And Access Options Of Statements
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.
Page 125: Examples Of Insert
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');...
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. •...
Page 127: Lock Table Statement
At the start of a session, the AUTOCOMMIT option is ON by default. When this option is ON, Neoview SQL automatically commits any changes, or rolls back any changes, made to the database at the end of statement execution. When you issue a LOCK TABLE statement without turning off AUTOCOMMIT, Neoview SQL locks the table temporarily, commits the transaction at the end of the LOCK TABLE statement and releases the locks.
Page 128: Indexes
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 •...
Page 129: Merge Into Statement
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...
Page 130: Restrictions
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 >...
Page 131: Example
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.
Page 132: Prepare Statement
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;...
Page 134: Revoke Statement
“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]}...
Page 135: Considerations For Revoke
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.
Page 136: Revoke Execute Statement
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 ]...
Page 137: Considerations For Revoke Execute
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).
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 ]}...
Page 139: Considerations For Revoke Schema
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.
Page 140: Rollback Work Statement
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.
Page 141: Select Statement
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...
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]...
Page 143 select-list is: * | select-sublist [,select-sublist]... select-sublist is: corr.*) | [corr.] single-col[[AS]name] table-ref is: table [[AS] corr [(col-expr-list)]] | STREAM (table) [[AS] corr [(col-expr-list)]] [AFTER LAST ROW] | view [[AS] corr [(col-expr-list)]] | STREAM (view) [[AS]] corr [(col-expr-list)]] [AFTER LAST ROW] | (query-expr) [AS] corr [(col-expr-list)] | (delete-statement [RETURN select-list]) [AS] corr [(col-expr-list)]...
Page 144: Syntax Description Of Select
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)]...
Page 145 Neoview SQL returns the columns you specify in select-list. To refer to a table or view, use the ANSI logical name.
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.
Page 147 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 FIRST rows-size [SORT BY colname [,colname]...] directs Neoview SQL to choose the first rows-size rows from the sorted result table. The sampling size is determined by using the specified number of rows.
Page 149 CUSTOMER.CITY. ASC | DESC specifies the sort order. The default is ASC. When Neoview SQL orders an intermediate result table on a column that can contain null, nulls are considered equal to one another but greater than all other nonnull values.
Page 150 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: Considerations For Select
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.
Page 152: Join Limits
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.
Page 153: Limitations Of Distinct Aggregates
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. SELECT sum(distinct a), stddev(distinct b) from T group by d; Examples of Multiple Distinct Aggregates •...
Page 154: Considerations For Sort By
• 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.
Page 155: Order By Clause And The Union Operator
(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.
Page 156: Examples Of Select
Use a table subquery to enclose the union, and apply the access mode to the main query. This statement receives a warning because Neoview SQL treats the access mode on the second SELECT as applicable only to that second SELECT:...
Page 157 1500 JIMMY SCHNEIDER 26000.00 2500 MIRIAM KING 18000.00 1000 CRAMER 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 >...
Page 158 Drop rows with unequal job codes. EMPLOYEE Table JOB Table EMPNUM ... JOBCODE ... SALARY JOBCODE JOBDESC 175500 MANAGER ....32000 SALESREP ..
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...
Page 160 JOBCODE 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. These joins show records from both tables and fill in NULLs for missing matches on either side: SELECT * FROM employee...
Page 161: Examples For Embedded Insert
(SELECT O.ordernum FROM sales.orders O WHERE custnum IN (SELECT custnum FROM sales.customer WHERE state = 'CALIFORNIA')) GROUP BY OD.ordernum; ORDERNUM (EXPR) ---------- --------------------- 200490 1030.00 300350 71025.00 300380 28560.00 --- 3 row(s) selected. The price for the total quantity ordered is computed for each order number. •...
Page 162 from ( insert into identity_table values (DEFAULT,100,100), (DEFAULT,200,200) --------------- ------ ------ 216944652091640 216944652091641 • 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.a = 300;...
Page 163: Set Schema Statement
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.
Page 164: Set Table Timeout Statement
Neoview SQL not to time out. A value of zero (0) directs Neoview SQL not to wait. If a table lock cannot be acquired or if a stream is empty, Neoview SQL immediately times out.
Page 165: Considerations For Set Table Timeout
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.
Page 166: Set Transaction Statement
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: Considerations For Set Transaction
This option applies to any statement for which the system initiates a transaction. If this option is set to ON, Neoview SQL automatically commits any changes or rolls back any changes made to the database at the end of statement execution. AUTOCOMMIT is on by default at the start of a session.
Page 168: Explicit Transactions
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.
Page 169: Update Statement
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.
Page 170 A scalar subquery in expression cannot refer to the table being updated. If expression refers to columns being updated, Neoview SQL uses the original values to evaluate the expression and determine the new value.
Page 171: Considerations For Update
ROLLBACK is encountered or an error occurs. 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.
Page 172: Requirements For Data In Row
When an UPDATE completes successfully, Neoview SQL reports the number of times rows were updated during the operation. Under certain conditions, updating a table with indexes can cause Neoview SQL to update the same row more than once, causing the number of reported updates to be higher than the actual number of changed rows.
Page 173: Set On Rollback Considerations
• 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.
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.employee (empnum, first_name, last_name, deptnum, salary) SQL Statements...
Page 175: Update Statistics Statement
You must use the ON clause to generate statistics stored in histogram tables. If you omit it, physical statistics are generated for Neoview SQL tables, and SQL returns a warning message. See “Using Statistics”...
Page 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.
Page 177: Considerations For Update Statistics
The value c must be an integer that is greater than or equal to zero (c > 0). If the ROWCOUNT clause in not specified, Neoview SQL determines the number of rows in the table either by estimation or SELECT COUNT(*).
Page 178: Generating And Clearing Statistics For Columns
--- SQL operation complete. • This example generates histogram statistics using the ON EVERY COLUMN option for the table DEPT. This statement performs a full scan, and Neoview SQL determines the default number of intervals. UPDATE STATISTICS FOR TABLE dept ON EVERY COLUMN;...
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...
Page 181: Sql Utilities
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: Maintain Command
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. •...
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.
Page 184: Maintain Mvgroup
The REFRESH utility can be applied to both incremental MVs and recomputed MVs. The REFRESH utility optimizes the refresh of MVGROUP. HP recommends that you gather all the MVs to refresh at a specific time into one group and refresh them together.
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
• “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
• “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 189: Sql Language Elements
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
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. The detailed language...
Page 191: Columns
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. SQL Language Elements...
Page 193: Constraints
If you omit the schema portion of the name you specify, Neoview SQL expands the name by using the schema for the table.
Page 194: Correlation Names
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 •...
Page 195: Database Objects
“Views” Ownership In Neoview SQL, the creator of a schema owns all the objects defined in the schema. In addition, you can use the GRANT and REVOKE statements to grant access privileges for a table or view to specified users.
Page 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 197: Data Types
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.
Page 198 Type SQL Designation Description Size or Range (1) DOUBLE PRECISION Floating-point numbers (64 +/- 2.2250738585072014e-308 bits) with 1 through 52 bits through of precision (52 bits of binary +/-1.7976931348623157e+308; stored precision and 11 bits of in 8 byte exponent) Decimal number DECIMAL (1,scale) to Decimal number with 1 to 18 digits.
Page 199: Comparable And Compatible Data Types
Compatible data types are also comparable. Assignment and comparison are the basic operations of Neoview SQL. Assignment operations are performed during the execution of INSERT and UPDATE statements. Comparison operations are performed during the execution of statements that include predicates, aggregate (or set) functions, and GROUP BY, HAVING, and ORDER BY clauses.
Page 200: Interval Data Types
Character String Data Types • “Considerations for Character String Data Types” Neoview SQL includes both fixed-length character data and variable-length character data. You cannot compare character data to numeric, datetime, or interval data. character-type is: CHAR[ACTER] [(length [CHARACTERS])] [char-set]...
Page 201: Considerations For Character String Data Types
Character Sets For Neoview SQL, a character data type can be associated only with the DEFAULT collation. You set the default NCHAR data type and you can select from the ISO88591 or UCS2. If you do not make a selection, the default is UCS2.
Page 202: Datetime Data Types
“CAST Expression” (page 298). Neoview SQL accepts dates, such as October 5 to 14, 1582, that were omitted from the Gregorian calendar. This functionality is a Neoview SQL extension. The range of times that a datetime value can represent is: January 1, 1 A.D., 00:00:00.000000 (low value) December 31, 9999, 23:59:59.999999 (high value)
Page 203 Second, from 00 to 59 Microsecond, from 000000 to 999999 msssss 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. Data Types...
Page 204: Interval Data Types
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). interval-type is: INTERVAL { start-field TO end-field | single-field } start-field is: {YEAR | MONTH | DAY | HOUR | MINUTE} [(leading-precision)] end-field is:...
Page 205: Numeric Data Types
Approximate numeric data types are types that do not necessarily represent a value exactly: FLOAT, REAL, and DOUBLE PRECISION. A column in a Neoview SQL table declared with a floating-point data type is stored in IEEE floating-point format and all computations on it are done assuming that. Neoview SQL tables can contain only IEEE floating-point data.
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.
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. Numeric Data Types...
Page 208: Expressions
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 The concatenation of two string literals. 'ABILENE ' ||' TEXAS' The concatenation of three string literals to form the literal: 'ABILENE ' ||' TEXAS' || x’55 53 41 'ABILENE TEXAS USA' The concatenation of a string literal with the value in 'Customer ' || custname column CUSTNAME.
Page 210: Datetime Value Expressions
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
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: The sum of the current date and an interval value of one CURRENT_DATE + INTERVAL '1' DAY day.
Page 212 (EXPR) ---------- 1996-03-12 --- 1 row(s) selected. The result of adding 20 days to 1996-02-21 is 1996-03-12. Neoview SQL correctly handles 1996 as a leap year. • Subtract an interval value qualified by HOUR TO MINUTE from a datetime value: SELECT ship_timestamp - INTERVAL '15:30' HOUR TO MINUTE FROM persnl.project...
Page 213: Interval Value Expressions
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;...
Page 214: Considerations For Interval Value Expressions
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;...
Page 215: Examples Of Interval Value Expressions
INTERVAL '1' MONTH + INTERVAL '7' DAY • If you multiply or divide an interval value by a numeric value expression, Neoview SQL converts the interval value to its least significant subfield and then multiplies or divides it by the numeric value expression. The result has the same fields as the interval that was multiplied or divided.
Page 216 4000 1996-09-21 1996-10-21:10:15:00.0000 5000 1996-09-28 1996-10-28:09:25:01.1111 • 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.project;...
Page 217: Numeric Value Expressions
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 numeric-expression is: numeric-term | numeric-expression + numeric-term...
Page 218 Neoview SQL cannot convert an HP REAL or a FLOAT data type with precision between 1 and 22 bits to IEEE REAL, because the HP exponent will not fit in an IEEE REAL data type. The precision of an HP data type will be maintained correctly.
Page 219: Examples Of Numeric Value Expressions
Examples of Numeric Value Expressions These are examples of numeric value expressions: Numeric literal. The product of the values in the SALARY column and a salary * 1.10 numeric literal. The product of the values in the UNIT_PRICE and unit_price * qty_ordered QTY_ORDERED columns.
Page 220: Identifiers
Lisa"""). Unlike regular identifiers, delimited identifiers are case-sensitive. Spaces within a delimited identifier are significant except for trailing spaces, which Neoview SQL truncates. You can use reserved words as delimited identifiers. These forms of delimited identifiers are not supported. Results are unpredictable for delimited identifiers that: •...
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 •...
Page 222: 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.
Page 223: Literals
[_character-set | N]'string' _character-set specifies the character set ISO88591 and UCS2. If you omit the character set specification, the default is whatever character set default you set when you installed Neoview SQL. See “Character Sets” (page 190). associates the system default character set with the string literal. The default is set by the value of the NATIONAL_CHARSET attribute during the Neoview SQL installation.
Page 224: Considerations For Character String Literals
• Do not put a space between the character set qualifier and the character string literal. If you use this character string literal in a statement, Neoview SQL returns an error. • To specify a single quotation mark within a string literal, use two consecutive single quotation marks.
Page 225: Datetime Literals
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.
Page 226: Interval Literals
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).
Page 227: Considerations For Interval Literals
'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: Unsigned integer that specifies a number of years. years years can be up to 18 digits, or 16 digits if months is the end-field.
Page 228: Examples Of Interval Literals
Examples of Interval Literals Interval of 1 month INTERVAL '1' MONTH Interval of 7 days INTERVAL '7' DAY Interval of 2 years, 7 months INTERVAL '2-7' YEAR TO MONTH Interval of 5 days, 2 hours, 15 minutes, and 36.33 seconds INTERVAL '5:2:15:36.33' DAY TO SECOND(2) Interval that subtracts 5 days INTERVAL - '5' DAY...
Page 229: Examples Of Numeric Literals
Examples of Numeric Literals • These are all numeric literals, along with their display format: Literal Display Format 580.45 580.45 +005 -.3175 -.3175 1300000000 1300000000 -0.123456789012345678 -.123456789012345678 99E-2 9.9000000E-001 12.3e+5 1.2299999E+006 Literals...
Page 230: Null
Null is the default for a column (other than NOT NULL) unless the column definition includes a DEFAULT clause (other than DEFAULT NULL) or the NO DEFAULT clause. The default value for a column is the value Neoview SQL inserts in a row when an INSERT statement omits a value for a particular column.
Page 231: Null And Expression Evaluation Comparison
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.
Page 232: Sql Tables
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. If the table is partitioned then the table is partitioned across all the disk volumes on the system.
Page 233: Predicates
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 234: Considerations For Between
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 >...
Page 235: Considerations For Comparison Predicates
Predicate X<>Y is true if X=Y is false. If X1<>Y1, Neoview SQL does not look at all components. It stops and returns a value of false for the X=Y predicate and a value of true for the X<>Y predicate.
Page 236: Examples Of Comparison Predicates
X=Y. Predicate X<=Y is true if X<Y is true or X=Y is true. In this scenario, Neoview SQL might need to look through all components and return true if they are all equal. It stops at the first nonequal components, Xm<>Ym.
Page 237 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. To evaluate the predicate, Neoview SQL converts the two INTERVAL values to MINUTE. The comparison predicate is true.
Page 238: Exists Predicate
--- 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.
Page 239: In Predicate
WHERE EXISTS (SELECT partnum FROM invent.partloc PL WHERE PS.partnum = PL.partnum AND qty_on_hand < 20); PARTNUM SUPPNUM ------- ------- 2001 2003 --- 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.
Page 240: Examples Of In
Both fixed-length and varying-length strings are padded in this way. For example, Neoview SQL considers the string ‘JOE’ equal to a value JOE stored in a column of data type CHAR or VARCHAR of width three or more. Similarly, Neoview SQL considers a value JOE stored in any column of the CHAR data type equal to the value JOE stored in any column of the VARCHAR data type.
Page 241: Null Predicate
------- ------------------ 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);...
Page 242: Examples Of Null
Note that the rvc IS NOT NULL predicate is not equivalent to NOT rvc IS NULL. Examples of NULL • Find all rows with null in the SALARY column: salary IS NULL • This predicate evaluates to true if the expression (PRICE + TAX) evaluates to null: (price + tax) IS NULL •...
Page 243: Considerations For All, Any, Some
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 244 EMPNUM FIRST_NAME LAST_NAME SALARY ------ --------------- -------------------- ----------- ROGER GREEN 175500.00 JERRY HOWARD 137000.10 JANE RAYMOND 136000.00 ALAN TERRY 39500.00 HENDERSON 65000.00 JESSICA CRINER 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.odetail...
Page 245: Privileges
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 246: General Rules For Column-List
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). SQL Language Elements...
Page 247: Schemas
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
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. You can use a search condition in the WHERE clause of a SELECT, DELETE, or UPDATE statement, the HAVING clause of a SELECT statement, the NOT CASESPECIFIC clause of a SELECT statement, the searched form of a CASE expression, the ON clause of a SELECT statement...
Page 249: Examples Of Search Condition
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...
Page 250: Subquery
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.
Page 251: Correlated Subqueries When Providing Comparison Values
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.
Page 252: Tables
(for example, the storage order of rows within the table) A Neoview SQL table is described in an SQL schema and stored in a physical file in the environment. An SQL table name can be a fully qualified ANSI name of the form schema-name.object-name.
Page 253: Triggers
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 254: Views
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.
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. Views...
Page 257: Sql Clauses
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.
Page 258: Default Clause
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. DEFAULT default | NO DEFAULT | identity-column- specification default is:...
Page 259: Examples Of Default
Examples of DEFAULT • This example uses DEFAULT clauses on CREATE TABLE to specify default column values CREATE TABLE items ( item_id CHAR(12) NO DEFAULT ,description CHAR(50) DEFAULT NULL ,num_on_hand INTEGER DEFAULT 0 NOT NULL; • This example uses DEFAULT clauses on CREATE TABLE to specify default column values: CREATE TABLE persnl.project ( projcode NUMERIC (4) UNSIGNED...
Page 260: Sample Clause
N. The number of rows picked follows a binomial distribution with mean equal to r * N/100. If you specify a sample size greater than 100 PERCENT, Neoview SQL returns all the rows in the result table plus duplicate rows. The duplicate rows are picked from the result table according to the specified sampling method.
Page 261: Considerations For Sample
FIRST rows-size [SORT BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] | DESC[ENDING]]]...] directs Neoview SQL to choose the first rows from the result table. You can specify the order of the rows to sample. Otherwise, Neoview SQL chooses an arbitrary order. The sampling...
Page 262: Examples Of Sample
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.
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;...
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.
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';...
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 ORDER BY age;...
Page 267 --- 30 row(s) selected. SAMPLE Clause...
Page 268: Sequence By Clause
You must include a SEQUENCE BY clause if you include a sequence function in the select list of the SELECT statement. Otherwise, Neoview SQL returns an error. Further, you cannot include a SEQUENCE BY clause if there is no sequence function in the select list. See “Sequence Functions”...
Page 269: Examples Of Sequence By
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 MCOUNT Part/Num Order/Date AMOUNT ORDER_TOTAL TOTAL_SALES ---------- ----------- -------- ---------- ---------- -------------- -------------- 100250 1997-01-23 14000.00 14000.00 14000.00 100250 5103 1997-01-23 4000.00 18000.00 18000.00 100250...
Page 271: Transpose Clause
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]... [KEY BY key-colname]...
Page 272: Considerations For Transpose
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.
Page 273: Cardinality Of The Transpose Result
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)
Page 274 The result table of the TRANSPOSE query is: KEYCOL VALCOL • 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;...
Page 275 (EXPR) -------------------- --- 3 row(s) selected. • This query shows how multiple TRANSPOSE clauses can be used in the same query. The result table from this query has nine times as many rows as there are rows in MYTABLE: SELECT KEYCOL1, VALCOL1, KEYCOL2, VALCOL2 FROM mytable TRANSPOSE A, B, C AS VALCOL1 KEY BY KEYCOL1 TRANSPOSE D, E, F AS VALCOL2...
Page 276 The result table of the TRANSPOSE query is: KEYCOL VALCOL1 VALCOL2 A question mark (?) in a value column indicates no value for the given KEYCOL. • This query shows how the preceding query can include a GROUP BY clause: SELECT KEYCOL, VALCOL1, VALCOL2, COUNT(*) FROM mytable TRANSPOSE A, B, C AS VALCOL1 D, E, F AS VALCOL2...
Page 277 The result table of the TRANSPOSE query is: VALCOL1 VALCOL2 VALCOL3 TRANSPOSE Clause...
Page 279: Sql Functions And Expressions
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. Categories Use these types of functions within an SQL value expression: •...
Page 280: Character String Functions
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: “ASCII Function”...
Page 281: Datetime Functions
“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 282: Mathematical Functions
“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 283: Sequence Functions
“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.
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.
Page 285: Other Functions And Expressions
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
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.
Page 287: Acos Function
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.
Page 288: Add_Months Function
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.
Page 289: Ascii Function
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...
Page 290: Asin Function
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.
Page 291: Atan Function
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.
Page 292: Atan2 Function
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.
Page 293: Avg Function
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.
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 "AVERAGE SALARY" -------- --------------------- 1000 52000.17 2000 50000.10 1500 41250.00 2500 37000.00 --- 4 row(s) selected.
Page 295: Case (Conditional) Expression
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: Considerations For Case
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.
Page 297 WHEN 500 THEN 'ACCOUNTANT' WHEN 600 THEN 'ADMINISTRATOR ANALYST' WHEN 900 THEN 'SECRETARY' ELSE NULL FROM persnl.employee; LAST_NAME FIRST_NAME (EXPR) -------------------- --------------- ----------------- GREEN ROGER MANAGER HOWARD JERRY MANAGER RAYMOND JANE MANAGER CHOU JOHN SECRETARY CONRAD MANFRED PROGRAMMER HERMAN SALESREP CLARK LARRY ACCOUNTANT...
Page 298: Cast Expression
10 characters consisting of the year, a hyphen, the month, another hyphen, and the day. • A date value to a character string or to a TIMESTAMP (Neoview SQL fills in the time part with 00:00:00.00). •...
Page 299: Examples Of Cast
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
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.
Page 301: Char Function
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.
Page 302: Char_Length Function
Considerations for CHAR_LENGTH CHAR and VARCHAR Operands For a column declared as fixed CHAR, Neoview SQL returns the maximum length of that column. For a VARCHAR column, Neoview SQL returns the actual length of the string stored in that column.
Page 303: Coalesce Function
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.
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
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.
Page 306 VALUES (002, 'Executed at ' || CAST (CURRENT_TIMESTAMP AS CHAR(26))); SQL Functions and Expressions...
Page 307: Converttimestamp Function
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...
Page 308: Cos Function
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.
Page 309: Cosh Function
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.
Page 310: Count Function
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 (*)
Page 311 --- 1 row(s) selected. SELECT COUNT(*) FROM persnl.employee WHERE jobcode IS NOT NULL; (EXPR) ----------- --- 1 row(s) selected. • Count the number of distinct departments in the EMPLOYEE table: SELECT COUNT (DISTINCT deptnum) FROM persnl.employee; (EXPR) ----------- --- 1 row(s) selected. COUNT Function...
Page 312: Current Function
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.
Page 313: Current_Date Function
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 •...
Page 314: Current_Role Function
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);...
Page 315: Current_Time Function
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
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.
Page 317: Current_User Function
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 318: Date_Add Function
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
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: Examples Of Dateadd
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.
Page 321: Datediff Function
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...
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.
Page 323: Date_Part Function (Of A Timestamp)
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.
Page 324: Date_Trunc Function
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'.
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.
Page 326: Dayname Function
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 327: Dayofmonth Function
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.
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.
Page 329: Dayofyear Function
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 330: Decode Function
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.
Page 331: Examples Of Decode
‘Hi’, ?P3, NULL) *** ERROR[8822] The statement was not prepared. The last ret-val is an explicit NULL. When Neoview SQL encounters this situation, it assumes that the return value will be NUMERIC(18,6). Once Neoview SQL determines that the return values are numeric, it determines that all possible return values must be numeric.
Page 332 This statement is equivalent and will not produce an error: SELECT decode( (?p1 || ?p2), trim(?p1), ‘Hi’ ) from emp; SQL Functions and Expressions...
Page 333: Degrees Function
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.
Page 334: Diff1 Function
DIFF1 computation could result in a divisor of zero. Datetime Arguments In general, Neoview SQL does not allow division by a value of INTERVAL data type. However, to permit use of the two-argument version of DIFF1 with times and dates, Neoview SQL relaxes this restriction and allows division by a value of INTERVAL data type.
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...
Page 336: Diff2 Function
DIFF1 computation could result in a divisor of zero. Datetime Arguments In general, Neoview SQL does not allow division by a value of INTERVAL data type. However, to permit use of the two-argument version of DIFF2 with times and dates, Neoview SQL relaxes this restriction and allows division by a value of INTERVAL data type.
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.
Page 338: Exp Function
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.
Page 339: Explain Function
EXPLAIN result for all statements prepared within the current session: SELECT * FROM TABLE (EXPLAIN (NULL,'%')) For more information about the LIKE predicate, see the Neoview SQL Reference Manual. This statement returns the EXPLAIN result for all statements prepared within the embedded SQL module named MYCAT.MYSCH.MYPROG:...
Page 340 Statement name; truncated on the right if longer than 60 characters. PLAN_ID LARGEINT Unique system-generated plan ID automatically assigned by Neoview SQL; generated at compile time. SEQ_NUM Sequence number of the current operator in the operator tree; indicates the sequence in which the operator tree is generated.
Page 341: Examples Of Explain Function
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
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 “Datetime Value Expressions”...
Page 343: Floor Function
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.
Page 344: Hour Function
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 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
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.
Page 347: Juliantimestamp Function
YEAR through SECOND, Neoview SQL extends the value before converting it to a Julian timestamp. Datetime fields to the left of the specified datetime value are set to current date fields. Datetime fields to the right of the specified datetime value are set to zero.
Page 348 SELECT LASTNOTNULL (I1) AS LASTNOTNULL FROM mining.seqfcn SEQUENCE BY TS; LASTNOTNULL ----------- 6215 6215 19058 19058 11966 --- 5 row(s) selected. SQL Functions and Expressions...
Page 349: Lcase Function
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. LCASE is a Neoview SQL extension. LCASE (character-expression) character-expression is an SQL character value expression that specifies a string of characters to downshift.
Page 350: Left Function
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.
Page 351: Locate Function
NULL. See “Character Value Expressions” (page 208). Neoview SQL returns the result as a 2-byte signed integer with a scale of zero. If substring-expression is not found in source-expression, Neoview SQL returns 0. Considerations for LOCATE Result of LOCATE If the length of source-expression is zero and the length of substring-expression is greater than zero, Neoview SQL returns 0.
Page 352: Log Function
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...
Page 353: Log10 Function
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.
Page 354: Lower Function
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 .
Page 355 Table 6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) L (x) L(x) L(x) L(x) L(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...
Page 356 Table 6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) L (x) L(x) L(x) L(x) L(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...
Page 357 Table 6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings (continued) L (x) L(x) L(x) L(x) L(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...
Page 358: Example Of Lower
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) (EXPR) (EXPR) ----------------- -------------------...
Page 359: Lpad Function
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]) can be an expression. See “Character Value Expressions” (page 208). can be an expression but must be an integral value. If len is equal to the length of the string, no change is made.
Page 360: Ltrim Function
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).
Page 361: Max/Maximum Function
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...
Page 362: Min Function
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.
Page 363: Minute Function
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 364: Mod Function
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
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 366: Monthname Function
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 367: Movingavg Function
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
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. MOVINGCOUNT Function...
Page 370: Movingmax Function
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
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
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. MOVINGSTDDEV Function...
Page 374: Movingsum Function
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
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. SQL Functions and Expressions...
Page 377: Nullif Function
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.
Page 378: Nullifzero Function
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 •...
Page 379: Nvl Function
If operand is a null value, NVL returns new-operand. If operand is not a null value, NVL returns operand. The operand and new-operand can be a column name, subquery, Neoview SQL string functions, math functions, or constant values. Examples of NVL •...
Page 380: Octet_Length Function
Considerations for OCTET_LENGTH CHAR and VARCHAR Operands For a column declared as fixed CHAR, Neoview SQL returns the length of that column as the maximum number of storage bytes. For a VARCHAR column, Neoview SQL returns the length of the string stored in that column as the actual number of storage bytes.
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
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.14159260000000064E+000: PI() SQL Functions and Expressions...
Page 383: Position Function
NULL. See “Character Value Expressions” (page 208). Neoview SQL returns the result as a 2-byte signed integer with a scale of zero. If substring-expression is not found in source-expression, Neoview SQL returns zero. Considerations for POSITION Result of POSITION If the length of source-expression is zero and the length of substring-expression is greater than zero, Neoview SQL returns 0.
Page 384: Power Function
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.
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.
Page 386: Radians Function
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.
Page 387: Rank/Runningrank Function
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.
Page 388 SELECT I1, RUNNINGRANK (I1) AS RANK FROM cat.sch.seqfcn SEQUENCE BY I1; RANK ----------- -------------------- --- 8 row(s) selected. Return the rank of I1 descending: SELECT I1, RUNNINGRANK (I1) AS RANK FROM cat.sch.seqfcn SEQUENCE BY I1 DESC; RANK ----------- -------------------- --- 8 row(s) selected. Return the rank of I2, using the alternative RANK syntax: SELECT I2, RANK (I2) AS RANK FROM cat.sch.seqfcn...
Page 389: Runningstddev Function
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. RUNNINGSTDDEV returns the standard deviation of nonnull values of column-expression up to and including the current row.
Page 390: Repeat Function
REPEAT Function The REPEAT function returns a character string composed of the evaluation of a character expression repeated a specified number of times. REPEAT is a Neoview SQL extension. REPEAT (character-expr, count) character-expr specifies the source string from which to return the specified number of repeated strings.
Page 391: Example Of Replace
RIGHT Function The RIGHT function returns the rightmost specified number of characters from a character expression. RIGHT is a Neoview SQL extension. RIGHT (character-expr, count) character-expr specifies the source string from which to return the rightmost specified number of characters.
Page 392: Round Function
The ROWS SINCE function is a sequence function that returns the number of rows counted since the specified condition was last true in the intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 268). ROWS SINCE is a Neoview SQL extension. SQL Functions and Expressions...
Page 393: Considerations For Rows Since
If a row is reached where the condition is true, ROWS SINCE returns the number of rows counted so far. Otherwise, if the condition is never true within the result table being considered, ROWS SINCE returns null. Neoview SQL then goes to the next row as the new current row. Examples of ROWS SINCE •...
Page 394: Rows Since Changed Function
SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 268). ROWS SINCE CHANGED is a Neoview SQL extension. ROWS SINCE CHANGED (column-expression-list) column-expression-list is a comma-separated list that specifies a derived column list determined by the evaluation of the column expression list.
Page 395: Rtrim Function
17, 'gofly' ) RTRIM Function The RTRIM function removes trailing spaces from a character string. RTRIM is a Neoview SQL extension. RTRIM (character-expression) character-expression is an SQL character value expression and specifies the string from which to trim trailing spaces.
Page 396: Considerations For Runningavg
SELECT statement. See “SEQUENCE BY Clause” (page 268). RUNNINGCOUNT is a Neoview SQL extension. RUNNINGCOUNT {(*) | (column-expression)} as an argument causes RUNNINGCOUNT(*) to return the number of rows in the intermediate result table up to and including the current row.
Page 397: Runningmax Function
SEQUENCE BY clause in a SELECT statement. See “SEQUENCE BY Clause” (page 268). RUNNINGMAX is a Neoview SQL extension. RUNNINGMAX (column-expression) column-expression specifies a derived column determined by the evaluation of the column expression.
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. SQL Functions and Expressions...
Page 399: Runningrank Function
RUNNINGRANK Function “RANK/RUNNINGRANK Function” (page 387). RUNNINGRANK Function...
Page 400: Runningstddev Function
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. RUNNINGSTDDEV returns the standard deviation of nonnull values of column-expression up to and including the current row.
Page 401: Runningsum Function
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
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
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 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.
Page 405: Sin Function
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.
Page 406: Sinh Function
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.
Page 407: Space Function
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.
Page 408: Sqrt Function
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...
Page 409: Stddev Function
Because the definition of variance has N-1 in the denominator of the expression (if weight is not specified), Neoview SQL returns a system-defined default setting of zero (and no error) if the number of rows in the table, or a group of the table, is equal to 1.
Page 410: Examples Of Stddev
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;...
Page 411: Substring/Substr Function
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]) SUBSTRING (character-expr,start-position,length) character-expr...
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) •...
Page 413: Sum Function
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.
Page 414: Tan Function
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.
Page 415: Tanh Function
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.
Page 416: This Function
If a row is reached where the condition is true, ROWS SINCE returns the number of rows counted so far. Otherwise, if the condition is never true within the result table being considered, ROWS SINCE returns null. Neoview SQL then goes to the next row as the new current row and the THIS constant is reevaluated.
Page 417: Translate Function
UCS2 character set no data loss is possible. However, when Neoview SQL translates a character-value-expression from UCS2, certain characters encoded in UTF16 cannot be converted to the target character set. Neoview SQL displays an error in this case.
Page 418: Trim Function
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.
Page 419: Ucase Function
Unicode “alphabetic” property and whose Unicode name includes title. UCASE returns a string of fixed-length or variable-length character data, depending on the data type of the input string. UCASE is a Neoview SQL extension. UCASE (character-expression) character-expression is an SQL character value expression that specifies a string of characters to upshift.
Page 420 Table 6-2 One-to-One UCS2 Mappings (continued) U (x) U(x) U(x) U(x) U(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...
Page 421 Table 6-2 One-to-One UCS2 Mappings (continued) U (x) U(x) U(x) U(x) U(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...
Page 422: Two-Character Ucs2 Mapping
Table 6-2 One-to-One UCS2 Mappings (continued) U (x) U(x) U(x) U(x) U(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...
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...
Page 424 Table 6-3 Two-Character UCS2 Mapping (continued) Titlecase characters Two-character uppercase expansions 0x1F9C 0x1F2C 0x0399 0x1F9D 0x1F2D 0x0399 0x1F9E 0x1F2E 0x0399 0x1F9F 0x1F2F 0x0399 0x1FA0 0x1F68 0x0399 0x1FA1 0x1F69 0x0399 0x1FA2 0x1F6A 0x0399 0x1FA3 0x1F6B 0x0399 0x1FA4 0x1F6C 0x0399 0x1FA5 0x1F6D 0x0399 0x1FA6 0x1F6E 0x0399 0x1FA7...
Page 425: Examples Of Ucase
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...
Page 426 ----------------- ------------------- ------------------ Hotel Oregon HOTEL OREGON hotel oregon --- 17 row(s) selected. “LCASE Function” (page 349). For more examples of when to use the UCASE function, see “UPSHIFT Function” (page 428). SQL Functions and Expressions...
Page 427: Upper Function
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
UCASE function. UPSHIFT returns a string of fixed-length or variable-length character data, depending on the data type of the input string. UPSHIFT is a Neoview SQL extension. UPSHIFT (character-expression) character-expression is an SQL character value expression that specifies a string of characters to upshift. See “Character Value Expressions”...
Page 429: User Function
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.
Page 430: Variance Function
Because the definition of variance has N-1 in the denominator of the expression (when weight is not specified), Neoview SQL returns a default value of zero (and no error) if the number of rows in the table, or a group of the table, is equal to 1.
Page 431: Data Type Of The Result
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.
Page 432: Week Function
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 433: Year Function
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 434: Zeroifnull Function
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
A Quick Reference This appendix provides a quick, alphabetic reference to commands, statements, and utilities. For other topics, see the Index. “ALTER MATERIALIZED VIEW Statement” (page 44) “ALTER MVGROUP Statement” (page 45) “ALTER VIEW Statement” (page 51) “ALTER TABLE Statement” (page 47) “ALTER TRIGGER Statement”...
Page 436 “POPULATE INDEX Utility” (page 186) “PURGEDATA Utility” (page 187) “PREPARE Statement” (page 132) “REVOKE Statement” (page 134) “REVOKE EXECUTE Statement” (page 136) “REVOKE SCHEMA Statement” (page 138) “ROLLBACK WORK Statement” (page 140) “SELECT Statement” (page 141) “SET SCHEMA Statement” (page 163) “SET TABLE TIMEOUT Statement”...
Page 437: B Reserved Words
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.
Page 438 Table B-1 Reserved SQL Identifiers (continued) BIT_LENGTH INITIALLY SCHEMA BLOB INNER SCOPE BOOLEAN INOUT SCROLL BOTH INPUT SEARCH BREADTH INSENSITIVE SECOND INSERT SECTION CALL SELECT CASE INTEGER SENSITIVE CASCADE INTERSECT SESSION CASCADED INTERVAL SESSION_USER CAST INTO CATALOG SETS CHAR ISOLATION SIGNAL CHAR_LENGTH ITERATE...
Page 439 DESCRIBE TRIM DESCRIPTOR ONLY TRUE DESTROY OPEN UNDER DESTRUCTOR OPERATORS UNION DETERMINISTIC OPTION UNIQUE DIAGNOSTICS UNKNOWN DISTINCT ORDER UNNEST DICTIONARY ORDINALITY UPDATE DISCONNECT OTHERS UPPER DOMAIN UPSHIFT DOUBLE OUTER USAGE DROP OUTPUT USER DYNAMIC OVERLAPS USING Reserved Neoview SQL Identifiers...
Page 440 PRESERVE WHERE EXTERNAL PRIMARY WHILE EXTRACT PRIOR WITH FALSE PRIVATE WITHOUT FETCH PRIVILEGES WORK FIRST PROCEDURE WRITE FLOAT PROTECTED YEAR ZONE Indicates reserved words that are Neoview SQL extensions. Words reserved by the ANSI standard are not marked. Reserved Words...
Page 441: C Limits
INSERT operations 150 records can be inserted into a Neoview SQL table in a single INSERT operation. Joins 40 tables can be joined, including base tables of views, but joining more tables affects performance.
Page 443: D Standard Sql And Neoview Sql
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”...
Page 444: Neoview Sql Compliance
F201 CAST function F471 Scalar subquery values F481 Expanded NULL predicate This table lists the Core SQL:1999 features for which Neoview SQL offers partial support: ID, Feature Level of Support E071 Basic query expressions Neoview SQL fully supports this subfeature:...
Page 445 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 match that of SQL '99) E121-17 WITH HOLD cursors (supported only for SELECT statements that use stream access mode or an...
Page 446 F051-04 Comparison predicate for DATE, TIME, and TIME-STAMP data types F051-05 Explicit CAST between datetime types and character types F051-06 CURRENT_DATE Neoview SQL does not support these subfeatures F051-07 LOCALTIME (equivalent to CAST (CURRENT_TIME AS TIME WITHOUT TIME ZONE)) F051-08 LOCALTIMESTAMP...
Page 447: Neoview Sql Extensions To Standard Sql
Neoview SQL provides many features that enhance or supplement the functionality of standard SQL. In your Neoview SQL applications, you can use these extensions just as you can use Core SQL:1999. This table shows the Non-Core extensions that Neoview SQL supports:...
Page 448: Character Set Support
10646, see the Web sites listed under “ISO Standards” (page 443). Neoview SQL complies fully with the Unicode 2.1 standard. For information about this standard, see the Web site of the Unicode Consortium: http://www.unicode.org Neoview SQL uses UTF-16BE (16-bit) encoding for the Unicode (UCS2) character set. The full range of UTF-16 characters is allowed, but surrogate pairs are not recognized as characters.
Page 449: Index
ANSI examples of, 286, 288 compliance, description of, 37 syntax diagram of, 286 SQL standards, 443 Access options standards, Neoview SQL compliance, 444 summary of, 30 ANSI names DELETE statement use of, 91 schemas, 247 DML statements use of, 30...
Page 450 syntax diagram of, 300 example of, 303 CHAR data type, 200, 201 COALESCE function, syntax diagram of, 303 CHAR function CODE_VALUE function, syntax diagram of, 304 examples of, 301 column-list, general rules, 246 syntax diagram of, 301 Columns CHAR VARYING data type, 201 column reference, 191 CHAR_LENGTH function default values, 191...
Page 451 68 CREATE TABLE, 69 versioning considerations, 68 CREATE VIEW, 87 CREATE TABLE DROP INDEX, 93 Neoview SQL extensions, 77 DROP MATERIALIZED VIEW, 94 CREATE TABLE AS statement DROP TABLE, 98 considerations for, 73 DROP VIEW, 100 syntax diagram of, 69...
Page 452 DECIMAL, 206 DAYNAME, 326 descriptions of, 205 DAYOFMONTH, 327 INTEGER, 206 DAYOFWEEK, 328 LARGEINT, 206 DAYOFYEAR, 329 NUMERIC, 205 EXTRACT, 342 PICTURE, 206 HOUR, 344 SMALLINT, 206 JULIANTIMESTAMP, 347 fixed length character MINUTE, 363 CHAR, 200 MONTH, 365 NATIONAL CHAR, 201 MONTHNAME, 366 NCHAR, 201 QUARTER, 385...
Page 453 Derived column names examples of, 98 examples of, 191 syntax diagram of, 98 syntax of, 191 DROP TRIGGER statement DETAIL_COST in EXPLAIN output authorization and availability requirements, 99 CPU_TIME, 111, 340 syntax diagram of, 99 IDLETIME, 111, 341 DROP VIEW statement IO_TIME, 111, 340 authorization and availability requirements, 100 MSG_TIME, 111, 340...
Page 454 displayed, 104 operators, 104 Identifiers, 220 OPTIONS ’e’ considerations, 108 IDENTITY columns, 75 OPTIONS ’f’ considerations, 105 IN predicate OPTIONS ’m’ considerations, 110 examples of, 240 OPTIONS ’n’ considerations, 106 limits, 441 output, 106 logical equivalent using ANY, 240 reviewing query execution plans, 104 operand requirements, 240 syntax, 104 syntax diagram of, 239...
Page 455 353 examples of, 347 syntax diagram of, 353 syntax diagram of, 347 Logical name LCASE function Neoview SQL objects, 196 examples of, 349 Logical operators syntax diagram of, 349 NOT, AND, OR, 248 LEFT function search condition use of, 248...
Page 456 201 syntax diagram of, 361 NCHAR VARYING data type, 201 merge from one table to another, 130 Neoview SQL objects, logical names, 196 MERGE INTO statement NOT CASESPECIFIC, 73, 248 syntax diagram of, 129 NOT NULL constraint, 193...
Page 457 examples of, 219 quantified comparison, 242 syntax diagram of, 217 PREPARE statement NVL function availability, 132 examples of, 379 examples of, 132 syntax diagram of, 379 syntax diagram of, 132 Primary key description of, 222 Object names, 196 PRIMARY KEY constraint, 193 Object namespace;Namespace, 196 Privileges Object naming statements, 43...
Page 458 REQUESTS_IN, 106 examples of, 397 reserved table names, 74 syntax diagram of, 397 Reserved words RUNNINGMIN function Neoview SQL, 437 examples of, 397 Resource control, statements for, 42 syntax diagram of, 397 REVOKE EXECUTE statement RUNNINGRANK function authorization and availability requirements, 137...
Page 459 407 examples of, 269 SQL statements SELECT statement use of, 268 ANSI compliant, 37 syntax diagram of, 268 Neoview SQL extensions, 38 Sequence functions SQL value expression, 208 summary of, 283 SQRT function DIFF1, 334 examples of, 408...
Page 460 correlated, 238, 251 dynamic, 164 description of, 250 Transaction control, statements for, 42 inner query, 250 Transaction isolation levels outer query, 250 READ COMMITTED, 36 outer reference, 251 READ UNCOMMITTED, 36 REPEATABLE READ, 36 BETWEEN predicate, 233 SERIALIZABLE, 36 comparison predicate, 235 Transaction management IN predicate, 239 AUTOCOMMIT, effect of, 33, 127...
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 Value expression, 208...

HP Neoview SQL Reference Manual

About this Document

1 Introduction

2 SQL Statements

Quick Links

Related Manuals for HP Neoview SQL

Summary of Contents for HP Neoview SQL