You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by sv...@apache.org on 2016/01/14 19:18:28 UTC
[24/42] incubator-trafodion git commit: Added SQL Language Elements
chapter.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3bce4129/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
new file mode 100644
index 0000000..b8474ae
--- /dev/null
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
@@ -0,0 +1,4088 @@
+////
+/**
+* @@@ START COPYRIGHT @@@
+*
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing,
+* software distributed under the License is distributed on an
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+* KIND, either express or implied. See the License for the
+* specific language governing permissions and limitations
+* under the License.
+*
+* @@@ END COPYRIGHT @@@
+ */
+////
+
+[[sql_language_elements]]
+= SQL Language Elements
+
+Trafodion 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.
+
+[[_authorization_ids]]
+== Authorization IDs
+
+An authorization ID is used for an authorization operation. Authorization is the process of validating that a
+database user has permission to perform a specified SQL operation. Externally, the authorization ID is a regular
+or delimited case-insensitive identifier that can have a maximum of 128 characters. See
+<<case_insensitive_delimited_identifiers,Case-Insensitive Delimited Identifiers>>.
+Internally, the authorization ID is associated with a 32-bit number that the database generates and uses for
+efficient access and storage.
+
+All authorization IDs share the same namespace. An authorization ID can be a database username or a role name.
+Therefore, a database user and a role cannot share the same name.
+
+An authorization ID can be the PUBLIC authorization ID, which represents all present and future authorization IDs.
+An authorization ID cannot be SYSTEM, which is the implicit grantor of privileges to the creator of bjects.
+
+[[character_sets]]
+== Character Sets
+
+You can specify ISO88591 or UTF8 for a character column definition. The use of UTF8 permits you to store characters
+from many different languages.
+
+<<<
+[[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.
+
+[[column_references]]
+=== Column References
+
+A qualified column name, or column reference, is a column name qualified by the name of the table or view to which
+the column belongs, or by a correlation name.
+
+If a query refers to columns that have the same name but belong to different tables, you must use a qualified column
+name to refer to the columns within the query. You must also refer to a column by a qualified column name if you join
+a table with itself within a query to compare one row of the table with other rows in the same table.
+
+The syntax of a column reference or qualified column name is:
+
+```
+{table-name | view-name | correlation-name}.column-name
+```
+
+If you define a correlation name for a table in the FROM clause of a statement, you must use that correlation name if
+you need to qualify the column name within the statement.
+
+If you do not define an explicit correlation name in the FROM clause, you can qualify the column name with the name of
+the table or view that contains the column. See <<correlation_names,Correlation Names>>.
+
+<<<
+[[derived_column_names]]
+=== Derived Column Names
+
+A derived column is an SQL value expression that appears as an item in the select list of a SELECT statement. An explicit
+name for a derived column is an SQL identifier associated with the derived column. The syntax of a derived column name is:
+
+```
+column-expression [[AS] column-name]
+```
+
+The column expression can simply be a column reference. The expression is optionally followed by the AS keyword and the
+name of the derived column.
+
+If you do not assign a name to derived columns, the headings for unnamed columns in query result tables appear as (EXPR).
+Use the AS clause to assign names that are meaningful to you, which is important if you have more than one derived column
+in your select list.
+
+[[examples_of_derived_column_names]]
+==== Examples of Derived Column Names
+
+These two examples show how to use names for derived columns.
+
+* The first example shows (EXPR) as the column heading of the SELECT result table:
++
+```
+SELECT AVG (salary) FROM persnl.employee; (EXPR)
+
+----------------
+49441.52
+
+--- 1 row(s) selected.
+```
+
+* The second example shows AVERAGE SALARY as the column heading:
++
+```
+SELECT AVG (salary) AS "AVERAGE SALARY"
+FROM persnl.employee; "AVERAGE SALARY"
+
+----------------
+49441.52
+
+--- 1 row(s) selected.
+```
+
+[[column_default_settings]]
+=== Column Default Settings
+
+You can define specific default settings for columns when the table is created. The CREATE TABLE statement defines the
+default settings for columns within tables. The default setting for a column is the value inserted in a row when an INSERT
+statement omits a value for a particular column.
+
+[[constraints]]
+== Constraints
+
+An SQL constraint is an object that protects the integrity of data in a table by specifying a condition that all the
+values in a particular column or set of columns of the table must satisfy.
+
+Trafodion SQL enforces these constraints on SQL tables:
+
+[cols="20%,80%"]
+|===
+| CHECK | Column or table constraint specifying a condition must be satisfied for each row in the table.
+| FOREIGN KEY | Column or table constraint that specifies a referential constraint for the table, declaring that a
+column or set of columns (called a foreign key) in a table can contain only values that match those in a column or
+set of columns in the table specified in the REFERENCES clause.
+| NOT NULL | Column constraint specifying the column cannot contain nulls.
+| PRIMARY KEY | Column or table constraint specifying the column or set of columns as the primary key for the table.
+| UNIQUE | Column or table constraint that specifies that the column or set of columns cannot contain more than
+one occurrence of the same value or set of values.
+|===
+
+[[creating_or_adding_constraints_on_sql_tables]]
+=== Creating or Adding Constraints on SQL Tables
+
+To create constraints on an SQL table when you create the table, use the NOT NULL, UNIQUE, CHECK, FOREIGN KEY, or
+PRIMARY KEY clause of the CREATE TABLE statement.
+
+For more information on Trafodion SQL commands, see <<create_table_statement,CREATE TABLE Statement>> and
+<<alter_table_statement,ALTER TABLE Statement>>.
+
+[[constraint_names]]
+=== Constraint Names
+
+When you create a constraint, you can specify a name for it or allow a name to be generated by Trafodion SQL.
+You can optionally specify both column and table constraint names. Constraint names are ANSI logical names.
+See <<database_object_names,Database Object Names>>. Constraint names are in the same namespace as tables and
+views, so a constraint name cannot have the same name s a table or view.
+
+The name you specify can be fully qualified or not. If you specify the schema parts of the name, they must match
+those parts of the affected table and must be unique among table, view, and constraint names in that schema. If you
+omit the schema portion of the name you specify, Trafodion SQL expands the name by using the schema for the table.
+
+If you do not specify a constraint name, Trafodion SQL constructs an SQL identifier as the name for the constraint
+and qualifies it with the schema of the table. The identifier consists of the table name concatenated with a
+system-generated unique identifier.
+
+[[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
+* Distinguish different uses of the same table
+* Make the query shorter
+
+
+A correlation name can be explicit or implicit.
+
+[[explicit_correlation_names]]
+=== Explicit Correlation Names
+
+An explicit correlation name for a table reference is an SQL identifier associated with the table reference in the FROM
+clause of a SELECT statement. See <<identifiers,Identifiers>>. The correlation name must be unique within the FROM clause.
+For more information about the FROM clause, table references, and correlation names, see <<select_statement,SELECT Statement>>.
+
+The syntax of a correlation name for the different forms of a table reference within a FROM clause is the same:
+
+```
+{table | view | (query-expression)} [AS]correlation-name
+```
+
+A table or view is optionally followed by the AS keyword and the correlation name. A derived table, resulting from the
+evaluation of a query expression, must be followed by the AS keyword and the correlation name. An explicit correlation
+name is known only to the statement in which you define it. You can use the same identifier as a correlation name in
+another statement.
+
+[[implicit_correlation_names]]
+=== Implicit Correlation Names
+
+A table or view reference that has no explicit correlation name has an implicit correlation name. The implicit correlation
+name is the table or view name qualified with the schema names.
+
+You cannot use an implicit correlation name for a reference that has an explicit correlation name within the statement.
+
+[[examples_of_correlation_names]]
+=== Examples of Correlation Names
+
+This query refers to two tables, ORDERS and CUSTOMER, that contain columns named CUSTNUM. In the WHERE clause, one column
+reference is qualified by an implicit correlation name (ORDERS) and the other by an explicit correlation name (C):
+
+```
+SELECT ordernum, custname FROM orders, customer c
+WHERE orders.custnum = c.custnum AND orders.custnum = 543;
+```
+
+[[database_objects]]
+== Database Objects
+
+A database object is an SQL entity that exists in a namespace. SQL statements can access Trafodion SQL database objects.
+The subsections listed below describe these Trafodion SQL database objects.
+
+* <<constraints,Constraints>>
+* <<indexes,Indexes>>
+* <<tables,Tables>>
+* <<views,Views>>
+
+[[ownership]]
+=== Ownership
+
+In Trafodion SQL, the creator of an object owns the object defined in the schema and has all privileges on the object.
+In addition, you can use the GRANT and REVOKE statements to grant access privileges for a table or view to specified users.
+
+For more information, see the <<grant_statement,GRANT Statement>> and <<revoke_statement,REVOKE Statement>>. For
+information on privileges on tables and views, see <<create_table_statement,CREATE TABLE Statement>> and
+<<create_view_statement,CREATE VIEW Statement>>.
+
+[[database_object_names]]
+== Database Object Names
+
+DML statements can refer to Trafodion 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,Database Objects>>.
+
+<<<
+[[logical_names_for_sql_objects]]
+=== Logical Names for SQL Objects
+
+You may refer to an SQL table, view, constraint, library, function, or procedure by using a one-part, two-part, or three-part
+logical name, also called an ANSI name:
+
+```
+catalog-name.schema-name.object-name
+```
+
+In this three-part name, _catalog-name_ is the name of the catalog, which is TRAFODION for Trafodion SQL objects that map to
+HBase tables. _schema-name_ is the name of the schema, and _object-name_ is the simple name of the table, view, constraint,
+library, function, or procedure. Each of the parts is an SQL identifier. See <<identifiers,Identifiers>>.
+
+Trafodion SQL automatically qualifies an object name with a schema name unless you explicitly specify schema names with the
+object name. If you do not set a schema name for the session using a SET SCHEMA statement, the default schema is SEABASE,
+which exists in the TRAFODION catalog. See <<set_schema_statement,SET SCHEMA Statement>>. A one-part name _object-name_ is
+qualified implicitly with the default schema.
+
+You can qualify a column name in a Trafodion SQL statement by using a three-part, two-part, or one-part object name, or a
+correlation name.
+
+[[sql_object_namespaces]]
+=== SQL Object Namespaces
+
+Trafodion SQL objects are organized in a hierarchical manner. Database objects exist in schemas, which are themselves
+contained in a catalog called TRAFODION. A catalog is a collection of schemas. Schema names must be unique within the catalog.
+
+Multiple objects with the same name can exist provided that each belongs to a different namespace. Trafodion SQL supports these
+namespaces:
+
+* Index
+* Functions and procedures
+* Library
+* Schema label
+* Table value object (table, view, constraint)
+
+Objects in one schema can refer to objects in a different schema. Objects of a given namespace are required to have
+unique names within a given schema.
+
+<<<
+[[data_types]]
+== Data Types
+
+Trafodion SQL data types are character, datetime, interval, or numeric (exact or approximate):
+
+[cols="2*"]
+|===
+| <<character_string_data_types,Character String Data Types>> | Fixed-length and variable-length character data types.
+| <<datetime_data_types,Datetime Data Types>> | DATE, TIME, and TIMESTAMP data types.
+| <<interval_data_types,Interval Data Types>> | Year-month intervals (years and months) and day-time intervals (days,
+hours, minutes, seconds, and fractions of a second).
+| <<numeric_data_types_,Numeric Data Types >> | Exact and approximate numeric data types.
+|===
+
+Each column in a table is associated with a data type. You can use the CAST expression to convert data to the data type that you specify. For
+more information, see <<cast_expression,CAST Expression>>.
+
+The following table summarizes the Trafodion SQL data types:
+
+[cols="13%,29%,29%,29%",options="header"]
+|===
+| Type | SQL Designation | Description | Size or Range^1^
+| Fixed-length character | CHAR[ACTER] | Fixed-length character data | 1 to 32707 characters^2^
+| | NCHAR | Fixed-length character data in predefined national character set | 1 to 32707 bytes^3^ ^7^
+| | NATIONAL CHAR[ACTER] | Fixed-length character data in predefined national character set | 1 to 32707 bytes^3^ ^7^
+| Variable-length character | VARCHAR | Variable-length ASCII character string | 1 to 32703 characters^4^
+| | CHAR[ACTER] VARYING | Variable-length ASCII character string | 1 to 32703 characters^4^
+| | NCHAR VARYING | Variable-length ASCII character string | 1 to 32703 bytes^4^ ^8^
+| | NATIONAL CHAR[ACTER] VARYING | Variable-length ASCII character string | 1 to 32703 characters^4^ ^8^
+| Numeric
+| NUMERIC (1,_scale_) to +
+NUMERIC (128,_scale_)
+| Binary number with optional scale; signed or unsigned for 1 to 9 digits
+| 1 to 128 digits; stored: +
+1 to 4 digits in 2 bytes +
+ +
+5 to 9 digits in 4 bytes +
+ +
+10 to 128 digits in 8-64 bytes, depending on precision
+| | SMALLINT | Binary integer; signed or unsigned | 0 to 65535 unsigned, -32768 to +32767 signed; stored in 2 bytes
+| | INTEGER | Binary integer; signed or unsigned | 0 to 4294967295 unsigned, -2147483648 to +2147483647 signed; stored in 4 bytes
+| | LARGEINT | Binary integer; signed only | -2**63 to +(2**63)-1; stored in 8 bytes
+| Numeric (extended numeric precision) | NUMERIC (precision 19 to 128) | Binary integer; signed or unsigned | Stored as multiple chunks of 16-bit integers, with a minimum storage
+length of 8 bytes.
+| Floating point number
+| FLOAT[(_precision_)]
+| Floating point number; precision designates from 1 through 52 bits of precision
+| +/- 2.2250738585072014e-308 through +/-1.7976931348623157e+308; stored in 8 bytes
+| | REAL | Floating point number (32 bits) | +/- 1.17549435e-38 through +/ 3.40282347e+38; stored in 4 bytes
+|
+| DOUBLE PRECISION
+| Floating-point numbers (64 bits) with 1 through 52 bits of precision (52 bits of binary precision and 1 bits of exponent)
+| +/- 2.2250738585072014e-308 through +/-1.7976931348623157e+308; stored in 8 byte
+| Decimal number
+| DECIMAL (1,_scale_) to DECIMAL (18,_scale_)
+| Decimal number with optional scale; stored as ASCII characters; signed or unsigned for 1 to 9 digits; signed required for 10 or more digits
+| 1 to 18 digits. Byte length equals the number of digits. Sign is stored as the first bit of the leftmost byte.
+|
+| Date-Time
+| Point in time, using the Gregorian calendar and a 24 hour clock system. The five supported designations are listed below.
+| YEAR 0001-9999 +
+MONTH 1-12 +
+DAY 1-31 +
+ +
+DAY constrained by MONTH and YEAR +
+ +
+HOUR 0-23 +
+MINUTE 0-59 +
+SECOND 0-59 +
+FRACTION(n) 0-999999 +
+ +
+in which n is the number of significant digits, from 1 to 6
+(default is 6; minimum is 1; maximum is 6). Actual database storage is
+incremental, as follows:
+ +
+YEAR in 2 bytes +
+MONTH in 1 byte +
+DAY in 1 byte +
+HOUR in 1 byte +
+MINUTE in 1
+byte SECOND in 1 byte +
+FRACTION in 4 bytes +
+| | DATE | Date | Format as YYYY-MM-DD; actual database storage size is 4 bytes
+| | TIME | Time of day, 24 hour clock, no time precision. Format as HH:MM:SS; actual database storage size is 3 bytes
+| | TIME (with time precision) | Time of day, 24 hour clock, with time precision | Format as HH:MM:SS.FFFFFF; actual database storage size is 7 bytes
+| | TIMESTAMP | Point in time, no time precision | Format as YYYY-MM-DD HH:MM:SS; actual database storage size is 7 bytes
+| | TIMESTAMP (with time precision) Point in time, with time precision | Format as YYYY-MM-DD HH:MM:SS.FFFFFF; actual database storage size is 1b ytes
+| Interval | INTERVAL | Duration of time; value is in the YEAR/MONTH range or the DAY/HOUR/MINUTE/YEAR/SECOND/FRACTION range
+| YEAR no constraint^6^ +
+MONTH 0-1 +
+DAY no contraint +
+HOUR 0-23 +
+MINUTE 0-59 +
+SECOND 0-59 +
+FRACTION(n) 0-999999 +
+in which n is the number of significant digits (default is 6; minimum is 1; maximum is 6); +
+stored in 2, 4, or 8 bytes depending on number of digits^2^
+|===
+
+* _scale_ is the number of digits to the right of the decimal.
+* _precision_ specifies the allowed number of decimal digits.
+
+
+1. The size of a column that allows null values is 2 bytes larger than the size for the defined data type.
+2. The maximum row size is 32708 bytes, but the actual row size is less than that because of bytes used by
+null indicators, varchar column length indicators, and actual data encoding.
+3. Storage size is the same as that required by CHAR data type but store only half as many characters depending
+on character set selection.
+4. Storage size is reduced by 4 bytes for storage of the varying character length.
+5. The maximum number of digits in an INTERVAL value is 18, including the digits in all INTERVAL fields of the value.
+Any INTERVAL field that is a starting field can have up to 18 digits minus the number of other digits in the INTERVAL value.
+6. The maximum is 32707 if the national character set was specified at installation time to be ISO88591.
+The maximum is 16353 if the national character set was specified at installation time as UTF8.
+7. The maximum is 32703 if the national character set was specified at installation time to be ISO88591.
+The maximum is 16351 if the national character set was specified at installation time as UTF8.
+
+
+<<<
+[[comparable_and_compatible_data_types]]
+=== Comparable and Compatible Data Types
+
+Two data types are comparable if a value of one data type can be compared to a value of the other data type.
+
+Two data types are compatible if a value of one data type can be assigned to a column of the other data type, and if
+columns of the two data types can be combined using arithmetic operations. Compatible data types are also comparable.
+
+Assignment and comparison are the basic operations of Trafodion 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.
+
+The basic rule for both assignment and comparison is that the operands have compatible data types. Data types with
+different character sets cannot be compared without converting one character set to the other. However, the SQL compiler
+will usually generate the necessary code to do this conversion automatically.
+
+[[character_data_types]]
+==== Character Data Types
+
+Values of fixed and variable length character data types of the same character set are all character strings and are
+all mutually comparable and mutually assignable.
+
+When two strings are compared, the comparison is made with a temporary copy of the shorter string that has been padded
+on the right with blanks to have the same length as the longer string.
+
+[[datetime_data_types]]
+==== Datetime Data Types
+
+Values of type datetime are mutually comparable and mutually assignable only if the types have the same datetime fields.
+A DATE, TIME, or TIMESTAMP value can be compared with another value only if the other value has the same data type.
+
+All comparisons are chronological. For example, this predicate is true:
+
+```
+TIMESTAMP '2008-09-28 00:00:00' > TIMESTAMP '2008-06-26 00:00:00'
+```
+
+
+<<<
+[[interval_data_types]]
+==== Interval Data Types
+
+Values of type INTERVAL are mutually comparable and mutually assignable only if the types are either both year-month
+intervals or both day-time intervals.
+
+For example, this predicate is true:
+
+```
+INTERVAL '02-01' YEAR TO MONTH > INTERVAL '00-01' YEAR TO MONTH
+```
+
+The field components of the INTERVAL do not have to be the same. For example, this predicate is also true:
+
+```
+INTERVAL '02-01' YEAR TO MONTH > INTERVAL '01' YEAR
+```
+
+[[numeric_data_types]]
+==== Numeric Data Types
+
+Values of the approximate data types FLOAT, REAL, and DOUBLE PRECISION, and values of the exact data types NUMERIC,
+DECIMAL, INTEGER, SMALLINT, and LARGEINT, are all numbers and are all mutually comparable and mutually assignable.
+
+When an approximate data type value is assigned to a column with exact data type, rounding might occur, and the
+fractional part might be truncated. When an exact data type value is assigned to a column with approximate data type,
+the result might not be identical to the original number.
+
+When two numbers are compared, the comparison is made with a temporary copy of one of the numbers, according to defined
+rules of conversion. For example, if one number is INTEGER and the other is DECIMAL, the comparison is made with a
+temporary copy of the integer converted to a decimal.
+
+[[extended_numeric_precision]]
+===== Extended Numeric Precision
+
+Trafodion SQL provides support for extended numeric precision data type. Extended numeric precision is an extension to
+the NUMERIC(x,y) data type where no theoretical limit exists on precision. It is a software data type, which means that
+the underlying hardware does not support it and all computations are performed by software. Computations using this data
+type may not match the performance of other hardware supported data types.
+
+<<<
+[[considerations_for_extended_numeric_precision_data_type]]
+===== Considerations for Extended NUMERIC Precision Data Type
+
+Consider these points and limitations for extended NUMERIC precision data type:
+
+
+* May cost more than other data type options.
+* Is a software data type.
+* Cannot be compared to data types that are supported by hardware.
+* If your application requires extended NUMERIC precision arithmetic
+expressions, specify the required precision in the table DDL or as
+explicit extended precision type casts of your select list items. The
+default system behavior is to treat user-specified extended precision
+expressions as extended precision values. Conversely, non-user-specified
+(that is, temporary, intermediate) extended precision expressions may
+lose precision. In the following example, the precision appears to lose
+one digit because the system treats the sum of two NUMERIC(18,4) type
+columns as NUMERIC(18,4). NUMERIC(18) is the longest non-extended
+precision numeric type. NUMERIC(19) is the shortest extended precision
+numeric type. The system actually computes the sum of 2 NUMERIC(18,4)
+columns as an extended precision NUMERIC(19,4) sum. But because no
+user-specified extended precision columns exist, the system casts the
+sum back to the user-specified type of NUMERIC(18,4).
++
+```
+CREATE TABLE T(a NUMERIC(18,4), B NUMERIC(18,4));
+INSERT INTO T VALUES (1.1234, 2.1234);
+
+>> SELECT A+B FROM T;
+
+(EXPR)
+--------------
+3.246
+```
++
+If this behavior is not acceptable, you can use one of these options:
++
+** Specify the column type as NUMERIC(19,4). For example, CREATE TABLE T(A NUMERIC(19,4), B NUMERIC(19,4)); or
+** Cast the sum as NUMERIC(19,4). For example, SELECT CAST(A+B AS NUMERIC(19,4)) FROM T; or
+** Use an extended precision literal in the expression. For example, SELECT A+B*1.00000000000000000000 FROM T;.
++
+Note the result for the previous example when changing to NUMERIC(19,4):
++
+```
+SELECT CAST(A+B AS NUMERIC(19,4)) FROM T;
+
+(EXPR)
+------------
+3.2468
+```
++
+When displaying output results in the command interface of a
+client-based tool, casting a select list item to an extended precision
+numeric type is acceptable. However, when retrieving an extended
+precision select list item into an application program's host variable,
+you must first convert the extended precision numeric type into a string
+data type. For example:
++
+```
+SELECT CAST(CAST(A+B AS NUMERIC(19,4)) AS CHAR(24)) FROM T;
+
+(EXPR)
+
+------------
+3.2468
+```
++
+NOTE: An application program can convert an externalized extended
+precision value in string form into a numeric value it can handle. But,
+an application program cannot correctly interpret an extended precision
+value in internal form.
+
+[[rules_for_extended_numeric_precision_data_type]]
+===== Rules for Extended NUMERIC Precision Data Type
+
+These rules apply:
+
+* No limit on maximum precision.
+* Supported in all DDL and DML statements where regular NUMERIC data type is supported.
+* Allowed as part of key columns for hash partitioned tables only.
+* NUMERIC type with precision 10 through 18.
+** UNSIGNED is supported as extended NUMERIC precision data type
+** SIGNED is supported as 64-bit integer
+* CAST function allows conversion between regular NUMERIC and extended NUMERIC precision data type.
+* Parameters in SQL queries support extended NUMERIC precision data type.
+
+<<<
+[[example_of_extended_numeric_precision_data_type]]
+===== Example of Extended NUMERIC Precision Data Type
+
+```
+>>CREATE TABLE t( n NUMERIC(128,30));
+
+--- SQL operation complete.
+
+>>SHOWDDL TABLE t;
+CREATE TABLE SCH.T
+ (
+ N NUMERIC(128, 30) DEFAULT NULL
+ )
+;
+
+--- SQL operation complete.
+
+>>
+```
+
+<<<
+[[character_string_data_types]]
+=== Character String Data Types
+
+Trafodion 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_] [UPSHIFT] [[NOT]CASESPECIFIC]
+| CHAR[ACTER] VARYING(_length_) [CHARACTERS][_char-set_] [UPSHIFT] [[NOT]CASESPECIFIC]
+| VARCHAR(_length_) [CHARACTERS] [_char-set_] [UPSHIFT] [[NOT]CASESPECIFIC]
+| NCHAR [(_length_)] [CHARACTERS] [UPSHIFT] [[NOT]CASESPECIFIC]
+| NCHAR VARYING (_length_) [CHARACTERS] [UPSHIFT] [[NOT]CASESPECIFIC]
+| NATIONAL CHAR[ACTER] [(_length_)] [CHARACTERS] [UPSHIFT] [[NOT]CASESPECIFIC]
+| NATIONAL CHAR[ACTER] VARYING (_length_) [CHARACTERS] [UPSHIFT] [[NOT]CASESPECIFIC]
+```
+
+* `_char-set_` is
++
+```
+CHARACTER SET char-set-name
+```
+
+CHAR, NCHAR, and NATIONAL CHAR are fixed-length character types. CHAR
+VARYING, VARCHAR, NCHAR VARYING and NATIONAL CHAR VARYING are
+varying-length character types.
+
+* `_length_`
++
+is a positive integer that specifies the number of characters allowed in
+the column. You must specify a value for _length_.
+
+* `_char-set-name_`
++
+is the character set name, which can be ISO88591 or UTF8.
+
+* `CHAR[ACTER] [(_length_ [CHARACTERS])] [_char-set_] [UPSHIFT] [[NOT]CASESPECIFIC]`
++
+specifies a column with fixed-length character data.
+
+* `CHAR[ACTER] VARYING (_length_) [CHARACTERS] [_char-set_] [UPSHIFT] [[NOT]CASESPECIFIC]`
++
+specifies a column with varying-length character data. VARYING specifies
+that the number of characters stored in the column can be fewer than the
+_length_.
++
+<<<
++
+Values in a column declared as VARYING can be logically and physically
+shorter than the maximum length, but the maximum internal size of a
+VARYING column is actually four bytes larger than the size required for
+an equivalent column that is not VARYING.
+
+* `VARCHAR (_length_) [_char-set_] [UPSHIFT] [[NOT]CASESPECIFIC]`
++
+specifies a column with varying-length character data. VARCHAR is
+equivalent to data type CHAR[ACTER] VARYING.
+
+* `NCHAR [(_length_)] [UPSHIFT] [[NOT]CASESPECIFIC], NATIONAL CHAR[ACTER] [(_length_)] [UPSHIFT] [[NOT]CASESPECIFIC]`
++
+specifies a column with data in the predefined national character set.
+
+* `NCHAR VARYING [(_length_)] [UPSHIFT] [[NOT]CASESPECIFIC], NATIONAL CHAR[ACTER] VARYING (_length_) [UPSHIFT] [[NOT]CASESPECIFIC]`
++
+specifies a column with varying-length data in the predefined national character set.
+
+[[considerations_for_character_string_data_types]]
+==== Considerations for Character String Data Types
+
+[[difference_between_char_and_varchar]]
+===== Difference Between CHAR and VARCHAR
+
+You can specify a fixed-length character column as CHAR(_n_), where
+_n_ is the number of characters you want to store. However, if you store
+five characters into a column specified as CHAR(10), ten characters are
+stored where the rightmost five characters are blank.
+
+If you do not want to have blanks added to your character string, you
+can specify a variable-length character column as VARCHAR(_n_), where
+_n_ is the maximum number of characters you want to store. If you store
+five characters in a column specified as VARCHAR(10), only the five
+characters are stored logically—without blank padding.
+
+<<<
+[[nchar_columns_in_sql_tables]]
+===== NCHAR Columns in SQL Tables
+
+In Trafodion SQL, the NCHAR type specification is equivalent to:
+
+
+* NATIONAL CHARACTER
+* NATIONAL CHAR
+* CHAR … CHARACTER SET …, where the character set is the character set for NCHAR
+
+Similarly, you can use NCHAR VARYING, NATIONAL CHARACTER VARYING, NATIONAL CHAR
+VARYING, and VARCHAR … CHARACTER SET … , where the character set is
+the character set for NCHAR. The character set for NCHAR is determined
+when Trafodion SQL is installed.
+
+<<<
+[[datetime_data_types]]
+=== Datetime Data Types
+
+A value of datetime data type represents a point in time according to
+the Gregorian calendar and a 24-hour clock in local civil time (LCT). A
+datetime item can represent a date, a time, or a date and time.
+
+When a numeric value is added to or subtracted from a date type, the
+numeric value is automatically CASTed to an INTERVAL DAY value. When a
+numeric value is added to or subtracted from a time type or a timestamp
+type, the numeric value is automatically CASTed to an INTERVAL SECOND
+value. For information on CAST, see <<cast
+expression,CAST
+Expression>>.
+
+Trafodion SQL accepts dates, such as October 5 to 14, 1582, that were
+omitted from the Gregorian calendar. This functionality is a Trafodion
+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)
+```
+
+Trafodion SQL has three datetime data types:
+
+* `_datetime-type_` is:
++
+```
+ DATE
+| TIME [(_time-precision_)]
+| TIMESTAMP [(_timestamp-precision_)]
+```
+
+* `DATE`
++
+specifies a datetime column that contains a date in the external form
+yyyy-mm-dd and stored in four bytes.
+
+* `TIME [(_time-precision_)]`
++
+specifies a datetime column that, without the optional time-precision,
+contains a time in the external form hh:mm:ss and is stored in three
+bytes. _time-precision_ is an unsigned integer that specifies the number
+of digits in the fractional seconds and is stored in four bytes. The
+default for _time-precision_ is 0, and the maximum is 6.
+
+* `TIMESTAMP [(_timestamp-precision_)]`
++
+specifies a datetime column that, without the optional
+_timestamp-precision_, contains a timestamp in the external form
+yyyy-mm-dd hh:mm:ss and is stored in seven bytes. _timestamp-precision_
+is an unsigned integer that specifies the number of digits in the
+fractional seconds and is stored in four bytes. The default for
+_timestamp-precision_ is 6, and the maximum is 6.
+
+
+[[considerations_for_datetime_data_types]]
+==== Considerations for Datetime Data Types
+
+[[datetime_ranges]]
+===== Datetime Ranges
+
+The range of values for the individual fields in a DATE, TIME, or
+TIMESTAMP column is specified as:
+
+
+[cols=","]
+|===
+| _yyyy_ | Year, from 0001 to 9999
+| _mm_ | Month, from 01 to 12
+| _dd_ | Day, from 01 to 31
+| _hh_ | Hour, from 00 to 23
+| _mm_ | Minute, from 00 to 59
+| _ss_ | Second, from 00 to 59
+| _msssss_ | Microsecond, from 000000 to 999999
+|===
+
+When you specify _datetime_value_ (FORMAT ‘string’) in the DML statement
+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.
+
+<<<
+[[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:
++
+```
+YEAR | MONTH | DAY | HOUR | MINUTE | SECOND [(_fractional-precision_)]
+```
+
+* `_single-field_ is:`
++
+```
+_start-field_ | SECOND [(_leading-precision_, _fractional-precision_)]
+```
+
+* `INTERVAL[-] { _start-field_ TO _end-field_ | _single-field_ }`
++
+specifies a column that represents a duration of time as a year-month or
+day-time range or a single-field. The optional sign indicates if this is
+a positive or negative integer. If you omit the sign, it defaults to
+positive.
++
+If the interval is specified as a range, the _start-field_ and
+_end-field_ must be in one of these categories:
+
+* `{YEAR | MONTH | DAY | HOUR | MINUTE} [(_leading-precision_)]`
++
+specifies the _start-field_. A _start-field_ can have a
+_leading-precision_ up to 18 digits (the maximum depends on the number
+of fields in the interval). The _leading-precision_ is the number of digits allowed in the
+_start-field_. The default for _leading-precision_ is 2.
+
+* `YEAR | MONTH | DAY | HOUR | MINUTE | SECOND [(_fractional-precision_)]`
++
+specifies the _end-field_. If the _end-field_ is SECOND, it can have a
+_fractional-precision_ up to 6 digits. The _fractional-precision_ is the
+number of digits of precision after the decimal point. The default for
+_fractional-precision_ is 6.
+
+* `start-field | SECOND [(_leading-precision_, _fractional-precision_)]`
++
+specifies the _single-field_. If the _single-field_ is SECOND, the
+_leading-precision_ is the number of digits of precision before the
+decimal point, and
+the _fractional-precision_ is the number of digits of precision after
+the decimal point. The default for _leading-precision_ is 2, and the
+default for _fractional-precision_
+is 6. The maximum for _leading-precision_ is 18, and the maximum for
+_fractional-precision_ is 6.
+
+
+[[considerations_for_interval_data_types]]
+==== Considerations for Interval Data Types
+
+[[adding_or_subtracting_imprecise_interval_values]]
+===== Adding or Subtracting Imprecise Interval Values
+
+Adding or subtracting an interval that is any multiple of a MONTH, a
+YEAR, or a combination of these may result in a runtime error. For
+example, adding 1 MONTH to January 31, 2009 will result in an error
+because February 31 does not exist and it is not clear whether the user
+would want rounding back to February 28, 2009, rounding up to March 1,
+2009 or perhaps treating the interval 1 MONTH as if it were 30 days
+resulting in an answer of March 2, 2009. Similarly, subtracting 1 YEAR
+from February 29, 2008 will result in an error. See the descriptions for
+the <<add_months_function,ADD_MONTHS Function>>,
+<<date_add_function,DATE_ADD Function>>,
+<<date_sub_function,DATE_SUB Function>> , and <<dateadd_function,DATEADD Function>> for ways
+to add or subtract such intervals without getting errors at runtime.
+
+[[interval_leading_precision]]
+===== Interval Leading Precision
+
+The maximum for the _leading-precision_ depends on the number of fields
+in the interval and on the _fractional-precision_. The maximum is
+computed as:
+
+```
+[[18 - _fractional-precision_ - 2 * (_n_ - 1)]]
+_max-leading-precision_ = 18 - _fractional-precision_ - 2 * (_N_ - 1)
+```
+
+where _N_ is the number of fields in the interval.
+
+For example, the maximum number of digits for the _leading-precision_ in
+a column with data type INTERVAL YEAR TO MONTH is computed as: 18 – 0 –
+2 * (2 – 1) = 16
+
+<<<
+[[interval_ranges]]
+===== Interval Ranges
+
+Within the definition of an interval range (other than a single field),
+the _start-field_ and
+_end-field_ can be any of the specified fields with these restrictions:
+
+* An interval range is either year-month or day-time—that is, if the
+_start-field_ is YEAR, the _end-field_ is MONTH; if the _start-field_ is
+DAY, HOUR, or MINUTE, the _end-field_ is also a time field.
+* The _start-field_ must precede the _end-field_ within the hierarchy:
+YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
+
+[[signed_intervals]]
+===== Signed Intervals
+
+To include a quoted string in a signed interval data type, the sign must
+be outside the quoted string. It can be before the entire literal or
+immediately before the duration enclosed in quotes.
+
+For example, for the interval “minus (5 years 5 months) these formats
+are valid:
+
+```
+INTERVAL - '05-05'YEAR TO MONTH
+
+- INTERVAL '05-05' YEAR TO MONTH
+```
+
+[[overflow_conditions]]
+===== Overflow Conditions
+
+When you insert a fractional value into an INTERVAL data type field, if
+the fractional value is 0 (zero) it does not cause an overflow.
+Inserting value INTERVAL '1.000000' SECOND(6) into a field SECOND(0)
+does not cause a loss of value. Provided that the value fits in the
+target column without a loss of precision, Trafodion SQL does not return
+an overflow error.
+
+However, if the fractional value is > 0, an overflow occurs. Inserting
+value INTERVAL '1.000001' SECOND(6) causes a loss of value.
+
+<<<
+[[numeric_data_types]]
+=== Numeric Data Types
+
+Numeric data types are either exact or approximate. A numeric data type
+is compatible with any other numeric data type, but not with character,
+datetime, or interval data types.
+
+* `_exact-numeric-type_` is:
++
+```
+ NUMERIC [(_precision_ [,_scale_])] [SIGNED|UNSIGNED]
+| SMALLINT [SIGNED|UNSIGNED]
+| INT[EGER] [SIGNED|UNSIGNED]
+| LARGEINT
+| DEC[IMAL] [(_precision_ [,_scale_])] [SIGNED|UNSIGNED]
+```
+
+* `_approximate-numeric-type_` is:
++
+```
+ FLOAT [(_precision_)]
+| REAL
+| DOUBLE PRECISION
+```
++
+Exact numeric data types are types that can represent a value exactly:
+NUMERIC, SMALLINT, INTEGER, LARGEINT, and DECIMAL.
++
+Approximate numeric data types are types that do not necessarily
+represent a value exactly: FLOAT, REAL, and DOUBLE PRECISION.
++
+A column in a Trafodion 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. Trafodion SQL tables can contain only IEEE
+floating-point data.
+
+* `NUMERIC [(_precision_ [,_scale_])] [SIGNED|UNSIGNED]`
++
+specifies an exact numeric column—a two-byte binary number, SIGNED or
+UNSIGNED. _precision_ specifies the total number of digits and cannot
+exceed 128. If _precision_ is between 10 and 18, you must use a signed
+value to obtain the supported hardware data type. If precision is over
+18, you will receive the supported software data type. You will also
+receive the supported software data type if the precision type is
+between 10 and 18, and you specify UNSIGNED. _scale_ specifies the
+number of digits to the right of the decimal point.
++
+The default is NUMERIC (9,0) SIGNED.
+
+* `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 4-byte binary integer, SIGNED or
+UNSIGNED. The column stores integers in the range unsigned 0 to
+4294967295 or signed -2147483648 to +2147483647.
++
+The default is SIGNED.
+
+* `LARGEINT`
++
+specifies an exact numeric column—an 8-byte signed binary integer. The
+column stores integers
+in the range -2^63^ to +2^63^ -1 (approximately 9.223 times 10 to the
+eighteenth power).
+
+* `DEC[IMAL] [(_precision_ [,_scale_])] [SIGNED|UNSIGNED]`
++
+specifies an exact numeric column—a decimal number, SIGNED or
+UNSIGNED,stored as ASCII characters. _precision_ specifies the total
+number of digits and cannot exceed 18. If _precision_ is 10 or more, the
+value must be SIGNED. The sign is stored as the first bit of the
+leftmost byte. _scale_ specifies the number of digits to the right of
+the decimal point.
++
+The default is DECIMAL (9,0) SIGNED.
+
+* `FLOAT [( precision )]`
++
+specifies an approximate numeric column. The column stores
+floating-point numbers and
+designates from 1 through 54 bits of _precision_.
+The range is from +/- 2.2250738585072014e-308 through +/-1.7976931348623157e+308 stored in 8 bytes.
++
+An IEEE FLOAT _precision_ data type is stored as an IEEE DOUBLE, that is, in 8 bytes, with the specified precision.
++
+The default _precision_ is 54.
+
+* `REAL`
++
+specifies a 4-byte approximate numeric column. The column stores 32-bit
+floating-point numbers with 23 bits of binary precision and 8 bits of
+exponent.
++
+The minimum and maximum range is from +/- 1.17549435e-38 through +/ 3.40282347e+38.
+
+<<<
+* `DOUBLE PRECISION`
++
+specifies an 8-byte approximate numeric column.
++
+The column stores 64-bit floating-point numbers and designates from 1
+through 52 bits of _precision_.
++
+An IEEE DOUBLE PRECISION data type is stored in 8 bytes with 52 bits of
+binary precision and 1 bits of exponent. The minimum and maximum range
+is from +/- 2.2250738585072014e-308 through +/-1.7976931348623157e+308.
+
+<<<
+[[expressions]]
+== Expressions
+
+An SQL value expression, called an expression, evaluates to a value.
+Trafodion SQL supports these types of expressions:
+
+
+[cols="30%,70%"]
+|===
+| <<character_value_expressions,Character Value Expressions>> | Operands can be combined with the concatenation operator (||). +
+ +
+Example: `'HOUSTON,' \|\| ' TEXAS'`
+| <<datetime_value_expressions,Datetime Value Expressions>> | Operands can be combined in specific ways with arithmetic operators. +
+ +
+Example: `CURRENT_DATE + INTERVAL '1' DAY`
+| <<interval_value_expressions,Interval Value Expressions>> | Operands can be combined in specific ways with addition and subtraction operators. +
+ +
+Example: `INTERVAL '2' YEAR - INTERVAL '3' MONTH`
+| <<numeric_value_expressions,Numeric Value Expressions>> | Operands can be combined in specific ways with arithmetic operators. +
+ +
+Example: `SALARY * 1.10`
+|===
+
+
+The data type of an expression is the data type of the value of the
+expression.
+
+A value expression can be a character string literal, a numeric literal,
+a dynamic parameter, or a column name that specifies the value of the
+column in a row of a table. A value expression can also include
+functions and scalar subqueries.
+
+<<<
+[[character_value_expressions]]
+=== Character Value Expressions
+
+The operands of a character value expression—called character
+primaries—can be combined with the concatenation operator (||). The data
+type of a character primary is character string.
+
+* `_character-expression_` is:
++
+```
+ character-primary
+| character-expression || character-primary
+```
+
+* `_character-primary_` is:
++
+```
+ character-string-literal
+| column-reference
+| character-type-host-variable
+| dynamic parameter
+| character-value-function
+| aggregate-function
+| sequence-function
+| scalar-subquery
+| CASE-expression
+| CAST-expression
+| (character-expression)
+```
+
+Character (or string) value expressions are built from operands that can be:
+
+* Character string literals
+* Character string functions
+* Column references with character values
+* Dynamic parameters
+* Aggregate functions, sequence functions, scalar subqueries, CASE expressions, or CAST expressions that return character values
+
+<<<
+[[examples_of_character_value_expressions]]
+==== Examples of Character Value Expressions
+
+These are examples of character value expressions:
+
+
+[cols="40%,60%",options="header"]
+|===
+| Expression | Description
+| 'ABILENE' | Character string literal.
+| 'ABILENE ' \|\|' TEXAS' | The concatenation of two string literals.
+| 'ABILENE ' \|\|' TEXAS ' \|\| x’55 53 41' | The concatenation of three string literals to form the literal: 'ABILENE TEXAS USA'
+| 'Customer ' \|\| custname | The concatenation of a string literal with the value in column CUSTNAME.
+| CAST (order_date AS CHAR(10)) | CAST function applied to a DATE value.
+|===
+
+<<<
+[[datetime_value_expressions]]
+=== 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.
+
+* `_datetime-expression_` is:
++
+```
+ datetime-primary
+| interval-expression + datetime-primary
+| datetime-expression + interval-term
+| datetime-expression - interval-term
+```
+
+* `_datetime-primary_` is:
++
+```
+ datetime-literal
+| column-reference
+| datetime-type-host-variable
+| dynamic parameter
+| datetime-value-function
+| aggregate-function
+| sequence-function
+| scalar-subquery
+| CASE-expression
+| CAST-expression
+| (datetime-expression)
+```
+
+* `_interval-term_` is:
++
+```
+ interval-factor
+| numeric-term * interval-factor
+```
+
+* `_interval-factor_` is:
++
+```
+[+|-] interval-primary
+```
+
+<<<
+* `_interval-primary_` is:
++
+```
+ interval-literal
+| column-reference
+| interval-type-host-variable
+| dynamic parameter
+| aggregate-function
+| sequence-function
+| scalar-subquery
+| CASE-expression
+| CAST-expression
+| (interval-expression)
+```
+
+Datetime value expressions are built from operands that can be:
+
+* Interval value expressions
+* Datetime or interval literals
+* Dynamic parameters
+* Column references with datetime or interval values
+* Dynamic parameters
+* Datetime or interval value functions
+* Any aggregate functions, sequence functions, scalar subqueries, CASE
+expressions, or CAST expressions that return datetime or interval values
+
+[[considerations_for_datetime_value_expressions]]
+==== Considerations for Datetime Value Expressions
+
+[[data_type_of_result]]
+===== 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:
+
+[cols="33%l,33%,33%",options="header"]
+|===
+| Datetime Expression | Description | Result Data Type
+| CURRENT_DATE + INTERVAL '1' DAY | The sum of the current date and an interval value of one day. | DATE
+| CURRENT_DATE + est_complete | The sum of the current date and the interval value in column EST_COMPLETE. | DATE
+| ( SELECT ship_timestamp FROM project WHERE projcode=1000) + INTERVAL '07:04' DAY TO HOUR
+| The sum of the ship timestamp for the specified project and an interval value of seven days, four hours.
+| TIMESTAMP
+|===
+
+The datetime primary in the first expression is CURRENT_DATE, a function
+that returns a value with DATE data type. Therefore, the data type of
+the result is DATE.
+
+In the last expression, the datetime primary is this scalar subquery:
+
+```
+( SELECT ship_timestamp FROM project WHERE projcode=1000 )
+```
+
+The preceding subquery returns a value with TIMESTAMP data type.
+Therefore, the data type of the result is TIMESTAMP.
+
+[[restrictions_on_operations_with_datetime_or_interval_operands]]
+===== Restrictions on Operations With Datetime or Interval Operands
+
+You can use datetime and interval operands with arithmetic operators in
+a datetime value expression only in these combinations:
+
+[cols="25%,25%l,25%,25%",options="header"]
+|===
+| Operand 1 | Operator | Operand 2 | Result Type
+| Datetime | + or – | Interval | Datetime
+| Interval | + | Datetime | Datetime
+|===
+
+
+When a numeric value is added to or subtracted from a DATE type, the
+numeric value is automatically CASTed to an INTERVAL DAY value. When a
+numeric value is added to or subtracted from a time type or a timestamp
+type, the numeric value is automatically CASTed to an INTERVAL SECOND
+value. For information on CAST, see <<cast expression,CAST Expression>>.
+For more information on INTERVALS, see
+<<interval_value_expressions,Interval Value Expressions>>
+
+When using these operations, note:
+
+* Adding or subtracting an interval of months to a DATE value results in
+a value of the same day plus or minus the specified number of months.
+Because different months have different lengths, this is an approximate
+result.
+* Datetime and interval arithmetic can yield unexpected results,
+depending on how the fields are used. For example, execution of this
+expression (evaluated left to right) returns an error:
++
+```
+DATE '2007-01-30' + INTERVAL '1' MONTH + INTERVAL '7' DAY
+```
++
+In contrast, this expression (which adds the same values as the previous
+expression, but in a different order) correctly generates the value
+2007-03-06:
++
+```
+DATE '2007-01-30' + INTERVAL '7' DAY + INTERVAL '1' MONTH
+```
+
+You can avoid these unexpected results by using the <<add_months_function,ADD_MONTHS Function>>.
+
+[[examples_of_datetime_value_expressions]]
+==== Examples of Datetime Value Expressions
+
+The PROJECT table consists of five columns that use the data types
+NUMERIC, VARCHAR, DATE, TIMESTAMP, and INTERVAL DAY. Suppose that you
+have inserted values into the PROJECT table. For example:
+
+```
+INSERT INTO persnl.project
+VALUES (1000,'SALT LAKE CITY',DATE '2007-04-10',
+TIMESTAMP '2007-04-21:08:15:00.00',INTERVAL '15' DAY);
+```
+
+The next examples use these values in the PROJECT table:
+
+[cols="4*",options="header"]
+|===
+| PROJCODE | START_DATE | SHIP_TIMESTAMP | EST_COMPLETE
+| 1000 | 2007-04-10 | 2007-04-21 08:15:00.00 | 15
+| 945 | 2007-10-20 | 2007-12-21 08:15:00.00 | 30
+| 920 | 2007-02-21 | 2007-03-12 09:45:00.00 | 20
+| 134 | 2007-1 -20 | 2008-01-01 00:00:00.00 | 30
+|===
+
+* Add an interval value qualified by YEAR to a datetime value:
++
+```
+SELECT start_date + INTERVAL '1' YEAR FROM persnl.project
+WHERE projcode = 1000;
+
+(EXPR)
+----------
+2008-04-10
+
+--- 1 row(s) selected.
+```
+
+* Subtract an interval value qualified by MONTH from a datetime value:
++
+```
+SELECT ship_timestamp - INTERVAL '1' MONTH FROM persnl.project
+WHERE projcode = 134;
+
+(EXPR)
+--------------------------
+2007-12-01 00:00:00.000000
+
+--- 1 row(s) selected.
+```
++
+The result is 2007-12-01 00:00:00.00. The YEAR value is decremented by 1
+because subtracting a month from January 1 causes the date to be in the
+previous year.
+
+<<<
+* Add a column whose value is an interval qualified by DAY to a datetime
+value:
++
+```
+SELECT start_date + est_complete FROM persnl.project
+WHERE projcode = 920;
+
+(EXPR)
+----------
+2007-03-12
+
+--- 1 row(s) selected.
+```
++
+The result of adding 20 days to 2008-02-21 is 2008-03-12. Trafodion SQL
+correctly handles 2008 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 WHERE projcode = 1000;
+
+(EXPR)
+--------------------------
+2008-04-20 16:45:00.000000
+```
++
+The result of subtracting 15 hours and 30 minutes from 2007-04-21
+08:15:00.00 is 2007-04-20 16:45:00.00.
+
+<<<
+[[interval_value_expressions]]
+=== Interval Value Expressions
+
+The operands of an interval value expression can be combined in specific
+ways with addition and subtraction operators. In this syntax diagram,
+the data type of a datetime expression is DATE, TIME, or TIMESTAMP; the
+data type of an interval term or expression is INTERVAL.
+
+* `_interval-expression_` is:
++
+```
+ interval-term
+| interval-expression + interval-term
+| interval-expression - interval-term
+| (datetime-expression - datetime-primary)
+ [interval-qualifier]
+```
+
+* `_interval-term_` is:
++
+```
+ interval-factor
+| interval-term * numeric-factor
+| interval-term / numeric-factor
+| numeric-term * interval-factor
+```
+
+* `_interval-factor_` is:
++
+```
+[+|-] interval-primary
+```
+
+* `_interval-primary_` is:
++
+```
+interval-literal
+| column-reference
+| interval-type-host-variable
+| dynamic-parameter
+| aggregate-function
+| sequence-function
+| scalar-subquery
+| CASE-expression
+| CAST-expression
+| (interval-expression)
+```
+
+* `_numeric-factor_` is:
++
+```
+ [+|-] numeric-primary
+| [+|-] numeric-primary ** numeric-factor
+```
+
+Interval value expressions are built from operands that can be:
+
+* Integers
+* Datetime value expressions
+* Interval literals
+* Column references with datetime or interval values
+* Dynamic parameters
+* Datetime or interval value functions
+* Aggregate functions, sequence functions, scalar subqueries, CASE expressions, or CAST expressions that return interval values
+
+
+For _interval-term_, _datetime-expression_, and _datetime-primary_, see <<datetime_value_[expressions,Datetime Value Expressions>>.
+
+If the interval expression is the difference of two datetime expressions, by default, the result is expressed in the least
+significant unit of measure for that interval. For date differences, the interval is expressed in days. For timestamp differences, the interval
+is expressed in fractional seconds.
+
+If the interval expression is the difference or sum of interval
+operands, the interval qualifiers of the operands are either year-month
+or day-time. If you are updating or inserting a value that is the result
+of adding or subtracting two interval qualifiers, the interval qualifier
+of the result depends on the interval qualifier of the target column.
+
+<<<
+[[considerations_for_interval_value_expressions]]
+==== Considerations for Interval Value Expressions
+
+[[start_and_end_fields]]
+===== Start and End Fields
+
+Within the definition of an interval range, the _start-field_ and
+_end-field_ can be any of the specified fields with these restrictions:
+
+
+* An interval is either year-month or day-time. If the _start-field_ is
+YEAR, the _end-field_ is MONTH; if the _start-field_ is DAY, HOUR, or
+MINUTE, the _end-field_ is also a time field.
+* The _start-field_ must precede the _end-field_ within the hierarchy
+YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
+
+
+Within the definition of an interval expression, the _start-field_ and
+_end-field_ of all operands in the expression must be either year-month
+or day-time.
+
+[[interval_qualifier]]
+===== Interval Qualifier
+
+The rules for determining the interval qualifier of the result
+expression vary. For example, interval value expressions include:
+
+[cols="40%l,40%,20%l",options="header"]
+|===
+| Datetime Expression | Description | Result Data Type
+| CURRENT_DATE - start_date
+| By default, the interval difference between the current date and the value in column START_DATE is expressed
+in days. You are not required to specify the interval qualifier.
+| INTERVAL DAY (12)
+| INTERVAL '3' DAY - INTERVAL '2' DAY | The difference of two interval literals. The result is 1 day. | INTERVAL DAY (3)
+| INTERVAL '3' DAY + INTERVAL '2' DAY | The sum of two interval literals. The result is 5 days. | INTERVAL DAY (3)
+| INTERVAL '2' YEAR - INTERVAL '3' MONTH | The difference of two interval literals. The result is 1 year, 9 months. | INTERVAL YEAR (3) TO MONTH
+|===
+
+
+[[restrictions_on_operations]]
+===== Restrictions on Operations
+
+You can use datetime and interval operands with arithmetic operators in
+an interval value expression only in these combinations:
+
+
+[cols="4*",options="header"]
+|===
+| Operand 1 | Operator | Operand 2 | Result Type
+| Datetime | - | Datetime | Interval
+| Interval | + or – | Interval | Interval
+| Interval | * or / | Numeric | Interval
+| Numeric | * | Interval | Interval
+|===
+
+<<<
+This table lists valid combinations of datetime and interval arithmetic operators, and the data type of the result:
+
+
+[cols="2*",options="header"]
+|===
+| Operands | Result type
+| Date + Interval or Interval + Date | Date
+| Date + Numeric or Numeric + Date | Date
+| Date - Numeric | Date
+| Date – Interval | Date
+| Date – Date | Interval
+| Time + Interval or Interval + Time | Time
+| Time + Numeric or Numeric + Time | Time
+| Time - Number | Time
+| Time – Interval | Time
+| Timestamp + Interval or Interval + Timestamp | Timestamp
+| Timestamp + Numeric or Numeric + Timestamp | Timestamp
+| Timestamp - Numeric | Timestamp
+| Timestamp – Interval | Timestamp
+| year-month Interval + year-month Interval | year-month Interval
+| day-time Interval + day-time Interval | day-time Interval
+| year-month Interval – year-month Interval | year-month Interval
+| day-time Interval – day-time Interval | day-time Interval
+| Time – Time | Interval
+| Timestamp – Timestamp | Interval
+| Interval * Number or Number * Interval | Interval
+| Interval / Number | Interval
+| Interval – Interval or Interval + Interval | Interval
+|===
+
+
+When using these operations, note:
+
+
+* If you subtract a datetime value from another datetime value, both
+values must have the same data type. To get this result, use the CAST
+expression. For example:
++
+```
+CAST (ship_timestamp AS DATE) - start_date
+```
+
+* If you subtract a datetime value from another datetime value, and you
+specify the interval qualifier, you must allow for the maximum number of
+digits in the result for the precision. For example:
++
+```
+(CURRENT_TIMESTAMP - ship_timestamp) DAY(4) TO SECOND(6)
+```
+
+<<<
+* If you are updating a value that is the result of adding or
+subtracting two interval values, an SQL error occurs if the source value
+does not fit into the target column's range of interval fields. For
+example, this expression cannot replace an INTERVAL DAY column:
++
+```
+INTERVAL '1' MONTH + INTERVAL '7' DAY
+```
+
+* If you multiply or divide an interval value by a numeric value
+expression, Trafodion 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. For example, this expression returns the
+value 5-02:
++
+```
+INTERVAL '2-7' YEAR TO MONTH * 2
+```
+
+[[examples_of_interval_value_expressions]]
+==== Examples of Interval Value Expressions
+
+The PROJECT table consists of five columns using the data types NUMERIC,
+VARCHAR, DATE, TIMESTAMP, and INTERVAL DAY. Suppose that you have
+inserted values into the PROJECT table. For example:
+
+```
+INSERT INTO persnl.project
+VALUES (1000,'SALT LAKE CITY',DATE '2007-04-10',
+ TIMESTAMP '2007-04-21:08:15:00.00',INTERVAL '15' DAY);
+```
+
+The next example uses these values in the PROJECT table:
+
+[cols="4*",options="header"]
+|===
+| PROJCODE | START_DATE | SHIP_TIMESTAMP | EST_COMPLETE
+| 1000 | 2007-04-10 | 2007-04-21:08:15:00.0000 | 15
+| 2000 | 2007-06-10 | 2007-07-21:08:30:00.0000 | 30
+| 2500 | 2007-10-10 | 2007-12-21:09:00:00.0000 | 60
+| 3000 | 2007-08-21 | 2007-10-21:08:10:00.0000 | 60
+| 4000 | 2007-09-21 | 2007-10-21:10:15:00.0000 | 30
+| 5000 | 2007-09-28 | 2007-10-28:09:25:01.1 1 | 30
+|===
+
+<<<
+* Suppose that the CURRENT_TIMESTAMP is 2000-01-06 1 :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;
+
+Project/Code (EXPR)
+------------ ---------------------
+ 1000 1355 02:58:57.087086
+ 2000 1264 02:43:57.087086
+ 2500 1111 02:13:57.087086
+ 3000 1172 03:03:57.087086
+ 4000 1172 00:58:57.087086
+ 5000 1165 01:48:55.975986
+
+--- 6 row(s) selected.
+```
+
+<<<
+[[numeric_value_expressions]]
+=== 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
+| numeric-expression - numeric-term
+
+numeric-term is:
+ numeric-factor
+| numeric-term * numeric-factor
+| numeric-term / numeric-factor
+
+numeric-factor is:
+ [+|-] numeric-primary
+| [+|-] numeric-primary ** numeric-factor
+
+numeric-primary is:
+ unsigned-numeric-literal
+| column-reference
+| numeric-type-host-variable
+| dynamic parameter
+| numeric-value-function
+| aggregate-function
+| sequence-function
+| scalar-subquery
+| CASE-expression
+| CAST-expression
+| (numeric-expression)
+```
+
+As shown in the preceding syntax diagram, numeric value expressions are
+built from operands that can be:
+
+
+* Numeric literals
+* Column references with numeric values
+* Dynamic parameters
+* Numeric value functions
+* Aggregate functions, sequence functions, scalar subqueries, CASE expressions, or CAST expressions that return numeric values
+
+<<<
+[[considerations_for_numeric_value_expressions]]
+==== Considerations for Numeric Value Expressions
+
+[[order_of_evaluation]]
+===== Order of Evaluation
+
+1. Expressions within parentheses
+2. Unary operators
+3. Exponentiation
+4. Multiplication and division
+5. Addition and subtraction
+
+
+Operators at the same level are evaluated from left to right for all
+operators except exponentiation. Exponentiation operators at the same
+level are evaluated from right to left. For example,
+`X + Y + Z` is evaluated as `(X + Y) + Z`, whereas `X ** Y ** Z` is evaluated as `X ** (Y ** Z)`.
+
+[[additional_rules_for_arithmetic_operations]]
+===== Additional Rules for Arithmetic Operations
+
+Numeric expressions are evaluated according to these additional rules:
+
+* An expression with a numeric operator evaluates to null if any of the operands is null.
+* Dividing by 0 causes an error.
+* Exponentiation is allowed only with numeric data types. If the first
+operand is 0 (zero), the second operand must be greater than 0, and the
+result is 0. If the second operand is 0, the
+first operand cannot be 0, and the result is 1. If the first operand is
+negative, the second operand must be a value with an exact numeric data
+type and a scale of zero.
+* Exponentiation is subject to rounding error. In general, results of
+exponentiation should be considered approximate.
+
+[[precision_magnitude,_and_scale_of_arithmetic_results]]
+===== Precision, Magnitude, and Scale of Arithmetic Results
+
+The precision, magnitude, and scale are computed during the evaluation
+of an arithmetic expression. Precision is the maximum number of digits
+in the expression. Magnitude is the number of digits to the left of the
+decimal point. Scale is the number of digits to the right of the decimal point.
+
+For example, a column declared as NUMERIC (18, 5) has a precision of 18,
+a magnitude of 13, and a scale of 5. As another example, the literal
+12345.6789 has a precision of 9, a magnitude of 5, and a scale of 4.
+
+The maximum precision for exact numeric data types is 128 digits. The
+maximum precision for the REAL data type is approximately 7 decimal
+digits, and the maximum precision for the DOUBLE PRECISION data type is
+approximately 16 digits.
+
+When Trafodion SQL encounters an arithmetic operator in an expression,
+it applies these rules (with the restriction that if the precision
+becomes greater than 18, the resulting precision is set to 18 and the
+resulting scale is the maximum of 0 and (18- (_resulted precision_ -
+_resulted scale_)).
+
+
+* If the operator is + or -, the resulting scale is the maximum of the
+scales of the operands. The resulting precision is the maximum of the
+magnitudes of the operands, plus the scale of the result, plus 1.
+* If the operator is *, the resulting scale is the sum of the scales of
+the operands. The resulting precision is the sum of the magnitudes of
+the operands and the scale of the result.
+* If the operator is /, the resulting scale is the sum of the scale of
+the numerator and the magnitude of the denominator. The resulting
+magnitude is the sum of the magnitude of the numerator and the scale of
+the denominator.
+
+
+For example, if the numerator is NUMERIC (7, 3) and the denominator is
+NUMERIC (7, 5), the resulting scale is 3 plus 2 (or 5), and the
+resulting magnitude is 4 plus 5 (or 9). The expression result is NUMERIC
+(14, 5).
+
+[[conversion_of_numeric_types_for_arithmetic_operations]]
+===== Conversion of Numeric Types for Arithmetic Operations
+
+Trafodion SQL automatically converts between floating-point numeric
+types (REAL and DOUBLE PRECISION) and other numeric types. All numeric
+values in the expression are first converted to binary, with the maximum
+precision needed anywhere in the evaluation.
+
+
+[[examples_of_numeric_value_expressions]]
+==== Examples of Numeric Value Expressions
+
+
+These are examples of numeric value expressions:
+
+[cols="40%l,60%"]
+|===
+| -57 | Numeric literal.
+| salary * 1.10 | The product of the values in the SALARY column and a numeric literal.
+| unit_price * qty_ordered | The product of the values in the UNIT_PRICE and QTY_ORDERED columns.
+| 12 * (7 - 4) | An expression whose operands are numeric literals.
+| COUNT (DISTINCT city) | Function applied to the values in a column.
+|===
+
+
+<<<
+[[identifiers]]
+== Identifiers
+
+SQL identifiers are names used to identify tables, views, columns, and
+other SQL entities. The two types of identifiers are regular and
+delimited. A delimited identifier is enclosed in double quotes (").
+Case-insensitive delimited identifiers are used only for usernames and
+role names. Either regular, delimited, or case-sensitive delimited
+identifiers can contain up to 128 characters.
+
+[[regular_identifiers]]
+=== Regular Identifiers
+
+Regular identifiers begin with a letter (A through Z and a through z),
+but can also contain digits (0 through 9) or underscore characters (_).
+Regular identifiers are not case-sensitive. You cannot use a reserved
+word as a regular identifier.
+
+[[delimited_identifiers]]
+=== Delimited Identifiers
+
+Delimited identifiers are character strings that appear within double
+quote characters (") and consist of alphanumeric characters, including
+the underscore character (_) or a dash (-). Unlike regular identifiers,
+delimited identifiers are case-sensitive. Trafodion SQL does not support
+spaces or special characters in delimited identifiers given the
+constraints of the underlying HBase filesystem. You can use reserved
+words as delimited identifiers.
+
+[[case_insensitive_delimited_identifiers]]
+=== Case-Insensitive Delimited Identifiers
+
+Case-insensitive delimited identifiers, which are used for usernames and
+roles, are character strings that appear within double quote characters
+(") and consist of alphanumeric characters
+(A through Z and a through z), digits (0 through 9), underscores (_), dashes (-), periods (.), at
+symbols (@), and forward slashes (/), except for the leading at sign (@)
+or leading forward slash (/) character.
+
+Unlike other delimited identifiers, case-insensitive-delimited
+identifiers are case-insensitive. Identifiers are up-shifted before
+being inserted into the SQL metadata. Thus, whether you specify a user's
+name as `"Penelope.Quan@hp.com"`, `"PENELOPE.QUAN@hp.com"`, or
+`"penelope.quan@hp.com"`, the value stored in the metadata will be the
+same: `PENELOPE.QUAN@HP.COM`.
+
+You can use reserved words as case-insensitive delimited identifiers.
+
+<<<
+[[examples_of_identifiers]]
+=== Examples of Identifiers
+
+* These are regular identifiers:
++
+```
+mytable SALES2006
+Employee_Benefits_Selections
+CUSTOMER_BILLING_INFORMATION
+```
++
+Because regular identifiers are case insensitive, SQL treats all these
+identifiers as alternative representations of mytable:
++
+```
+mytable MYTABLE MyTable mYtAbLe
+```
+
+* These are delimited identifiers:
++
+```
+"mytable"
+"table"
+"CUSTOMER-BILLING-INFORMATION"
+```
++
+Because delimited identifiers are case-sensitive, SQL treats the
+identifier "mytable" as different from the identifiers "MYTABLE" or
+"MyTable".
++
+You can use reserved words as delimited identifiers. For example, table
+is not allowed as a regular identifier, but "table" is allowed as a
+delimited identifier.
+
+
+<<<
+[[indexes]]
+== Indexes
+
+An index is an ordered set of pointers to rows of a table. Each index is
+based on the values in one or more columns. Indexes are transparent to
+DML syntax.
+
+A one-to-one correspondence always exists between index rows and base
+table rows.
+
+[[sql_indexes]]
+=== SQL Indexes
+
+Each row in a Trafodion SQL index contains:
+
+* The columns specified in the CREATE INDEX statement
+* The clustering key of the underlying table (the user-defined
+clustering key)
+
+An index name is an SQL identifier. Indexes have their own namespace
+within a schema, so an index name might be the same as a table or
+constraint name. However, no two indexes in a schema can have the same
+name.
+
+See <<create_index_statement,CREATE INDEX Statement>>.
+
+<<<
+[[keys]]
+== Keys
+
+[[clustering_keys]]
+=== Clustering Keys
+
+Every table has a clustering key, which is the set of columns that
+determine the order of the rows on disk. Trafodion SQL organizes records
+of a table or index by using a b-tree based on this clustering key.
+Therefore, the values of the clustering key act as logical row-ids.
+
+[[syskey]]
+=== SYSKEY
+
+When the STORE BY clause is specified with the _key-column-list_ clause,
+an additional column is appended to the _key-column-list_ called the
+SYSKEY.
+
+A SYSKEY (or system-defined clustering key) is a clustering key column
+which is defined by Trafodion SQL rather than by the user. Its type is
+LARGEINT SIGNED. When you insert a record in a table, Trafodion SQL
+automatically generates a value for the SYSKEY column. You cannot supply
+the value.
+
+You cannot specify a SYSKEY at insert time and you cannot update it
+after it has been generated. To see the value of the generated SYSKEY,
+include the SYSKEY column in the select list:
+
+```
+SELECT *, SYSKEY FROM t4;
+```
+
+[[index_keys]]
+=== Index Keys
+
+A one-to-one correspondence always exists between index rows and base
+table rows. Each row in a Trafodion SQL index contains:
+
+
+* The columns specified in the CREATE INDEX statement
+* The clustering (primary) key of the underlying table (the user-defined clustering key)
+
+
+For a nonunique index, the clustering key of the index is composed of
+both items. The clustering key cannot exceed 2048 bytes. Because the
+clustering key includes all the columns in the table, each row is also
+limited to 2048 bytes.
+
+For varying-length character columns, the length referred to in these
+byte limits is the defined column length, not the stored length. (The
+stored length is the expanded length, which includes two extra bytes for
+storing the data length of the item.)
+
+See <<create_index_statement,CREATE INDEX Statement>>.
+
+[[primary_keys]]
+=== Primary Keys
+
+A primary key is the column or set of columns that define the uniqueness
+constraint for a table. The columns cannot contain nulls, and only one
+primary key constraint can exist on a table.
+
+<<<
+[[literals]]
+== Literals
+
+A literal is a constant you can use in an expression, in a statement, or
+as a parameter value. An SQL literal can be one of these data types:
+
+[cols="40%,50%]
+|===
+| <<character_string_literals,Character String Literals>> | A series of characters enclosed in single quotes. +
+ +
+Example: 'Planning'
+| <<datetime_literals,Datetime Literals>> | Begins with keyword DATE, TIME, or TIMESTAMP and followed by a character string. +
+ +
+Example: DATE '1990-01-22'
+| <<interval_literals,Interval Literals>> | Begins with keyword INTERVAL and followed by a character string and an interval qualifier. +
+ +
+Example: INTERVAL '2-7' YEAR TO MONTH
+| <<numeric_literals,Numeric Literals>> | A simple numeric literal (one without an exponent) or a numeric literal in scientific notation. +
+ +
+Example: 99E-2
+|===
+
+[[character_string_literals]]
+=== Character String Literals
+
+A character string literal is a series of characters enclosed in single
+quotes.
+
+You can specify either a string of characters or a set of hexadecimal
+code values representing the characters in the string.
+
+* `[_character-set_ | N]_'string'_
+| [_character-set_ | N] X'_hex-code-value_. . . '
+| [_character-set_ | N]
+X'[_space_. . .]_hex-code-value_[[_space_. . .]_hex-code-value_. . .][_space_. . .]'
+_ character-set_`
++
+specifies the character set ISO88591 or UTF8. The _character-set_
+specification of the string literal should correspond with the character
+set of the column definition, which is either ISO88591 or UTF8. If you
+omit the _character-set specification, Trafodion SQL initially assumes
+the ISO88591 character set if the string literal consists entirely of
+7-bit ASCII characters and UTF8 otherwise. (However, the initial
+assumption will later be changed if the string literal is used in a
+context that requires a character set different from the initial
+assumption.)
+
+* `N`
++
+associates the string literal with the character set of the NATIONAL
+CHARACTER (NCHAR) data type. The character set for NCHAR is determined
+during the installation of Trafodion SQL. This value can be either UTF8
+(the default) or ISO88591.
+
+<<<
+* `'_string_'`
++
+is a series of any input characters enclosed in single quotes. A single
+quote within a string is represented by two single quotes (''). A string
+can have a length of zero if you specify two single quotes ('') without
+a space in between.
+
+* `X`
++
+indicates the hexadecimal string.
+
+* `'_hex-code-value_'`
++
+represents the code value of a character in hexadecimal form enclosed in
+single quotes. It must contain an even number of hexadecimal digits. For
+ISO88591, each value must be two digits long. For UTF8, each value can
+be 2, 4, 6, or 8 hexadecimal digits long. If _hex-code-value_ is
+improperly formatted (for example, it contains an invalid hexadecimal
+digit or an odd number of hexadecimal digits), an error is returned.
+
+* `_space_`
++
+is space sequences that can be added before or after _hex-code-value_
+for readability. The encoding for _space_ must be the TERMINAL_CHARSET
+for an interactive interface and the SQL module character set for the
+programmatic interface.
+
+[[considerations_for_character_string_literals]]
+==== Considerations for Character String Literals
+
+[[using_string_literals]]
+===== Using String Literals
+
+A string literal can be as long as a character column. See
+<<character_string_data_types,Character String Data Types>>.
+
+You can also use string literals in string value expressions—for
+example, in expressions that use the concatenation operator (||) or in
+expressions that use functions returning string values.
+
+When specifying 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, Trafodion SQL returns an error.
+* To specify a single quotation mark within a string literal, use two
+consecutive single quotation marks.
+* To specify a string literal whose length is more than one line,
+separate the literal into several smaller string literals, and use the
+concatenation operator (||) to concatenate them.
+* Case is significant in string literals. Lowercase letters are not
+equivalent to the corresponding uppercase letters.
+* Leading and trailing spaces within a string literal are significant.
+* Alternately, a string whose length is more than one line can be
+written as a literal followed by a space, CR, or tab character, followed
+by another string literal.
+
+[[examples_of_character_string_literals]]
+==== Examples of Character String Literals
+
+* These data type column specifications are shown with examples of
+literals that can be stored in the columns.
++
+[cols="50%l,50%l",options="header"]
+|===
+| Character String Data Type | Character String Literal Example
+| CHAR (12) UPSHIFT | 'PLANNING'
+| VARCHAR (18) | 'NEW YORK'
+|===
+
+* These are string literals:
++
+```
+'This is a string literal.'
+'abc^&*'
+'1234.56'
+'This literal contains '' a single quotation mark.'
+```
+
+* This is a string literal concatenated over three lines:
++
+```
+'This literal is' || '
+in three parts,' ||
+'specified over three lines.'
+```
+
+* This is a hexadecimal string literal representing the VARCHAR pattern
+of the ISO88591 string 'Strauß':
++
+```
+_ISO88591 X'53 74 72 61 75 DF'
+```
+
+<<<
+[[datetime_literals]]
+=== Datetime Literals
+
+A datetime literal is a DATE, TIME, or TIMESTAMP constant you can use in
+an expression, in a statement, or as a parameter value. Datetime
+literals have the same range of valid values as the corresponding
+datetime data types. You cannot use leading or trailing spaces within a
+datetime string (within the single quotes).
+
+A datetime literal begins with the DATE, TIME, or TIMESTAMP keyword and
+can appear in default, USA, or European format.
+
+```
+DATE 'date' | TIME 'time' | TIMESTAMP 'timestamp'
+
+date is:
+ yyyy-mm-dd Default
+| mm/dd/yyyy USA
+| dd.mm.yyyy European
+
+time is:
+ hh:mm:ss.msssss Default
+| hh:mm:ss.msssss [am | pm] USA
+| hh.mm.ss.msssss European
+
+timestamp is:
+ yyyy-mm-dd hh:mm:ss.msssss Default
+| mm/dd/yyyy hh:mm:ss.msssss [am | pm] USA
+| dd.mm.yyyy hh.mm.ss.msssss European
+```
+
+* `_date,time,timestamp_`
++
+specify the datetime literal strings whose component fields are:
++
+[cols="30%l,70%"]
+|===
+| yyyy | Year, from 0001 to 9999
+| mm | Month, from 01 to 12
+| dd | Day, from 01 to 31
+| hh | Hour, from 00 to 23
+| mm | Minute, from 00 to 59
+| ss | Second, from 00 to 59
+| msssss | Microsecond, from 000000 to 999999
+| am | AM or am, indicating time from midnight to before noon
+| pm | PM or pm, indicating time from noon to before midnight
+|===
+
+[[examples_of_datetime_literals]]
+==== Examples of Datetime Literals
+
+* These are DATE literals in default, USA, and European formats, respectively:
++
+```
+DATE '2008-01-22' DATE '01/22/2008' DATE '22.01.2008'
+```
+
+* These are TIME literals in default, USA, and European formats, respectively:
++
+```
+TIME '13:40:05'
+TIME '01:40:05 PM'
+TIME '13.40.05'
+```
+
+* These are TIMESTAMP literals in default, USA, and European formats, respectively:
++
+```
+TIMESTAMP '2008-01-22 13:40:05'
+TIMESTAMP '01/22/2008 01:40:05 PM'
+TIMESTAMP '22.01.2008 13.40.05'
+```
+
+<<<
+[[interval_literals]]
+=== Interval Literals
+
+An interval literal is a constant of data type INTERVAL that represents
+a positive or negative duration of time as a year-month or day-time
+interval; it begins with the keyword INTERVAL optionally preceded or
+followed by a minus sign (for negative duration). You cannot include
+leading or trailing spaces within an interval string (within single
+quotes).
+
+```
+[-]INTERVAL [-]{'year-month' | 'day:time'} interval-qualifier
+
+year-month is:
+ years [-months] | months
+
+day:time is:
+ days [[:]hours [:minutes [:seconds [.fraction]]]]
+| hours [:minutes [:seconds [.fraction]]]
+| minutes [:seconds [.fraction]]
+| seconds [.fraction]
+
+interval-qualifier is:
+ start-field TO end-field | single-field
+
+start-field is:
+ {YEAR | MONTH | DAY | HOUR | MINUTE} [(leading-precision)]
+
+end-field is:
+ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND [(fractional-precision)]
+
+single-field is:
+ start-field | SECOND [(leading-precision,fractional-precision)]
+```
+
+* `_start-field_ TO _end-field_`
++
+must be year-month or day-time.The _start-field_ you specify must
+precede the _end-field_ you specify in the list of field names.
+
+* `{YEAR | MONTH | DAY | HOUR | MINUTE} [(_leading-precision_)]`
++
+specifies the _start-field_. A _start-field_ can have a
+_leading-precision_ up to 18 digits (the maximum depends on the number
+of fields in the interval). The
+_leading-precision_ is the number of digits allowed in the
+_start-field_. The default for _leading-precision_ is 2.
+
+* `YEAR | MONTH | DAY | HOUR | MINUTE | SECOND [(_fractional-precision_)]`
++
+specifies the _end-field_. If the _end-field_ is SECOND, it can have a
+_fractional-precision_ up to 6 digits. The _fractional-precision_ is the
+number
+of digits of precision after the decimal point. The default for
+_fractional-precision_ is 6.
+
+* `_start-field_ | SECOND [(_leading-precision_, _fractional-precision_)]`
++
+specifies the _single-field_. If the _single-field_ is SECOND, the
+_leading-precision_ is the number of digits of precision before the
+decimal point, and the _fractional-precision_ is the number of digits of
+precision after the decimal point.
++
+The default for _leading-precision_ is 2, and the default for
+_fractional-precision_ is 1. The maximum for _leading-precision_ is 18,
+and the maximum for _fractional-precision_ is 6.
++
+See <<interval_data_types,Interval Data Types>> and
+<<interval_value_expressions,Interval Value Expressions>>.
+
+* `'_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:
++
+[cols="15%l,85%"]
+|===
+| years | Unsigned integer that specifies a number of years. _years_ can be up to 18 digits, or 16 digits if _months_
+is the end-field. The maximum for the _leading-precision_ is specified within the interval qualifier by either YEAR(18)
+or YEAR(16) TO MONTH.
+| months | Unsigned integer that specifies a number of months. Used as a starting field, _months_ can have up to 18
+digits. The maximum for the _leading-precision_ is specified by MONTH(18). Used as an ending field, the value of _months_
+must be in the range 0 to 1 .
+| days | Unsigned integer that specifies number of days. _days_ can have up to 18 digits if no end-field exists; 16 digits
+if _hours_ is the end-field; 14 digits if _minutes_ is the end-field; and 13-_f_ digits if _seconds_ is the end-field, where
+f is the _fraction_ less than or equal to 6. These maximums are specified by DAY(18), DAY(16) TO HOUR, DAY(14) TO
+MINUTE, and DAY(13-_f_) TO SECOND(_f_).
+| hours | Unsigned integer that specifies a number of hours. Used as a starting field, _hours_ can have up to 18 digits if
+no end-field exists; 16 digits if _minutes_ is the end-field; and 14-_f_ digits if _seconds_ is the end-field, where f is
+the _fraction_ less than or equal to 6. These maximums are specified by HOUR(18), HOUR(16) TO MINUTE, and HOUR(14-f) TO
+SECOND(_f_). Used as an ending field, the value of _hours_ must be in the range 0 to 23.
+| minutes | Unsigned integer that specifies a number of minutes. Used as a starting field, _minutes_ can have up to 18 digits
+if no end-field exists; and 16-f digits if _seconds_ is the end-field, where _f_ is the _fraction_ less than or equal to 6.
+These maximums are specified by MINUTE(18), and MINUTE(16-_f_) TO SECOND(_f_). Used as an ending field, the value of _minutes_
+must be in the range 0 to 59.
+| seconds | Unsigned integer that specifies a number of seconds. Used as a starting field, _seconds_ can have up to 18 digits,
+minus the number of digits f in the _fraction_ less than or equal to 6. This maximum is specified by SECOND(18-_f_, _f_). The
+value of _seconds_ must be in the range 0 to 59.9(_n_), where _n_ is the number of digits specified for seconds precision.
+| fraction | Unsigned integer that specifies a fraction of a second. When _seconds_ is used as an ending field, _fraction_ is
+limited to the number of digits specified by the _fractional-precision_ field following the SECOND keyword.
+|===
+
+<<<
+[[considerations_for_interval_literals]]
+==== Considerations for Interval Literals
+
+[[length_of_year_month_and_day_time_strings]]
+===== Length of Year-Month and Day-Time Strings
+
+An interval literal can contain a maximum of 18 digits, in the string
+following the INTERVAL keyword, plus a hyphen (-) that separates the
+year-month fields, and colons (:) that separate the day-time fields. You
+can also separate day and hour with a space.
+
+[[examples_of_interval_literals]]
+==== Examples of Interval Literals
+
+[cols="50%l,50%"]
+|===
+| INTERVAL '1' MONTH | Interval of 1 month
+| INTERVAL '7' DAY | Interval of 7 days
+| INTERVAL '2-7' YEAR TO MONTH | Interval of 2 years, 7 months
+| INTERVAL '5:2:15:36.33' DAY TO SECOND(2) | Interval of 5 days, 2 hours, 15 minutes, and 36.33 seconds
+| INTERVAL - '5' DAY | Interval that subtracts 5 days
+| INTERVAL '100' DAY(3) | Interval of 100 days. This example requires an explicit leading
+precision of 3 because the default is 2.
+| INTERVAL '364 23' DAY(3) TO HOUR | Interval of 364 days, 23 hours. The separator for the day and hour
+fields can be a space or a colon.
+|===
+
+<<<
+[[numeric_literals]]
+=== Numeric Literals
+
+A
<TRUNCATED>