Supplement to the HP Neoview SQL Reference Manual HP Part Number: 546555-001 Published: October 2008 Edition: Neoview Release 2.
© Copyright 2008 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
Table of Contents About This Document.........................................................................................................5 Supported Release Version Updates (RVUs)..........................................................................................5 Intended Audience.................................................................................................................................5 New and Changed Information in This Edition.............................................
List of Tables 1-1 4 Precedence of Data Types During Suitable Key Searches.............................................................
About This Document This document supplements the Neoview SQL Reference Manual for Neoview Release 2.3 Service Pack 2. It describes the volatile table and CREATE VIEW enhancements implemented with this release. Supported Release Version Updates (RVUs) HP Neoview Release 2.3 Service Pack 2 Intended Audience This supplement is intended for database administrators and application programmers who are using Neoview SQL to manage a database on an HP Neoview data warehousing platform.
?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. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines.
Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example: "{" module-name [, module-name]... "}" Item Spacing Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: DAY (datetime-expression) DAY(datetime-expression) If there is no space between two items, spaces are not permitted.
1 Volatile Table Enhancements For Neoview Release 2.3 Service Pack 2, Neoview SQL implements these enhancements for volatile tables: • • Allows users to explicitly specify primary key, STORE BY, and HASH PARTITION BY clauses on columns that contain null values. Does not require that the first column in a volatile table contains not null values and be the primary key.
• • If a suitable column is not located, the volatile table becomes a non-partitioned table with a system defined SYSKEY as its primary key. If a suitable column is located, it becomes the partitioning key where the primary key is suitable_column, SYSKEY. This causes the table to be partitioned while preventing the duplicate key and null-to-non-null errors. Table 1-1 shows the order of precedence, from low to high, of data types when Neoview SQL searches for a suitable key.
--- 1 row(s) inserted. >>insert into t values (null); *** ERROR[8102] The operation is prevented by a unique constraint. --- 0 row(s) inserted.
2 CREATE VIEW Enhancements For Neoview Release 2.3 Service Pack 2, users can specify the isolation level and the ORDER BY clause in the SELECT portion of a CREATE VIEW DDL statement. Previously, Neoview SQL did not allow users to specify isolation level or ORDER BY clauses during the creation of a view. This meant that users had to add these clauses to every DML statement that was executed against the created view.
Explicit User-Specified Isolation Level on SELECT From a View An explicitly-specified access option in the view definition cannot be overwritten, either by another explicit access option in the query or by a session-level isolation setting. After the view definition has been expanded, the isolation level that is closest to the specified table in the query is used.
Or this INSERT statement: Insert into t1 select * from v; In these two examples, the ORDER BY clause is ignored during DML processing because the first appears as part of a derived table and the second as a subquery selects, both created after the view expansion.
3 SQL Functions This chapter describes these two new SQL functions: • “TIMESTAMPADD Function” (page 17) • “TIMESTAMPDIFF Function” (page 18) TIMESTAMPADD Function The TIMESTAMPADD function adds the interval of time specified by interval-ind and num_expr to datetime_expr. If the specified interval is in years, months, or quarters and the resulting date is not a valid date, the day will be rounded down to the last day of the result month.
TIMESTAMPDIFF Function The TIMESTAMPDIFF function returns the integer value for the number of interval-ind units of time between startdate and enddate. If enddate precedes startdate, the return value is negative or zero.
Index A ALLOW_ISOLATION_LEVEL_IN_CREATE_VIEW, 13 ALLOW_ORDER_BY_IN_CREATE_VIEW, 13 C CREATE VIEW enhancement descriptions, 13 D Default attributes ALLOW_ISOLATION_LEVEL_IN_CREATE_VIEW, 13 ALLOW_ORDER_BY_IN_CREATE_VIEW, 13 VOLATILE_TABLE_FIND_SUITABLE_KEY, 9 Documents, related information, 7 I Isolation level guidelines for CREATE VIEW, 13 O ORDER BY clause guidelines for CREATE VIEW, 14 S Suitable keys description, 9 Neoview SQL guidelines for selecting, 9 T TMESTAMPADD function description, 17 exampl
