Values can be specified in SQL statements to enter or update column values. They can also be linked by operators in expressions and used in comparisons. In routines they can also be used as parameters and local variables. Values can be entered directly as literals or passed in host variables (see "Host variables"). In dynamically compilable statements (see "Dynamic SQL"), the character “?” can be used as a placeholder for a value. Every value has a data type.
SESAM/SQL makes a distinction between NULL values and non-NULL values. Depending on the data type, non-null values are classified as strings (alphanumeric and national values), numeric values and date and time values.
Each of these classes has corresponding formats for specifying literals.
REF values that occur in connection with BLOBs (Binary Large Objects) are not values in the conventional sense. They are used to reference so-called BLOB objects in base tables. Information on defining REF values in base tables can be found in the section “Column”. The structure and method of processing BLOB objects are described in section “BLOB constructs”.
NULL values
NULL values are provided to distinguish missing values from values. The term “null value” does not refer to any specified value, it conveys the meaning “value unknown” or “irrelevant”. In particular, it must not be confused with a blank or a zero.
The keyword NULL can be seen as a literal for the null value. It can be specified for instance in INSERT, MERGE or UPDATE statements in order to enter a null value in a column. A null value can also be defined as a default value within a column definition using the DEFAULT NULL clause.
If NOT NULL or PRIMARY KEY is specified for a column in the column definition, the column can contain non-null values only. If no NOT NULL or PRIMARY KEY constraint and no default value (see "Column") other than NULL has been defined, SESAM/SQL enters a null value automatically if no value is specified for a column when a row is inserted.
Depending on the type of link (see "Search condition"), a null value in a condition will generally return the truth value unknown. One exception in this context is the predicate column IS [NOT] NULL, which returns only the truth values true or false.
Values for multiple columns
A value for a range of column elements in a multiple column is specified by a value, which can be a host variable for a vector, a placeholder in the form “?” or an aggregate. An aggregate is specified in the form
<value1, value2, ...>
value1, value2, ... are here the values of the occurrences of a multiple column.
Strings
Strings are sequences of any characters in EBCDIC or Unicode. EBCDIC strings are termed “alphanumeric values”, Unicode strings are termed “national values”.
In SESAM/SQL, alphanumeric literals, national literals and special literals are used to represent strings.
Alphanumeric values
Alphanumeric literals are specified in the form '[character... ]' or X'[hex hex... ]' respectively and can contain any EBCDIC character. The two single quotes then count as a single character. The data type is CHAR (n), where n is the number of characters.
In addition to alphanumeric literals, it is possible to specify alphanumeric values with special literals. For example, SYSTEM_USER returns the name of the current system user.
The operator || is available for alphanumeric values. This operator concatenates two strings to form a single string. When strings are concatenated, either both operands must be alphanumeric (CHAR or VARCHAR) or both must be of the national type (NCHAR or NVARCHAR).
An alphanumeric literal can comprise substrings, each contained in a separate line:
'substring_1'
'substring_2’
...
'substring_n'
This corresponds to the alphanumeric literal
'substring_1'||'substring_2'|| ... ||'substring_n'
Comments and blanks are permitted between the substrings.
National values
National literals are specified in the form N'[character... ]' or NX'[4hex... ]' or U&'[character ...esc+4hex...character...].
N literals can contain Unicode characters which are also included in the coded character set EDF03IRV. A single quote in a national literal must be duplicated; the duplicated single quote is regarded as one character. The data type is NCHAR (cu_length), where cu_length is the number of code units (1 code unit in UTF-16 = 2 bytes).
NX literals only contain Unicode characters in hexadecimal representation.
U& literals contain both Unicode characters from the coded character set EDF03IRV and Unicode characters in hexadecimal representation which are identified by a preceding escape character, U&'[esc 4hex...]' or U&'[esc+6hex...]'. However, each character must be invalidated.
For national values there is the operator ||, which concatenates two strings to form one string (concatenation). When strings are concatenated, either both operands must be alphanumeric (CHAR or VARCHAR) or both must be of the national type (NCHAR or NVARCHAR).
A national literal can comprise substrings, each of which is contained in a row:
N'substring_1'
'substring_2’
...
'substring_n'
This corresponds to the national literal
N'substring_1'||N'substring_2'|| ... ||N'substring_n'
Special literals
Special literals are converted to the corresponding values of their runtime environment at runtime:
Special literal | Meaning |
CURRENT_CATALOG | Name of the preset database |
CURRENT_ISOLATION_LEVEL | Isolation level of the current transaction |
CURRENT_REFERENCED_CATALOG | Name of the database which the current statement references |
CURRENT_SCHEMA | Name of the preset schema |
[CURRENT_ ]USER | Name of the current authorization identifier |
SYSTEM_USER | Name of the current system user |
Table 31: Special literals
Numeric values
Numeric values are integers, fixed-point numbers and floating-point numbers.
Time values
SESAM/SQL distinguishes three date and time values:
date (
DATE'year-month-day')time (
TIME'hour:minute:second.fraction_of_second')time stamp (
TIMESTAMP'year-month-day hour:minute:second.fraction_of_second').
The time functions (see "Time functions") CURRENT_DATE, CURRENT_TIME(3) and CURRENT_TIMESTAMP(3) and LOCALTIMESTAMP(3) can also be specified as date and time values. These return the current date, and/or the current time.
You can specify a number between 00 and 61 for second. The numbers after the decimal point specify the fractions of a second.
Date and time values can be entered in columns which have the appropriate data type. They are used
in the time functions CURRENT_DATE, CURRENT_TIME(3), LOCALTIME(3), CURRENT_TIMESTAMP(3) and LOCALTIMESTAMP(3)
in the aggregate functions COUNT, MAX and MIN
in the numeric functions EXTRACT and JULIAN_DAY_OF_DATE
in comparisons with another date and time value of the same data type.