Google Git
Sign in
apache/trafodion/default/./docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
blob: 2704107cda378a8ad6c5a3d267a1c0030568f23b [file]
////
/**
* @@@ 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).
| <<LOB Data Types,LOB Data Types>> | A set of large object data types used to store large volumes of data,
provides random and piece-wise access to the data, such as BLOB and CLOB.
| <<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="14%,14%,24%,24%,24%",options="header"]
|===
| Category | Type | SQL Designation | Description | Size or Range^1^
.8+| Character String Data Type .3+| Fixed-length character | CHAR[ACTER] | Fixed-length character data | 1 to 200000 characters^2^ ^8^
| NCHAR | Fixed-length character data in predefined national character set | 1 to 200000 bytes^3^ ^7^ ^9^
| NATIONAL CHAR[ACTER] | Fixed-length character data in predefined national character set | 1 to 200000 bytes^3^ ^7^ ^9^
.5+| Variable-length character | VARCHAR | Variable-length ASCII character string | 1 to 200000 characters^4^ ^8^
| VARCHAR2 | Variable-length ASCII character string | 1 to 200000 characters^4^ ^8^
| CHAR[ACTER] VARYING | Variable-length ASCII character string | 1 to 200000 characters^4^ ^8^
| NCHAR VARYING | Variable-length ASCII character string | 1 to 200000 bytes^4^ ^7^ ^9^
| NATIONAL CHAR[ACTER] VARYING | Variable-length ASCII character string | 1 to 200000 characters^4^ ^7^ ^8^
.6+| Datetime Data Types .6+| 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.FFFFFF; actual database storage size is 11 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 Data Types |Interval | INTERVAL | Duration of time; value is in the YEAR/MONTH range or the DAY/HOUR/MINUTE/YEAR/SECOND/FRACTION range
| YEAR no constraint^5^ +
MONTH 0-11 +
DAY no constraint +
HOUR 0-23 +
MINUTE 0-59 +
SECOND 0-59 +
FRACTION(n) 0-999999 +
in which n is the number of significant digits (default is 6; minimum is 1; maximum is 6); +
stored in 2, 4, or 8 bytes depending on number of digits^2^
.2+| LOB Data Types .2+| LOB | BLOB | Binary Large Object .2+| 10G – no limit +
Currently the limit defaults to 10G. This limit can be configured as needed using the CQD `LOB_MAX_SIZE`.
| CLOB | Character Large Object
.10+| Numeric Data Types .5+| 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
| TINYINT | Binary integer; signed or unsigned | 0 to 255 unsigned, -128 to +127 signed; stored in 1 byte
| 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.
.3+| 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 bytes
| 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.
|===
* _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.
8. It defaults to 200000 characters and can be declared explicitly by using the TRAF_MAX_CHARACTER_COL_LENGTH CQD.
9. It defaults to 200000 bytes and can be declared explicitly by using the TRAF_MAX_CHARACTER_COL_LENGTH CQD.
<<<
[[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 [unit])] [char-set] [UPSHIFT] [[NOT]CASESPECIFIC]
| CHAR[ACTER] VARYING(length [unit]) [char-set] [UPSHIFT] [[NOT]CASESPECIFIC]
| VARCHAR(length [unit]) [CHARACTERS] [char-set] [UPSHIFT] [[NOT]CASESPECIFIC]
| VARCHAR2(length [unit]) [CHARACTERS] [char-set] [UPSHIFT] [[NOT]CASESPECIFIC]
| NCHAR [(length)] [UPSHIFT] [[NOT]CASESPECIFIC]
| NCHAR VARYING (length) [UPSHIFT] [[NOT]CASESPECIFIC]
| NATIONAL CHAR[ACTER] [(length)] [UPSHIFT] [[NOT]CASESPECIFIC]
| NATIONAL CHAR[ACTER] VARYING (length) [UPSHIFT] [[NOT]CASESPECIFIC]
```
+
CHAR, NCHAR, and NATIONAL CHAR are fixed-length character types.
+
CHAR VARYING, VARCHAR, VARCHAR2, NCHAR VARYING and NATIONAL CHAR VARYING are
varying-length character types.
* `_length_`
+
is a positive integer that specifies the number of characters (or bytes, see below) allowed in
the column. You must specify a value for _length_.
* `_unit_`
+
is an optional unit of either CHAR[ACTER[S]] or BYTE[S]. The default is CHAR[ACTER[S]]. This unit is meaningful only for UTF8 characters.
A UTF8 character is one to four bytes in length, therefore the storage length of a CHAR column that can hold _n_ UTF8 characters is 4*_n_ bytes.
The same applies to the maximum length of a VARCHAR column.
Specifying the length of UTF8 columns in bytes can lead to significant savings in space and resources.
* `_char-set_` is
+
```
CHARACTER SET char-set-name
```
** `_char-set-name_`
+
is the character set name, which can be ISO88591, UTF8 or UCS2.
*** ISO88591 (ISO 8859-1) is a single-byte character set for US ASCII and Western European language characters.
*** UTF8 (UTF-8) is a variable-length (1 to 4 bytes) encoding of Unicode characters including those in supplementary planes. It is compatible with the US-ASCII character set.
*** UCS2 (UCS-2) is a fixed-length, 2 byte encoding of Unicode characters of the Basic Multilingual Plane (BMP).
Note that, while not strictly part of UCS2, {project-name} also tolerates UTF-16 surrogate pairs in UCS2 columns, but such surrogate pairs are interpreted as two separate characters.
* `CHAR[ACTER] [(_length_ [_unit_])] [_char-set_] [UPSHIFT] [[NOT]CASESPECIFIC]`
+
specifies a column with fixed-length character data.
* `CHAR[ACTER] VARYING (_length_ [_unit_]) [_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_ [_unit_]) [_char-set_] [UPSHIFT] [[NOT]CASESPECIFIC]`
+
specifies a column with varying-length character data. VARCHAR is
equivalent to data type CHAR[ACTER] VARYING.
* `VARCHAR2 (_length_ [_unit_]) [_char-set_] [UPSHIFT] [[NOT]CASESPECIFIC]`
+
specifies a column with varying-length character data. VARCHAR2 is
equivalent to data type CHAR[ACTER] VARYING.
NOTE: Trafodion supports VARCHAR2 as a synonym for VARCHAR. This improves portability from some popular databases.
VARCHAR2 in Trafodion, however, has the standard semantics of VARCHAR. That is, an empty string is a non-null value.
* `NCHAR [(_length_)] [UPSHIFT] [[NOT]CASESPECIFIC], NATIONAL CHAR[ACTER] [(_length_)] [UPSHIFT] [[NOT]CASESPECIFIC]`
+
specifies a column with data in the predefined national character set (UCS2).
* `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 (UCS2).
[[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 &#8230; CHARACTER SET &#8230;, where the character set is the character set for NCHAR
Similarly, you can use NCHAR VARYING, NATIONAL CHARACTER VARYING, NATIONAL CHAR
VARYING, and VARCHAR &#8230; CHARACTER SET &#8230; , 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]
| TINYINT [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.
* `TINYINT [SIGNED|UNSIGNED]`
+
specifies an exact numeric column—a one-byte binary integer, SIGNED or
UNSIGNED. The column stores integers in the range unsigned 0 to 255 or signed
-128 to +127. The default is 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 (&#124;&#124;). +
+
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 &#42;&#42; Z` is evaluated as `X &#42;&#42; (Y &#42;&#42; 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 `"Penelope.Quan@company.com"`, `"PENELOPE.QUAN@company.com"`, or
`"penelope.quan@company.com"`, the value stored in the metadata will be the
same: `PENELOPE.QUAN@COMPANY.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.
<<<
[[identity_column]]
== Identity Column
A identity column is an auto-increment column, which is defined to a column of a table with identity attribute and used to automatically generate increasing or decreasing sequential numeric value for a column with each row insertion into the table.
Identity column, unlike a sequence which works independently of table column, is bound to a table column and can be accessed only by the table column. For more information, see <<create_sequence_statement,CREATE SEQUENCE Statement>>.
```
GENERATED [ALWAYS | BY DEFAULT] AS IDENTITY
[START WITH integer]
[INCREMENT BY integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer]
[CYCLE | NO CYCLE]
[CACHE integer | NO CACHE]
[DATA TYPE]
```
[[syntax_description_of_identity_column]]
=== Syntax Description of Identity Column
* `ALWAYS`
+
Indicates that when a row is inserted to a table, a value will always be generated for the column.
* `BY DEFAULT`
+
Indicates that when a row is inserted to a table, if the value of the column is not specified, then a default value will be assigned for the column. The value can be unique if you place a unique index on the column.
The options above serve the same purposes as they serve when you create a sequence. For more information, see <<create_sequence_statement,CREATE SEQUENCE Statement>>.
[[examples_of_identity_column]]
=== Examples of Identity Column
* Example of `ALWAYS`
+
```
CREATE TABLE identity_employee (
id LARGEINT GENERATED ALWAYS AS IDENTITY,
description VARCHAR(40)
);
SQL>INSERT INTO identity_employee (description) VALUES ('Just DESCRIPTION');
--- 1 row(s) inserted.
SQL>INSERT INTO identity_employee (id, description) VALUES(NULL, 'ID=NULL and DESCRIPTION');
*** ERROR[4122] NULL cannot be assigned to NOT NULL column TRAFODION.SEBASE.IDENTITY_EMPLOYEE.ID.
SQL>INSERT INTO identity_employee (id, description) VALUES(999, 'ID=999 and DESCRIPTION');
*** ERROR[3428] IDENTITY column ID defined as GENERATED ALWAYS cannot accept values specified by the user.
```
* Example of `BY DEFAULT`
+
```
CREATE TABLE identity_employee (
id LARGEINT GENERATED BY DEFAULT AS IDENTITY,
description VARCHAR(40)
);
SQL>INSERT INTO identity_employee (description) VALUES ('Just DESCRIPTION');
--- 1 row(s) inserted.
SQL>INSERT INTO identity_employee (id, description) VALUES(NULL, 'ID=NULL and DESCRIPTION');
*** ERROR[4122] NULL cannot be assigned to NOT NULL column TRAFODION.SEBASE.IDENTITY_EMPLOYEE.ID.
SQL>INSERT INTO identity_employee (id, description) VALUES(999, 'ID=999 and DESCRIPTION');
--- 1 row(s) inserted.
```
<<<
[[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 &#124; MONTH &#124; DAY &#124; HOUR &#124; 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 &#124; MONTH &#124; DAY &#124; HOUR &#124; MINUTE &#124; 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_ &#124; 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_' &#124; '_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 numeric literal represents a numeric value. Numeric literals can be
represented as an exact numeric literal (without an exponent) or as an
approximate numeric literal by using scientific notation (with an
exponent).
```
exact-numeric-literal is:
[+|-]unsigned-integer[.[unsigned-integer]]
| [+|-].unsigned-integer
approximate-numeric-literal is:
mantissa{E|e}exponent
mantissa is:
exact-numeric-literal
exponent is:
[+|-]unsigned-integer
unsigned-integer is:
digit. . .
```
* `_exact-numeric-literal_`
+
is an exact numeric value that includes an optional plus sign (+) or
minus sign (-), up to 128 digits (0 through 9), and an optional period
(.) that indicates a decimal point. Leading zeros do not count toward
the 128-digit limit; trailing zeros do.
+
A numeric literal without a sign is a positive number. An exact numeric
literal that does not include a decimal point is an integer. Every exact
numeric literal has the data type NUMERIC and the minimum precision
required to represent its value.
* `_approximate-numeric-literal_`
+
is an exact numeric literal followed by an exponent expressed as an
uppercase E or lowercase e followed by an optionally signed integer.
+
Numeric values expressed in scientific notation are treated as data type
REAL if they include no more than seven digits before the exponent, but
treated as type DOUBLE PRECISION if they include eight or more digits.
Because of this factor, trailing zeros after a decimal can sometimes
increase the precision of a numeric literal used as a DOUBLE PRECISION
value.
+
<<<
+For example, if XYZ is a table that consists of one DOUBLE PRECISION
column, the inserted value:
+
```
INSERT INTO XYZ VALUES (1.00000000E-10)
```
+
has more precision than:
+
```
INSERT INTO XYZ VALUES (1.0E-10)
```
[[examples_of_numeric_literals]]
==== Examples of Numeric Literals
These are all numeric literals, along with their display format:
[cols="50%l,50%l",options="header"]
|===
| Literal | Display Format
| 477 | 477
| 580.45 | 580.45
| +005 | 5
| -.3175 | -.3175
| 1300000000 | 1300000000
| 99. | 99
| -0.123456789012345678 | -.123456789012345678
| 99E-2 | 9.9000000E-001
| 12.3e+5 | 1.2299999E+006
|===
<<<
[[null]]
== Null
Null is a special symbol, independent of data type, that represents an
unknown. The {project-name} SQL keyword NULL represents null. Null indicates
that an item has no value. For sorting purposes, null is greater than
all other values. You cannot store null in a column by using INSERT or
UPDATE, unless the column allows null.
A column that allows null can be null at any row position. A nullable
column has extra bytes associated with it in each row. A special value
stored in these bytes indicates that the column has null for that row.
[[using_null_versus_default_values]]
=== Using Null Versus Default Values
Various scenarios exist in which a row in a table might contain no value
for a specific column. For example:
* A database of telemarketing contacts might have null AGE fields if
contacts did not provide their age.
* An order record might have a DATE_SHIPPED column empty until the order
is actually shipped.
* An employee record for an international employee might not have a
social security number.
You allow null in a column when you want to convey that a value in the
column is unknown (such as the age of a telemarketing contact) or not
applicable (such as the social security number of an international
employee).
In deciding whether to allow nulls or use defaults, also note:
* Nulls are not the same as blanks. Two blanks can be compared and found
equal, while the result of a comparison of two nulls is indeterminate.
* Nulls are not the same as zeros. Zeros can participate in arithmetic
operations, while nulls are excluded from any arithmetic operation.
[[defining_columns_that_allow_or_prohibit_null]]
=== Defining Columns That Allow or Prohibit Null
The CREATE TABLE and ALTER TABLE statements define the attributes for
columns within tables. A column allows nulls unless the column
definition includes the NOT NULL clause or the column is part of the
primary key of the table.
Null is the default for a column (other than NOT NULL) unless the column
definition includes a DEFAULT clause (other than DEFAULT NULL) or the NO
DEFAULT clause. The default value for a column is the value {project-name}
SQL inserts in a row when an INSERT statement omits a value for a
particular column.
[[null_in_distinct_group_by_and_order_by_clauses]]
==== Null in DISTINCT, GROUP BY, and ORDER BY Clauses
In evaluating the DISTINCT, GROUP BY, and ORDER BY clauses, {project-name}
SQL considers all nulls to be equal. Additional considerations for these
clauses are:
[cols="15%l,85%"]
|===
| DISTINCT | Nulls are considered duplicates; therefore, a result has at most one null.
| GROUP BY | The result has at most one null group.
| ORDER BY | Nulls are considered greater than non-null values.
|===
[[null-and-expression-evaluation-comparison]]
==== Null and Expression Evaluation Comparison
[cols="3*",options="header"]
|===
| Expression Type | Condition | Result
| Boolean operators (AND, OR, NOT) | Either operand is null. | For AND, the result is null. For OR, the result is true if the other
operand is true, or null if the other operand is null or false. For NOT, the result is null.
| Arithmetic operators | Either or both operands are null. | The result is null.
| NULL predicate | The operand is null. | The result is true.
| Aggregate (or set) functions (except COUNT) | Some rows have null columns. The function is evaluated after eliminating nulls. | The result is null if set is empty.
| COUNT(*) | The function does not eliminate nulls. | The result is the number of rows in the table whether or not the rows are null.
| COUNT COUNT DISTINCT | The function is evaluated after eliminating nulls. | The result is zero if set is empty.
| Comparison: =, <>, <, >, <&#61;, >&#61;, LIKE | Either operand is null. | The result is null.
| IN predicate | Some expressions in the IN value list are null. | The result is null if all of the expressions are null.
| Subquery | No rows are returned. | The result is null.
|===
<<<
[[predicates]]
== Predicates
A predicate determines an answer to a question about a value or group of
values. A predicate returns true, false, or, if the question cannot be
answered, unknown. Use predicates within search conditions to choose
rows from tables or views.
[cols="30%,70%"]
|===
| <<between_predicate,BETWEEN Predicate>> | Determines whether a sequence of values is within a range of sequences of values.
| <<comparison_predicates,Comparison Predicates>> +
( =, <>, <, >, <&#61;, >&#61; ) | Compares the values of sequences of expressions, or compares the values
of sequences of row values that are the result of row subqueries.
| <<exists_predicate,EXISTS Predicate>> | Determines whether any rows are selected by a subquery. If the subquery
finds at least one row that satisfies its search condition, the
predicate evaluates to true. Otherwise, if the result table of the
subquery is empty, the predicate is false.
| <<in_predicate,IN Predicate>> | Determines if a sequence of values is equal to any of the sequences of
values in a list of sequences.
| <<like_predicate,LIKE Predicate>> | Searches for character strings that match a pattern.
| <<regexp_predicate,REGEXP Predicate>> | Searches for character strings that match an extended regular expression.
| <<null_predicate,NULL Predicate>> | Determines whether all the values in a sequence of values are null.
| <<quantified_comparison_predicates,Quantified Comparison Predicates>> +
(ALL, ANY, SOME ) | Compares the values of sequences of expressions to the values in each
row selected by a table subquery. The comparison is quantified by ALL,
ANY, or .
|===
See the individual entry for a predicate or predicate group.
[[between_predicate]]
=== BETWEEN Predicate
The BETWEEN predicate determines whether a sequence of values is within
a range of sequences of values.
```
row-value-constructor [NOT] BETWEEN
row-value-constructor AND row-value-constructor
row-value-constructor is:
(expression [,expression ]...)
| row-subquery
```
* `_row-value-constructor_`
+
specifies an operand of the BETWEEN predicate. The three operands can be
either of:
+
** (_expression_ [,_expression_ ]&#8230;)
+
is a sequence of SQL value expressions, separated by commas and enclosed
in parentheses.
_expression_ cannot include an aggregate function unless _expression_ is
in a HAVING clause. _expression_ can be a scalar subquery (a subquery
that returns a single row consisting of a single column). See
<<expressions,Expressions>>.
+
<<<
+
** `_row-subquery_`
+
is a subquery that returns a single row (consisting of a sequence of
values). See <<subquery,Subquery>> .
+
The three _row-value-constructors_ specified in a BETWEEN predicate must
contain the same number of elements. That is, the number of value
expressions in each list, or the number of values returned by a row
subquery, must be the same.
+
The data types of the respective values of the three
_row-value-constructors_ must be comparable. Respective values are
values with the same ordinal position in the two lists. See
<<comparable_and_compatible_data_types,Comparable and Compatible Data Types>>.
[[considerations_for_between]]
==== Considerations for BETWEEN
[[logical_equivalents_using_and_and_or]]
===== Logical Equivalents Using AND and OR
The predicate _expr1_ BETWEEN _expr2_ AND _expr3_ is true if and only if
this condition is true:
```
expr2 <= expr1 AND expr1 <= expr3
```
The predicate _expr1_ NOT BETWEEN _expr2_ AND _expr3_ is true if and
only if this condition is true:
```
expr2 > expr1 OR expr1 > expr3
```
[[descending_columns_in_keys]]
===== Descending Columns in Keys
If a clause specifies a column in a key BETWEEN _expr2_ and _expr3_,
_expr3_ must be greater than _expr2_ even if the column is specified as DESCENDING within its table
definition.
[[examples_of_between]]
==== Examples of BETWEEN
* This predicate is true if the total price of the units in inventory is
in the range from $1,000 to $10,000:
+
```
qty_on_hand * price
BETWEEN 1000.00 AND 10000.00
```
* This predicate is true if the part cost is less than $5 or more than $800:
+
```
partcost NOT BETWEEN 5.00 AND 800.00
```
* This BETWEEN predicate selects the part number 6400:
+
```
SELECT * FROM partsupp
WHERE partnum BETWEEN 6400 AND 6700
AND partcost > 300.00;
Part/Num Supp/Num Part/Cost Qty/Rec
-------- -------- ------------ ----------
6400 1 390.00 50
6401 2 500.00 20
6401 3 480.00 38
--- 3 row(s) selected.
```
* Find names between Jody Selby and Gene Wright:
+
```
(last_name, first_name) BETWEEN
('SELBY', 'JODY') AND ('WRIGHT', 'GENE')
```
+
The name Barbara Swift would meet the criteria; the name Mike Wright would not.
+
```
SELECT empnum, first_name, last_name
FROM persnl.employee
WHERE (last_name, first_name) BETWEEN
('SELBY', 'JODY') AND ('WRIGHT', 'GENE');
EMPNUM FIRST_NAME LAST_NAME
------ --------------- --------------------
43 PAUL WINTER
72 GLENN THOMAS
74 JOHN WALKER
...
--- 15 row(s) selected.
```
<<<
[[comparison_predicates]]
=== Comparison Predicates
A comparison predicate compares the values of sequences of expressions,
or the values of sequences of row values that are the result of row
subqueries.
```
row-value-constructor comparison-op row-value-constructor
```
* `_comparison-op_` is:
+
```
= Equal
| <> Not equal
| < Less than
| > Greater than
| <= Less than or equal to
| >= Greater than or equal to
```
* `_row-value-constructor_` is:
+
```
(expression [,expression]...)
| row-subquery
```
* `_row-value-constructor_`
+
specifies an operand of a comparison predicate. The two operands can be
either of these:
** `(_expression_ [,_expression_ ]&#8230;)`
+
is a sequence of SQL value expressions, separated by commas and enclosed
in parentheses.
+
_expression_ cannot include an aggregate function unless expression is
in a HAVING clause. _expression_ can be a scalar subquery (a subquery
that returns a single row consisting of a single column). See
<<expressions,Expressions>>.
** `_row-subquery_`
+
is a subquery that returns a single row (consisting of a sequence of
values). See <<subquery,Subquery>> .
* The two _row-value-constructors_ must contain the same number of
elements. That is, the number of value expressions in each list, or the
number of values returned by a row subquery, must be the same.
+
The data types of the respective values of the two
_row-value-constructors_ must be comparable. (Respective values are
values with the same ordinal position in the two lists.) See
<<comparable_and_compatible_data_types,Comparable and Compatible Data Types>>.
[[considerations_for_comparison_predicates]]
==== Considerations for Comparison Predicates
[[when_a_comparison_predicate_is_true]]
===== When a Comparison Predicate Is True
{project-name} SQL determines whether a relationship is true or false by
comparing values in corresponding positions in sequence, until it finds
the first non-equal pair.
You cannot use a comparison predicate in a WHERE or HAVING clause to
compare row value constructors when the value expressions in one row
value constructor are equal to null. Use the IS NULL predicate instead.
Suppose that two rows with multiple components exist, X and Y:
```
X=(X1,X2,...,Xn), Y=(Y1,Y2,...,Yn).
```
Predicate X=Y is true if for all i=1,&#8230;,n: Xi=Yi. For this predicate,
{project-name} SQL must look through all values. Predicate X = Y is false if
for some i Xi<>Yi. When SQL finds non-equal components, it stops and does
not look at remaining components.
Predicate X<>Y is true if X=Y is false. If X1<>Y1, {project-name} SQL does
not look at all components. It stops and returns a value of false for
the X=Y predicate and a value of true for the X<>Y predicate. Predicate
X<>Y is false if X=Y is true, or for all i=1,&#8230;,n: Xi=Yi. In this
situation, {project-name} SQL must look through all components.
Predicate X>Y is true if for some index m Xm>Ym and for all i=1,&#8230;,m-1:
Xi=Yi. {project-name} SQL does not look through all components. It stops when
it finds the first nonequal components, Xm<>Ym. If Xm>Ym, the predicate
is true. Otherwise the predicate is false. The predicate is also false
if all components are equal, or X=Y.
Predicate X>&#61;Y is true if X>Y is true or X=Y is true. In this scenario,
{project-name} SQL might look through all components and return true if they
are all equal. It stops at the first nonequal components, Xm<>Ym. If
Xm>Ym, the predicate is true. Otherwise, it is false.
Predicate X<Y is true if for some index m Xm<Ym, and for all i=1,&#8230;,m-1:
Xi=Yi. {project-name} SQL does not look through all components. It stops when
it finds the first nonequal components Xm<>Ym. If Xm<Ym, the predicate
is true. Otherwise, the predicate is false. The predicate is also false
if all components are equal, or X=Y.
Predicate X<&#61;Y is true if X<Y is true or X=Y is true. In this scenario,
{project-name} SQL might need to look through all components and return true
if they are all equal. It stops at the first non-equal components,
Xm<>Ym. If Xm<Ym, the predicate is true. Otherwise, it is false.
[[comparing_character_data]]
===== Comparing Character Data
For comparisons between character strings of different lengths, the
shorter string is padded on the right with spaces (HEX 20) until it is
the length of the longer string. Both fixed-length and
variable-length strings are padded in this way.
For example, {project-name} SQL considers the string ‘JOE’ equal to a value
JOE stored in a column of data type CHAR or VARCHAR of width three or
more. Similarly, {project-name} SQL considers a value JOE stored in any
column of the CHAR data type equal to the value JOE stored in any column
of the VARCHAR data type.
Two strings are equal if all characters in the same ordinal position are
equal. Lowercase and uppercase letters are not considered equivalent.
[[comparing_numeric_data]]
===== Comparing Numeric Data
Before evaluation, all numeric values in an expression are first
converted to the maximum precision needed anywhere in the expression.
[[comparing_interval_data]]
===== Comparing Interval Data
For comparisons of INTERVAL values, {project-name} SQL first converts the
intervals to a common unit.
If no common unit exists, {project-name} SQL reports an error. Two INTERVAL
values must be both year-month intervals or both day-time intervals.
[[comparing_multiple_values]]
===== Comparing Multiple Values
Use multi-value predicates whenever possible; they are generally more
efficient than equivalent conditions without multi-value predicates.
[[examples_of_comparison_predicates]]
==== Examples of Comparison Predicates
* This predicate is true if the customer number is equal to 3210:
+
```
custnum = 3210
```
* This predicate is true if the salary is greater than the average
salary of all employees:
+
```
salary > (SELECT AVG (salary) FROM persnl.employee);
```
* This predicate is true if the customer name is BACIGALUPI:
+
```
custname = 'BACIGALUPI'
```
* This predicate evaluates to unknown for any rows in either CUSTOMER or
ORDERS that contain null in the CUSTNUM column:
+
```
customer.custnum > orders.custnum
```
* This predicate returns information about anyone whose name follows
MOSS, DUNCAN in a list arranged alphabetically by last name and, for the
same last name, alphabetically by first name:
+
```
(last_name, first_name) > ('MOSS', 'DUNCAN')
```
+
REEVES, ANNE meets this criteria, but MOSS, ANNE does not.
+
This multi-value predicate is equivalent to this condition with three
comparison predicates:
+
```
(last_name > 'MOSS') OR
(last_name = 'MOSS' AND first_name > 'DUNCAN')
```
* Compare two datetime values START_DATE and the result of the
CURRENT_DATE function:
+
```
START_DATE < CURRENT_DATE
```
* Compare two datetime values START_DATE and SHIP_TIMESTAMP:
+
```
CAST (start_date AS TIMESTAMP) < ship_timestamp
```
* Compare two INTERVAL values:
+
```
JOB1_TIME < JOB2_TIME
```
+
Suppose that JOB1_TIME, defined as INTERVAL DAY TO MINUTE, is 2 days 3
hours, and JOB2_TIME, defined as INTERVAL DAY TO HOUR, is 3 days.
+
To evaluate the predicate, {project-name} SQL converts the two INTERVAL
values to MINUTE. The comparison predicate is true.
* The next examples contain a subquery in a comparison predicate. Each
subquery operates on a separate logical copy of the EMPLOYEE table.
+
The processing sequence is outer to inner. A row selected by an outer
query allows an inner query to be evaluated, and a single value is
returned. The next inner query is evaluated when it receives a value
from its outer query.
+
Find all employees whose salary is greater than the maximum salary of
employees in department 1500:
+
```
SELECT
first_name, last_name, deptnum, salary
FROM persnl.employee
WHERE salary >
(SELECT MAX (salary) FROM persnl.employee WHERE deptnum = 1500);
FIRST_NAME LAST_NAME DEPTNUM SALARY
--------------- -------------------- ------- -----------
ROGER GREEN 9000 175500.00
KATHRYN HALL 4000 96000.00
RACHEL MCKAY 4000 118000.00
THOMAS RUDLOFF 2000 138000.40
JANE RAYMOND 3000 136000.00
JERRY HOWARD 1000 137000.10
--- 6 row(s) selected.
```
Find all employees from other departments whose salary is less than the
minimum salary of employees (not in department 1500) that have a salary
greater than the average salary for department 1500:
+
```
SELECT first_name, last_name, deptnum, salary
FROM persnl.employee
WHERE deptnum <> 1500
AND salary < (SELECT MIN (salary)
FROM persnl.employee WHERE deptnum <> 1500
AND salary > (SELECT AVG (salary) FROM persnl.employee WHERE deptnum = 1500));
FIRST_NAME LAST_NAME DEPTNUM SALARY
--------------- -------------------- ------- -----------
JESSICA CRINER 3500 39500.00
ALAN TERRY 3000 39500.00
DINAH CLARK 9000 37000.00
BILL WINN 2000 32000.00
MIRIAM KING 2500 18000.00
...
--- 35 row(s) selected.
```
+
<<<
+
The first subquery of this query determines the minimum salary of
employees from other departments whose salary is greater than the
average salary for department 1500. The main query then finds the names
of employees who are not in department 1500 and whose salary is less
than the minimum salary determined by the first subquery.
[[exists_predicate]]
=== EXISTS Predicate
The EXISTS predicate determines whether any rows are selected by a
subquery. If the subquery finds at least one row that satisfies its
search condition, the predicate evaluates to true. Otherwise, if the
result table of the subquery is empty, the predicate is false.
```
[NOT] EXISTS subquery
```
* `_subquery_`
+
specifies the operand of the predicate. A _subquery_ is a query
expression enclosed in parentheses. An EXISTS _subquery_ is typically
correlated with an outer query. See <<subquery,Subquery>> .
[[examples_of_exists]]
==== Examples of EXISTS
* Find locations of employees with job code 300:
+
```
SELECT deptnum, location FROM persnl.dept D WHERE EXISTS
(SELECT jobcode FROM persnl.employee E
WHERE D.deptnum = E.deptnum AND jobcode = 300);
DEPTNUM LOCATION
------- -------------
3000 NEW YORK
3100 TORONTO
3200 FRANKFURT
3300 LONDON
3500 HONG KONG
--- 5 row(s) selected.
```
+
In the preceding example, the EXISTS predicate contains a subquery that
determines which locations have employees with job code 300. The
subquery depends on the value of D.DEPTNUM from the outer query and must
be evaluated for each row of the result table where D.DEPTNUM equals
E.DEPTNUM. The column D.DEPTNUM is an example of an outer reference.
* Search for departments that have no employees with job code 420:
+
```
SELECT deptname FROM persnl.dept D WHERE NOT EXISTS
(SELECT jobcode FROM persnl.employee E
WHERE D.deptnum = E.deptnum AND jobcode = 420);
DEPTNAME
------------
FINANCE
PERSONNEL
INVENTORY
...
--- 11 row(s) selected.
```
* Search for parts with less than 20 units in the inventory:
+
```
SELECT partnum, suppnum FROM invent.partsupp PS WHERE EXISTS
(SELECT partnum FROM invent.partloc PL
WHERE PS.partnum = PL.partnum AND qty_on_hand < 20);
PARTNUM SUPPNUM
------- -------
212 1
212 3
2001 1
2003 2
...
--- 18 row(s) selected.
```
<<<
[[in_predicate]]
=== IN Predicate
The IN predicate determines if a sequence of values is equal to any of
the sequences of values in a list of sequences. The NOT operator
reverses its truth value. For example, if IN is true, NOT IN is false.
```
row-value-constructor
[NOT] IN {table-subquery | in-value-list}
row-value-constructor is:
(expression [,expression]...)
| row-subquery
```
* `_in-value-list_` is:
+
```
(expression [,expression_]...)
```
* `_row-value-constructor_`
+
specifies the first operand of the IN predicate. The first operand can
be either of:
+
```
(expression [,expression ]...)
```
+
is a sequence of SQL value expressions, separated by commas and enclosed
in parentheses.
+
_expression_ cannot include an aggregate function unless expression is
in a HAVING clause. _expression_ can be a scalar subquery (a subquery
that returns a single row consisting of a single column). See
<<expressions,Expressions>>.
* `_row-subquery_`
+
is a subquery that returns a single row (consisting of a sequence of
values). See <<subquery,Subquery>> .
* `_table-subquery_`
+
is a subquery that returns a table (consisting of rows of columns). The
table specifies rows of values to be compared with the row of values
specified by the _row-value-constructor_. The number of values of the
_row-value-constructor_ must be equal to the number of columns in the
result table of the _table-subquery_, and the data types of the values
must be comparable.
* `_in-value-list_`
+
is a sequence of SQL value expressions, separated by commas and enclosed
in parentheses. _expression_ cannot include an aggregate function
defined on a column. _expression_ can be a scalar subquery (a subquery
that returns a single row consisting of a single column). In this case,
the result of the _row-value-constructor_ is a single value. The data
types of the values must be comparable. The number of expressions in the
_in-value-list_ can have at least 5000 expressions.
<<<
[[considerations_for_in]]
==== Considerations for IN
[[logical_equivalent_using_any_or_some]]
===== Logical Equivalent Using ANY (or SOME)
The predicate _expr_ IN (_expr1_, _expr2_, &#8230; ) is true if and only
if the following predicate is true:
```
expr = ANY (expr1, expr2, ... )
```
[[in_predicate_results]]
===== IN Predicate Results
The IN predicate is true if and only if either of these is true:
* The result of the _row-value-constructor_ (a row or sequence of
values) is equal to any row of column values specified by
_table-subquery_.
A table subquery is a query expression and can be specified as a form of
a simple table; for example, as the VALUES keyword followed by a list of
row values. See <<select_statement,SELECT Statement>>.
* The result of the _row-value-constructor_ (a single value) is equal to
any of the values specified by the list of expressions
_in-value-list_.
+
In this case, it is helpful to think of the list of expressions as a
one-column table—a special case of a table subquery. The degree of the
row value constructor and the degree of the list of expressions are both
one.
[[comparing_character_data]]
===== Comparing Character Data
Two strings are equal if all characters in the same ordinal position are
equal. Lowercase and uppercase letters are not considered equivalent.
For comparisons between character strings of different lengths, the
shorter string is padded on the right with spaces (HEX 20) until it is
the length of the longer string. Both fixed-length and varying-length
strings are padded in this way.
For example, {project-name} SQL considers the string ‘JOE’ equal to a value
JOE stored in a column of data type CHAR or VARCHAR of width three or
more. Similarly, {project-name} SQL considers a value JOE stored in any
column of the CHAR data type equal to the value JOE stored in any column
of the VARCHAR data type.
[[comparing_numeric_data]]
===== Comparing Numeric Data
Before evaluation, all numeric values in an expression are first
converted to the maximum precision needed anywhere in the expression.
[[comparing_interval_data]]
===== Comparing Interval Data
For comparisons of INTERVAL values, {project-name} SQL first converts the
intervals to a common unit.
If no common unit exists, {project-name} SQL reports an error. Two INTERVAL
values must be both year-month intervals or both day-time intervals.
[[examples_of_in]]
==== Examples of IN
* Find those employees whose EMPNUM is 39, 337, or 452:
+
```
SELECT last_name, first_name, empnum
FROM persnl.employee
WHERE empnum IN (39, 337, 452);
LAST_NAME FIRST_NAME EMPNUM
-------------------- --------------- ------
CLARK DINAH 337
SAFFERT KLAUS 39
--- 2 row(s) selected.
```
* Find those items in PARTS whose part number is not in the PARTLOC table:
+
```
SELECT partnum, partdesc FROM sales.parts
WHERE partnum NOT IN (SELECT partnum
FROM invent.partloc);
PARTNUM PARTDESC
------- ------------------
186 186 MegaByte Disk
--- 1 row(s) selected.
```
* Find those items (and their suppliers) in PARTS that have a supplier
in the PARTSUPP table:
+
```
SELECT P.partnum, P.partdesc, S.suppnum, S.suppname
FROM sales.parts P, invent.supplier S
WHERE P.partnum, S.suppnum IN
(SELECT partnum, suppnum FROM invent.partsupp);
```
* Find those employees in EMPLOYEE whose last name and job code match
the list of last names and job codes:
+
```
SELECT empnum, last_name, first_name
FROM persnl.employee
WHERE (last_name, jobcode)
IN (VALUES ('CLARK', 500), ('GREEN', 200));
```
<<<
[[like_predicate]]
=== LIKE Predicate
The LIKE predicate searches for character strings that match a pattern.
[[like_syntax]]
==== Syntax
```
match-value [NOT] LIKE pattern [ESCAPE esc-char-expression]
```
* `_match-value_`
+
is a character value expression that specifies a set of strings to
search for that match the _pattern_.
* `_pattern_`
+
is a character value expression that specifies the pattern string for
the search.
* `_esc-char-expression_`
+
is a character value expression that must evaluate to a single
character. The escape character value is used to turn off the special
meaning of percent (%) and underscore (_). See <<wild_card_characters,Wild-Card Characters>>.
<<escape_characters>>.
See <<character_value_expressions,Character Value Expressions>>.
[[like_considerations]]
==== Considerations
[[comparing_the_value_to_the_pattern]]
===== Comparing the Value to the Pattern
The values that you compare must be character strings. Lowercase and
uppercase letters are not equivalent. To make lowercase letters match
uppercase letters, use the UPSHIFT function. A blank is compared in the
same way as any other character.
[[when_a_like_predicate_is_true]]
===== When a LIKE Predicate Is True
When you refer to a column, the LIKE predicate is true if the _pattern_
matches the column value. If the value of the column reference is null,
the LIKE predicate evaluates to unknown for that row.
If the values that you compare are both empty strings (that is, strings
of zero length), the LIKE predicate is true.
[[using_not]]
===== Using NOT
If you specify NOT, the predicate is true if the _pattern_ does not
match any string in the _match-value_ or is not the same length as any string in the
_match-value_. For example, NAME NOT LIKE '_Z' is true if the string
is not two characters long or the last character is not Z. In a search
condition, the predicate NAME NOT LIKE '_Z' is equivalent to NOT (NAME
LIKE '_Z').
[[wild_card_characters]]
===== Wild-Card Characters
You can look for similar values by specifying only part of the
characters of _pattern_ combined with these wild-card characters:
* `Percent Sign (%)`
+
Use a percent sign to indicate zero or more characters of any type. For
example, '%ART%' matches 'SMART', 'ARTIFICIAL', and 'PARTICULAR', but not 'smart'.
* `Underscore (_)`
+
Use an underscore to indicate any single character. For example, 'BOO_'
matches 'BOOK' and 'BOOT' but not 'BOO', 'BOOKLET', or 'book'.
* `Escape Characters`
+
To search for a string containing a percent sign (%) or an underscore
(_), define an escape character, using ESCAPE _esc-char-expression_,
to turn off the special meaning of the percent sign and underscore.
+
To include a percent sign or an underscore in a comparison string, type
the escape character immediately preceding the percent sign or
underscore. For example, to locate the value 'A_B', type:
+
```
NAME LIKE 'A\_B' ESCAPE '\'
```
+
To include the escape character itself in the comparison string, type
two escape characters. For example, to locate 'A_B\C%', type:
+
```
NAME LIKE 'A\_B\\C\%' ESCAPE '\'
```
+
The escape character must precede only the percent sign, underscore, or
escape character itself. For example, the pattern RA\BS is an invalid
LIKE pattern if the escape character is defined to be '\'. Error 8410
will be returned if this kind of pattern is used in an SQL query.
[[comparing_the_pattern_to_char_columns]]
===== Comparing the Pattern to CHAR Columns
Columns of data type CHAR are fixed length. When a value is inserted
into a CHAR column, {project-name} SQL pads the value in the column with
blanks if necessary. The value 'JOE' inserted into a CHAR(4) column
becomes 'JOE ' (three characters plus one blank). The LIKE predicate is
true only if the column value and the comparison value are the same
length. The column value 'JOE ' does not match 'JOE' but does match
'JOE%'.
[[comparing_the_pattern_to_varchar_columns]]
===== Comparing the Pattern to VARCHAR Columns
Columns of variable-length character data types do not include trailing
blanks unless blanks are specified when data is entered. For example,
the value 'JOE' inserted in a VARCHAR(4) column is 'JOE' with no
trailing blanks. The value matches both 'JOE' and 'JOE%'.
If you cannot locate a value in a variable-length character column, it
might be because trailing blanks were specified when the value was
inserted into the table. For example, a value of '5MB ' (with one
trailing blank) will not be located by LIKE '%MB' but will be located by
LIKE '%MB%'.
[[like_examples]]
==== Examples
* Find all employee last names beginning with ZE:
+
```
last_name LIKE 'ZE%'
```
* Find all part descriptions that are not 'FLOPPY_DISK':
+
```
partdesc NOT LIKE 'FLOPPY\_DISK' ESCAPE '\'
```
+
The escape character indicates that the underscore in 'FLOPPY_DISK' is
part of the string to search for, not a wild-card character.
<<<
[[regexp_predicate]]
=== REGEXP Predicate
Performs a pattern match of a string expression against a pattern .
The pattern can be an extended regular expression.
Returns 1 if expression matches pattern; otherwise it returns 0.
If either expression or pattern is NULL, the result is NULL.
[[regexp_syntax]]
==== Syntax
```
match-value [NOT] REGEXP regular-expression
```
* `_match-value_`
+
is a character value expression that specifies a set of strings to
search for that match the _regular-expression_.
* `_regular-expression_`
+
is a character value expression that specifies a regular expression.
Trafodion regular expressions follow POSIX regular expression rules.
[[using_not]]
===== Using NOT
If you specify NOT, the predicate is true if the _regular-expression_ does not
match any string in the _match-value_.
[[regexp_examples]]
==== Examples
* Find valid numbers
+
```
col REGEXP '^[0-9]*\s*$'
```
* Find valid words, no numbers
+
```
col REGEXP '^.[A-Za-z]+\s*$'
```
* Find valid email address
+
```
col REGEXP '\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*\s*'
```
<<<
[[null_predicate]]
=== NULL Predicate
The NULL predicate determines whether all the expressions in a sequence
are null. See <<null,Null>> .
```
row-value-constructor IS [NOT] NULL
row-value-constructor is:
(expression [,expression]...)
| row-subquery
```
* `_row-value-constructor_`
+
specifies the operand of the NULL predicate. The operand can be either
of these:
+
```
(expression [,expression ]...)
```
+
is a sequence of SQL value expressions, separated by commas and enclosed
in parentheses.
+
_expression_ cannot include an aggregate function unless expression is
in a HAVING clause. _expression_ can be a scalar subquery (a subquery
that returns a single row consisting of a single column). See
<<expressions,Expressions>>.
* `_row-subquery_`
+
is a subquery that returns a single row (consisting of a sequence of
values). See <<subquery,Subquery>> .
+
If all of the expressions in the _row-value-constructor_ are null, the
IS NULL predicate is true. Otherwise, it is false. If none of the
expressions in the _row-value-constructor_ are null, the IS NOT NULL
predicate is true. Otherwise, it is false.
<<<
[[considerations-for-null]]
==== Considerations for NULL
[[summary_of_null_results]]
===== Summary of NULL Results
Let rvc be the value of the _row-value-constructor_. This table
summarizes the results of NULL predicates. The degree of a _rvc_ is the
number of values in the _rvc_.
[cols="5*",options="header"]
|===
| Expressions | _rvc_ IS NULL | _rvc_ IS NOT NULL | NOT _rvc_ IS NULL | NOT _rvc_ IS NOT NULL
| degree 1: null | TRUE | FALSE | FALSE | TRUE
| degree 1: not null | FALSE | TRUE | TRUE | FALSE
| degree>1: all null | TRUE | FALSE | FALSE | TRUE
| degree>1: some null | FALSE | FALSE | TRUE | TRUE
| degree>1: none null | FALSE | TRUE | TRUE | FALSE
|===
The _rvc_ IS NOT NULL predicate is not equivalent to NOT _rvc_ IS NULL.
[[examples-of-null]]
==== Examples of NULL
* Find all rows with null in the SALARY column:
+
```
salary IS NULL
```
* This predicate evaluates to true if the expression (PRICE + TAX)
evaluates to null:
+
```
(price + tax) IS NULL
```
* Find all rows where both FIRST_NAME and SALARY are null:
+
```
(first_name, salary) IS NULL
```
<<<
[[quantified_comparison_predicates]]
=== Quantified Comparison Predicates
A quantified comparison predicate compares the values of sequences of
expressions to the values in each row selected by a table subquery. The
comparison operation is quantified by the logical quantifiers ALL, ANY,
or SOME.
```
row-value-constructor comparison-op quantifier table-subquery
row-value-constructor is:
(expression [,expression]...)
| row-subquery
comparison-op is:
= Equal
| <> Not equal
| != Not equal
| < Less than
| > Greater than
| <= Less than or equal to
| >= Greater than or equal to
quantifier is:
ALL | ANY | SOME
```
* `_row-value-constructor_`
+
specifies the first operand of a quantified comparison predicate. The
first operand can be either of:
* `(_expression_ [,_expression_ ]&#8230;)`
+
is a sequence of SQL value expressions, separated by commas and enclosed
in parentheses. _expression_ cannot include an aggregate function
unless _expression_ is in a HAVING clause. _expression_ can be a scalar
subquery (a subquery that returns a single row consisting of a single
column). See <<expressions,Expressions>>.
** `row-subquery`
+
is a subquery that returns a single row (consisting of a sequence of
values). See <<subquery,Subquery>> .
* `ALL`
+
specifies that the predicate is true if the comparison is true for every
row selected by
+
_table-subquery_ (or if _table-subquery_ selects no rows), and specifies
that the predicate is false if the comparison is false for at least one
row selected.
* `ANY | SOME`
+
specifies that the predicate is true if the comparison is true for at
least one row selected by the _table-subquery_ and specifies that the
predicate is false if the comparison is false for every row selected (or
if _table-subquery_ selects no rows).
* `table-subquery`
+
provides the values for the comparison. The number of values returned by
the _row-value-constructor_ must be equal to the number of values specified
by the _table-subquery_, and the data types of values returned by the
_row-value-constructor_ must be comparable to the data types of values
returned by the _table-subquery_. See “Subquery” .
[[considerations_for_all_any_some]]
==== Considerations for ALL, ANY, SOME
Let _R_ be the result of the _row-value-constructor_, _T_ the result
of the _table-subquery_, and _RT_ a row in _T_.
===== Result of _Rcomparison-op_ ALL _T_
If _T_ is empty or if _R comparison-op RT_ is true for every row _RT_ in
_T_, the _comparison-op_ ALL predicate is true.
If _R comparison-op RT_ is false for at least one row _RT_ in _T_, the
_comparison-op_ ALL predicate is false.
===== Result of _Rcomparison-op_ ANY _T_ or _Rcomparison-op_ SOME _T_
If _T_ is empty or if _R comparison-op RT_ is false for every row _RT_
in _T_, the _comparison-op_ ANY predicate is false.
If _R comparison-op RT_ is true for at least one row _RT_ in _T_, the
_comparison-op_ ANY predicate is true.
<<<
[[examples_of_all_any_some]]
==== Examples of ALL, ANY, SOME
* This predicate is true if the salary is greater than the salaries of
all the employees who have a jobcode of 420:
+
```
salary > ALL (SELECT salary
FROM persnl.employee
WHERE jobcode = 420)
```
+
Consider this SELECT statement using the preceding predicate:
+
```
SELECT empnum, first_name, last_name, salary FROM persnl.employee
WHERE salary > ALL (SELECT salary
FROM persnl.employee WHERE jobcode = 420);
```
+
The inner query providing the comparison values yields these results:
+
```
SELECT salary
FROM persnl.employee WHERE jobcode = 420;
SALARY
-----------
33000.00
36000.00
18000.10
--- 3 row(s) selected.
```
+
The SELECT statement using this inner query yields these results. The
salaries listed are greater than the salary of every employees with
jobcode equal to 420—that is, greater than
$33,000.00, $36,000.00, and $18,000.10:
+
```
SELECT empnum, first_name, last_name, salary FROM persnl.employee
WHERE salary > ALL (SELECT salary
FROM persnl.employee WHERE jobcode = 420);
FIRST_NAME LAST_NAME SALARY
--------------- -------------------- -----------
ROGER GREEN 175500.00
JERRY HOWARD 137000.10
JANE RAYMOND 136000.00
...
ALAN TERRY 39500.00
BEN HENDERSON 65000.00
JESSICA CRINER 39500.00
--- 23 row(s) selected.
```
* This predicate is true if the part number is equal to any part number
with more than five units in stock:
+
```
partnum = ANY (SELECT partnum
FROM sales.odetail
WHERE qty_ordered > 5)
```
+
Consider this SELECT statement using the preceding predicate:
+
```
SELECT ordernum, partnum, qty_ordered FROM sales.odetail
WHERE partnum = ANY (SELECT partnum
FROM sales.odetail WHERE qty_ordered > 5);
```
+
The inner query providing the comparison values yields these results:
+
```
SELECT partnum FROM sales.odetail
WHERE qty_ordered > 5;
Part/Num
--------
2403
5100
5103
6301
6500
....
--- 60 row(s) selected.
```
+
The SELECT statement using this inner query yields these results. All of
the order numbers listed have part number equal to any part number with
more than five total units in stock—that is, equal to 2403, 5100, 5103,
6301, 6500, and so on:
+
```
SELECT ordernum, partnum, qty_ordered FROM sales.odetail
WHERE partnum = ANY (SELECT partnum
FROM sales.odetail WHERE qty_ordered > 5);
Order/Num Part/Num Qty/Ord
---------- -------- ----------
100210 244 3
100210 2001 3
100210 2403 6
100210 5100 10
100250 244 4
100250 5103 10
100250 6301 15
100250 6500 10
...... .... ..
--- 71 row(s) selected.
```
<<<
[[privileges]]
== Privileges
A privilege provides authorization to perform a specific operation for a
specific object. A privilege can be granted to or revoked from a user or
role in many ways:
* Implicit privileges are granted to an owner of an object when the
object is created. The owner retains implicit privileges for the
lifespan of the object.
* Explicit privileges can be granted to or revoked from a user or role.
Explicit privileges can be granted or revoked by a database user
administrator, an object owner, or a user who has been granted the
privilege with the WITH GRANT OPTION option.
* The privileges granted to a user can come from various sources.
Privileges can be directly granted to a user or they can be inherited
through a role. For example, a user gets the SELECT privilege on table
T1 from two different roles. If one of the roles is revoked from the
user, the user will still be able to select from T1 via the SELECT
privilege granted to the remaining role.
* A user who is granted a role is thereby conferred all privileges of
the role. The only way to revoke any such privilege is to revoke the
role from the user. For more information, see
<<roles,Roles>> .
* Privileges granted on an object can be for all the columns of the object or just a subset of the columns.
Only the following subset of privileges is applicable at the column-level: INSERT, REFERENCES, SELECT, and UPDATE.
You can manage privileges by using the GRANT and REVOKE statements.
For more information on GRANT, see:
* <<grant_statement,GRANT Statement>>
* <<grant_component_privilege_statement,GRANT COMPONENT PRIVILEGE Statement>>
* <<grant_role_statement,GRANT ROLE Statement>>
For more information on REVOKE, see:
* <<revoke_statement,REVOKE Statement>>
* <<revoke_component_privilege_statement,REVOKE COMPONENT PRIVILEGE Statement>>
* <<revoke_role_statement,REVOKE ROLE Statement>>
<<<
[[roles]]
== Roles
A role offers the flexibility of implicitly assigning a set of
privileges to users, instead of assigning privileges individually. A
user can be granted one or more roles. A role can be granted to one or
more users. A role can be granted by or revoked by a database user
administrator, a role owner, or a member of the role.
Privileges are granted to a role. When a role is granted to a user, the
privileges granted to the role become available to the user. If new
privileges are granted to the role, those privileges become available to
all users who have been granted the role. When a role is revoked from a
user, the privileges granted to the role are no longer available to the
user. The change in privileges is automatically propagated to and detected by
active sessions, so there is no need for users to disconnect from and
reconnect to a session to see the updated set of privileges. For more
information about privileges, see <<privileges,Privileges>>.
A role name is an authorization ID. A role name cannot be identical to a
registered database user name. For more information, see
<<authorization_ids,Authorization IDs>>.
To manage roles, see these SQL statements:
* <<create_role_statement,CREATE ROLE Statement>>
* <<drop_role_statement,DROP ROLE Statement>>
* <<grant_role_statement,GRANT ROLE Statement>>
* <<revoke_role_statement,REVOKE ROLE Statement>>
<<<
[[schemas]]
== Schemas
The ANSI SQL:1999 schema name is an SQL identifier that is unique for a
given ANSI catalog name. {project-name} SQL automatically qualifies the
schema name with the current default catalog name, TRAFODION.
The logical name of the form _schema.object_ is an ANSI name. The part
_schema_ denotes the ANSI-defined schema.
To be compliant with ANSI SQL:1999, {project-name} SQL provides support for
ANSI object names.
By using these names, you can develop ANSI-compliant applications that
access all SQL objects. You can access {project-name} SQL objects with the
name of the actual object. See <<set_schema_statement,SET SCHEMA Statement>>.
[[creating_and_dropping_schemas]]
=== Creating and Dropping Schemas
You create a schema using the CREATE SCHEMA command and drop a schema using the
DROP SCHEMA statement. For more information, see the
<<create_schema_statement,CREATE SCHEMA Statement>> and the
<<drop_schema_statement,DROP SCHEMA Statement>>.
<<<
[[search_condition]]
== Search Condition
A search condition is used to choose rows from tables or views,
depending on the result of applying the condition to rows. The condition
is a Boolean expression consisting of predicates combined together with
OR, AND, and NOT operators.
You can use a search condition in the WHERE clause of a SELECT, DELETE,
or UPDATE statement, the HAVING clause of a SELECT statement, the
searched form of a CASE expression, the ON clause of a SELECT statement
that involves a join, a CHECK constraint, or a ROWS SINCE sequence
function.
```
search-condition is:
boolean-term | search-condition OR boolean-term
boolean-term is:
boolean-factor | boolean-term AND boolean-factor
boolean-factor is:
[NOT] boolean-primary
boolean-primary is:
predicate | (search-condition)
```
* `OR`
+
specifies the resulting search condition is true if and only if either
of the surrounding predicates or search conditions is true.
* `AND`
+
specifies the resulting search condition is true if and only if both the
surrounding predicates or search conditions are true.
* `NOT`
+
reverses the truth value of its operand—the following predicate or
search condition. predicate is a BETWEEN, comparison, EXISTS, IN, LIKE, NULL, or quantified
comparison predicate. A predicate specifies conditions that must be satisfied for a row to be
chosen. See <<predicates,Predicates>> and individual entries.
<<<
[[considerations_for_search_condition]]
=== Considerations for Search Condition
[[order_of_evaluation]]
==== Order of Evaluation
SQL evaluates search conditions in this order:
1. Predicates within parentheses
2. NOT
3. AND
4. OR
[[column-references]]
==== Column References
Within a search condition, a reference to a column refers to the value
of that column in the row currently being evaluated by the search
condition.
[[subqueries]]
==== Subqueries
If a search condition includes a subquery and the subquery returns no
values, the predicate evaluates to null. See
<<subquery,Subquery>>.
<<<
[[examples_of_search_condition]]
=== Examples of Search Condition
* Select rows by using a search condition composed of three comparison
predicates joined by AND operators:
+
```
SELECT O.ordernum, O.deliv_date, OD.qty_ordered
FROM sales.orders O, sales.odetail OD
WHERE qty_ordered < 9
AND deliv_date <= DATE '2008-11-01'
AND O.ordernum = OD.ordernum;
ORDERNUM DELIV_DATE QTY_ORDERED
---------- ---------- -----------
100210 2008-04-10 3
100210 2008-04-10 3
100210 2008-04-10 6
100250 2008-06-15 4
101220 2008-12-15 3
...
--- 28 row(s) selected.
```
* Select rows by using a search condition composed of three comparison
predicates, two of which are joined by an OR operator (within
parentheses), and where the result of the OR and the first comparison
predicate are joined by an AND operator:
+
```
SELECT partnum, S.suppnum, suppname
FROM invent.supplier S, invent.partsupp PS
WHERE S.suppnum = PS.suppnum
AND (partnum < 3000 OR partnum = 7102);
SUPPNAME
------------------
NEW COMPUTERS INC
NEW COMPUTERS INC
NEW COMPUTERS INC
...
LEVERAGE INC
--- 18 row(s) selected.
```
<<<
[[subquery]]
== Subquery
A subquery is a query expression enclosed in parentheses. Its syntactic
form is specified in the syntax of a SELECT statement. For further
information about query expressions, see <<select_statement,SELECT Statement>>.
A subquery is used to provide values for a BETWEEN, comparison, EXISTS,
IN, or quantified comparison predicate in a search condition. It is also
used to specify a derived table in the FROM clause of a SELECT
statement.
A subquery can be a table, row, or scalar subquery. Therefore, its
result table can be a table consisting of multiple rows and columns, a
single row of column values, or a single row consisting of only one
column value.
[[select_form_of_a_subquery]]
=== SELECT Form of a Subquery
A subquery is typically specified as a special form of a SELECT
statement enclosed in parentheses that queries (or selects) to provide
values in a search condition or to specify a derived table as a table
reference.
The form of a subquery specified as a SELECT statement is _query-expr_.
Neither the ORDER BY clause nor [FIRST N] / [ANY N] clause is allowed in
a subquery.
[[using_subqueries_to_provide_comparison_values]]
=== Using Subqueries to Provide Comparison Values
When a subquery is used to provide comparison values, the SELECT
statement that contains the subquery is called an outer query. The
subquery within the SELECT is called an _inner query_.
In this case, the differences between the SELECT statement and the
SELECT form of a subquery are:
* A subquery is always enclosed in parentheses.
* A subquery cannot contain an ORDER BY clause.
* If a subquery is not part of an EXISTS, IN, or quantified comparison
predicate, and the subquery evaluates to more than one row, a run-time
error occurs.
<<<
[[nested_subqueries_when_providing_comparison_values]]
=== Nested Subqueries When Providing Comparison Values
An outer query (a main SELECT statement) can have nested subqueries.
Subqueries within the same WHERE or HAVING clause are at the same level.
For example, this query has one level of nesting:
```
SELECT * FROM table1
WHERE A = (SELECT P FROM table2 WHERE q = 1)
AND b = (SELECT x FROM table3 WHERE y = 2);
```
A subquery within the WHERE clause of another subquery is at a different
level, however, so this query has two levels of nesting:
```
SELECT * FROM table1
WHERE a = (SELECT p FROM table2
WHERE q = (SELECT x FROM table3
WHERE y = 2))
```
The maximum level of nested subqueries might depend on:
* The complexity of the subqueries.
* Whether the subquery is correlated and if so, whether it can be un-nested.
* Amount of available memory.
Other factors may affect the maximum level of subqueries.
[[correlated_subqueries_when_providing_comparison_values]]
=== Correlated Subqueries When Providing Comparison Values
In a subquery, when you refer to columns of any table or view defined in
an outer query, the reference is called an outer reference. A subquery
containing an outer reference is called a correlated subquery.
If you refer to a column name that occurs in more than one outer query,
you must qualify the column name with the correlation name of the table
or view to which it belongs. Similarly, if you refer to
a column name that occurs in the subquery and in one or more outer
queries, you must qualify the column name with the correlation name of
the table or view to which it belongs. The correlation name is known to
other subqueries at the same level, or to inner queries but not to outer
queries.
If you use the same correlation name at different levels of nesting, an
inner query uses the one from the nearest outer level.
<<<
[[tables]]
== Tables
A table is a logical representation of data in which a set of records is
represented as a sequence of rows, and the set of fields common to all
rows is represented by columns. A column is a set of values of the same
data type with the same definition. The intersection of a row and column
represents the data value of a particular field in a particular record.
Every table must have one or more columns, but the number of rows can be
zero. No inherent order of rows exists within a table.
You create a {project-name} SQL user table by using the CREATE TABLE
statement. See the <<create_table_statement,CREATE TABLE Statement>>.
The definition of a user table within the statement includes this information:
* Name of the table
* Name of each column of the table
* Type of data you can store in each column of the table
* Other information about the table, including the physical
characteristics of the file that stores the table (for example, the
storage order of rows within the table)
A {project-name} SQL table is described in an SQL schema and stored as an
HBase table. {project-name} SQL tables have regular ANSI names in the catalog
TRAFODION. A {project-name} SQL table name can be a fully qualified ANSI name
of the form TRAFODION._schema-name.object-name_. A {project-name} SQL
table’s metadata is stored in the schema TRAFODION."_MD_".
Because {project-name} defines the encodings for column values in {project-name}
SQL tables, those tables support various {project-name} SQL statements. See
<<supported_sql_statements_with_hbase_tables,Supported SQL Statements With HBase Tables>>.
Internally, {project-name} SQL tables use a single HBase column family and
shortened column names to conserve space. Their encoding allows keys
consisting of multiple columns and preserves the order of key values as
defined by SQL. The underlying HBase column model makes it very easy to
add and remove columns from {project-name} SQL tables. HBase columns that are
not recorded in the {project-name} metadata are ignored, and missing columns
are considered NULL values.
[[base_tables_and_views]]
=== Base Tables and Views
In some descriptions of SQL, tables created with a CREATE TABLE
statement are called base tables to distinguish them from views, which
are called logical tables.
A view is a named logical table defined by a query specification that
uses one or more base tables or other views. See <<views,Views>>.
[[example_of_a_base_table]]
=== Example of a Base Table
For example, this EMPLOYEE table is a base table in a sample database:
[cols="6*",options="header"]
|===
| EMPNUM | FIRST_NAME | LAST_NAME | DEPTNUM | JOBCODE | SALARY
| 1 | ROGER | GREEN | 9000 | 100 | 175500.00
| 23 | JERRY | HOWARD | 1000 | 100 | 137000.00
| 75 | TIM | WALKER | 3000 | 300 | 32000.00
| . . . | . . . | . . . | . . . | . . . | . . .
|===
In this sample table, the columns are EMPNUM, FIRST_NAME, LAST_NAME,
DEPTNUM, JOBCODE, and SALARY. The values in each column have the same
data type.
<<<
[[views]]
== Views
A view provides an alternate way of looking at data in one or more
tables. A view is a named specification of a result table, which is a
set of rows selected or generated from one or more base tables or other
views. The specification is a SELECT statement that is executed whenever
the view is referenced.
A view is a logical table created with the CREATE VIEW statement and
derived by projecting a subset of columns, restricting a subset of rows,
or both, from one or more base tables or other views.
[[sql_views]]
=== SQL Views
A view’s name must be unique among table and view names within the
schema that contains it. Single table views can be updatable. Multi-table
views are not updatable.
For information about SQL views, see <<create_view_statement,CREATE VIEW Statement>>
and <<drop_view_statement,DROP VIEW Statement>>.
[[example_of_a_view]]
=== Example of a View
You can define a view to show only part of the data in a table. For
example, this EMPLIST view is defined as part of the EMPLOYEE table:
[cols="5*",options="header"]
|===
| EMPNUM | FIRST_NAME | LAST_NAME | DEPTNUM | JOBCODE
| 1 | ROGER | GREEN | 9000 | 100
| 23 | JERRY | HOWARD | 1000 | 100
| 75 | TIM | WALKER | 3000 | 300
| . . . | . . . | . . . | . . . | . . .
|===
In this sample view, the columns are EMPNUM, FIRST_NAME, LAST_NAME,
DEPTNUM, and JOBCODE. The SALARY column in the EMPLOYEE table is not
part of the EMPLIST view.