http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/da748b4d/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 index e00218d..4bd94e8 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc @@ -1,4088 +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 - -{project-name} 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 name space. An authorization ID can be a database user name 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 objects. - -[[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. - -{project-name} 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 {project-name} 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 {project-name} 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 name space 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, {project-name} SQL expands the name by using the schema for the table. - -If you do not specify a constraint name, {project-name} 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 name space. SQL statements can access {project-name} SQL database objects. -The subsections listed below describe these {project-name} SQL database objects. - -* <<constraints,Constraints>> -* <<indexes,Indexes>> -* <<tables,Tables>> -* <<views,Views>> - -[[ownership]] -=== Ownership - -In {project-name} 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 {project-name} 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 {project-name} 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>>. - -{project-name} 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 {project-name} SQL statement by using a three-part, two-part, or one-part object name, or a -correlation name. - -[[sql_object_namespaces]] -=== SQL Object Namespaces - -{project-name} 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 name space. {project-name} 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 name space are required to have -unique names within a given schema. - -<<< -[[data_types]] -== Data Types - -{project-name} 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 {project-name} 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 1 byte -| 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 {project-name} 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 - -{project-name} 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 - -{project-name} 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 {project-name} 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 {project-name} 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>>. - -{project-name} SQL accepts dates, such as October 5 to 14, 1582, that were -omitted from the Gregorian calendar. This functionality is a {project-name} -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) -``` - -{project-name} 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, {project-name} 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 {project-name} 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. {project-name} 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. -{project-name} 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. {project-name} 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, {project-name} 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 {project-name} 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 - -{project-name} 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 user names 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. {project-name} SQL does not support -spaces or special characters in delimited identifiers given the -constraints of the underlying HBase file system. You can use reserved -words as delimited identifiers. - -[[case_insensitive_delimited_identifiers]] -=== Case-Insensitive Delimited Identifiers - -Case-insensitive delimited identifiers, which are used for user names 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 `"[email protected]"`, `"[email protected]"`, or -`"[email protected]"`, the value stored in the metadata will be the -same: `[email protected]`. - -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 {project-name} 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 name space -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. {project-name} 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 {project-name} SQL rather than by the user. Its type is -LARGEINT SIGNED. When you insert a record in a table, {project-name} 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 {project-name} 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 non-unique 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, {project-name} 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 {project-name} 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, {project-name} 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
<TRUNCATED>
