Literals, which are also called constants, specify a value.
The following diagram shows the format of literals:
(B)0[m[1mliteral = [m
[1m [m
[1mqqwq> numeric-literal qqqqwqqq> [m
[1m tq> string-literal qqqqqu [m
[1m tq> date-time-literal qqu [m
[1m tq> interval-literal qqqu [m
[1m mq> dbkey-literal qqqqqqj [m
Literals are a type of value expression . Many SQL clauses that
do not accept general value expressions require literal values.
Literal values can be either numeric, character string, or date.
In addition, SQL provides keywords that refer to literals, for
example:
o NULL
o CURRENT_DATE
o SYSTEM_USER
1 – Numeric Literals
A numeric literal is a string of digits that SQL interprets as a
decimal number. A numeric literal can be a:
o Decimal string that consists of digits and an optional decimal
point. The maximum length, not counting the decimal point, is
19 digits.
o Decimal number in scientific notation (E notation) that
consists of a decimal string mantissa and a signed integer
exponent, separated by the letter E. You cannot embed spaces
in E notation.
The following syntax shows the format of numeric literals:
(B)0[m[1mnumeric-literal = [m
[1m [m
[1mqqwqqqqqqwqwqw> <digit> wqwqqqqqqqqqqqqqqqqqqqqqqqqwqwqk [m
[1m tq> + qu x mqqqq<qqqqqj mq> . qqwqqqqqqq>qqqqqqwqj x x [m
[1m mq> - qj x mqw> <digit> wqj x x [m
[1m x mqqqq<qqqqqj x x [m
[1m mq> . qw> <digit> wqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mqqqq<qqqqqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mwqqqqqqqqqq>qqqqqqqqqqqqqqqqqwqq> [m
[1m mqq E qwqqqqqqwqw> <digit> wqj [m
[1m tq> + qu mqqqq<qqqqqj [m
[1m mq> - qj [m
2 – Character String Literals
SQL recognizes the following types of character string literals:
o A quoted character string to represent printable characters
from the session's literal character set.
o A quoted character string qualified by the name of a character
set. The string represents printable characters from the named
character set.
o A national character string literal (an N followed by a quoted
character string), represents printable characters from the
national character set.
o A hexadecimal character string (an X followed by a quoted
character string) represents printable and nonprintable ASCII
characters.
2.1 – Quoted Character String
A quoted character string literal is a string of printable
characters enclosed in single quotation marks. The maximum length
of a character string is 1,024 octets. An unqualified character
string must contain characters only from the literal character
set of that session.
The printable ASCII characters consist of:
o Uppercase alphabetic characters:
A-Z
o Lowercase alphabetic characters:
a-z
o Numerals:
0-9
o Special characters:
! @ # $ % ^ & * ( ) - _ = + ` ~
[ ] { } ; : " \ | / ? > < . ,
For a list of the printable characters for DEC_MCS, see the
OpenVMS documentation for users; for a list of printable
characters for the other supported character sets, see the
standard for that character set.
Use a pair of single quotation marks to enclose a character
string literal. If you use double quotation marks, an
informational message is displayed, indicating that double
quotation marks are nonstandard. Double quotation marks are
passed as delimited identifiers if the quoting rules are set
to ANSI/ISO SQL.
2.1.1 – Qualified
You can use a quoted character string literal qualified by
the name of a character set. The character string must contain
characters only from the named character set.
A string literal qualified by a character set begins with an
underscore (_), followed by the name of a supported character
set, and a quoted string. No blank spaces are allowed outside of
the literal.
The following example shows how to qualify character strings with
DEC_MCS and with DEC_KANJI:
_DEC_MCS'Blue'
_DEC_KANJI'Blue'
You can use a national character string literal, which is a
quoted character string literal qualified by the national
character set. The character string must contain characters only
from the national character set.
A national character string literal begins with the letter N
followed by a quoted string. No blank spaces are allowed outside
of the literal.
The following example shows how to qualify a character string
with the national character set:
N'Blue'
2.2 – Hexadecimal Character String
A hexadecimal character string literal begins with an X
followed by a string of up to 16 characters enclosed in single
quotation marks. This type of string literal lets you represent
nonprintable ASCII characters by specifying the hexadecimal value
of the characters within the quotation marks.
Each ASCII character requires 2 hexadecimal digits to represent
it, so you must provide an even number of characters within
the quotation marks. The only valid characters for hexadecimal
character string literals are 0 through 9 and A through F
(uppercase or lowercase).
In the following example, the hexadecimal character string
literal represents two delete characters; the ASCII hexadecimal
value for a delete character is FF:
X'FFFF'
3 – Date Time Literals
When you refer to a date-time data type with a literal in an
SQL statement, you must precede the literal with the data type
name and enclose the literal in single quotation marks. You must
provide values for all fields, and values must be within the
valid range for the field.
The following syntax shows the format of date-time literals:
(B)0[m[1mdate-time-literal = [m
[1m [m
[1mqqwq> [1;4mTIME[m[1m qq> ' qq> time-body qq> ' qqqqqqqqqqqqqqqqqqqqqqqqwqq>[m
[1m tq> [1;4mDATE[m[1m qwqqqqqqqqqwq> ' qq> date-body qq> ' qqqqqqqqqqqqqu [m
[1m x mq> [1;4mANSI[m[1m qj x [m
[1m mq> [1;4mTIMESTAMP[m[1m qq> ' qqwq> date-body time-body qwqq> ' qqj [m
[1m mq> date-body : time-body qj [m
[1m [m
(B)0[m[1mtime-body = [m
[1m [m
[1mqqq> <hours> :[m [1m<minutes>[m [1m: [m [1m<seconds> qqqk[m
[1m lqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqj[m
[1m mwqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqq>[m
[1mmq> . <fractional-seconds> qj[m
(B)0[m[1mdate-body = [m
[1m [m
[1mqqq> <year> - <month> - <day> qq>[m
[1m [m
NOTE
In the following syntax descriptions, y, d, h, and s stand
for single digits in fields representing years, days, hours,
and seconds, respectively. The letter m stands for 1 digit
of the month number when it follows a y, and 1 digit of the
minutes number when it does not. Fractions of a second are
represented by digits after the decimal point.
The syntax for date-time literals is as follows:
o DATE literals
DATE 'yyyy-mm-dd'
or
DATE 'dd-mmm-yyyy hh:mm:ss.ss'
Examples: DATE ANSI '1993-05-27'
DATE VMS '27-MAY-1993 15:25:00.00'
SQL includes leap year validation for the 29th of February.
o TIME literals
TIME 'h:m:s'
TIME 'h:m:s.s'
Example: TIME '14:23:45.19'
TIME represents 24-hour time.
o TIMESTAMP literals
TIMESTAMP 'y-m-d h:m:s'
or
TIMESTAMP 'y-m-d:h:m:s'
Example: TIMESTAMP '1993-1-4 14:12:01.00'
TIMESTAMP '1993-1-4:14:12:01.00'
There are two formats allowed for the TIMESTAMP literal. The
SQL92 format allows a separating space character between the
date-body and the time-body as shown in the previous example.
The nonstandard format allows a separating colon character
between the date-body and the time-body. For example:
SQL> SET DEFAULT DATE FORMAT 'SQL92';
SQL> --
SQL> -- Create a table and insert several rows using the SQL92 format and
SQL> -- the nonstandard format for the TIMESTAMP literal.
SQL> --
SQL> CREATE TABLE t (a INTEGER, b TIMESTAMP(2)
cont> DEFAULT TIMESTAMP '1995-1-1 12:34:10.01');
SQL> INSERT INTO t (a) VALUE (0);
1 row inserted
SQL> --
SQL> -- Insert a row using the nonstandard format for the TIMESTAMP
SQL> -- literal.
SQL> --
SQL> INSERT INTO t (a,b) VALUE (1, TIMESTAMP '1995-1-1:12:34:10.01');
1 row inserted
SQL> --
SQL> -- Insert a row using the SQL92 format for the TIMESTAMP literal.
SQL> --
SQL> INSERT INTO t (a,b) VALUE (2, TIMESTAMP '1995-1-1 12:34:10.01');
1 row inserted
SQL> --
SQL> -- Select the rows. SQL uses the SQL92 format to display the
SQL> -- TIMESTAMP literal for all selected rows.
SQL> --
SQL> SELECT a, b, CAST (b AS CHAR(30)) FROM t ORDER BY a;
A B
0 1995-01-01 12:34:10.01 1995-01-01 12:34:10.01
1 1995-01-01 12:34:10.01 1995-01-01 12:34:10.01
2 1995-01-01 12:34:10.01 1995-01-01 12:34:10.01
3 rows selected
o INTERVAL literals
INTERVAL '±y-m' YEAR TO MONTH
INTERVAL '±d:h:m:s.s' DAY TO SECOND
Examples: INTERVAL '-1-2' YEAR TO MONTH
INTERVAL '1:4:30:0.0' DAY TO SECOND
INTERVAL '1:10' DAY TO HOUR
INTERVAL '235' MONTH(3)
The following syntax shows the format of interval literals:
(B)0[m[1minterval-literal = [m
[1m [m
[1mqq> [1;4mINTERVAL[m[1m qq> ' qq> <interval-body> qq> ' qq> interval-qualifier qq> [m
[1m [m
(B)0[m[1minterval-body = [m
[1m [m
[1mwqqqqwwq> years qwqqqqqqqqqqqqqqqk [m
[1mt> + xx m> - k x [m
[1mm> - jtqqqqqqqqqqqqqqqv> months qvqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqw>[m
[1m t> days wqqqqqqqqqqqqqqk x [m
[1m x m> : k x x [m
[1m tqqqqqqqqqqqqv> hours wvqqqqqqqqqqqk x [m
[1m x m> : k x x [m
[1m tqqqqqqqqqqqqqqqqqqqqqqqqqqv> min wvqqqqqqqqqqqk x [m
[1m x m> : k x x [m
[1m tqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqv> sec wvqqqqqqqqqqqqqqqqqu [m
[1m x m> : k x [m
[1m mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqv> . frac-sec j [m
[1m [m
(B)0[m[1minterval-qualifier = [m
[1m [m
[1mqqwq> [1;4mYEAR[m[1m qqq> prec qqwqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqwq> [m
[1m x mq> [1;4mTO[m[1m [1;4mMONTH[m[1m qj x [m
[1m tq> [1;4mMONTH[m[1m qq> prec qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mDAY[m[1m qqqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x mq> [1;4mTO[m[1m qwq> [1;4mHOUR[m[1m qqqqqqqqqqqqqqqu [m
[1m x tq> [1;4mMINUTE[m[1m qqqqqqqqqqqqqu [m
[1m x mq> [1;4mSECOND[m[1m q> frac qqqqqu [m
[1m tq> [1;4mHOUR[m[1m qqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x mq> [1;4mTO[m[1m qwq> [1;4mMINUTE[m[1m qqqqqqqqqqqqqu [m
[1m x mq> [1;4mSECOND[m[1m q> frac qqqqqu [m
[1m tq> [1;4mMINUTE[m[1m q> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x mq> [1;4mTO[m[1m [1;4mSECOND[m[1m qqqqqq> frac qqqqqu [m
[1m mq> [1;4mSECOND[m[1m q> seconds-prec qqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mfrac = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1m mqq> ( <numeric-literal> ) qj [m
[1m [m
(B)0[m[1mprec = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1m mqq> ( <numeric-literal> ) qj [m
[1m [m
(B)0[m[1mseconds-prec = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> [m
[1m mq> ( <numeric-literal-1> qqqk x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mwqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qqj [m
[1m m> , <numeric-literal-2> qqj [m
[1m [m
Because intervals can be signed quantities, a leading addition
or subtraction operator can precede the literal to indicate
positive (+) or negative (-) intervals.
o DBKEY string literals
THE DBKEY literal is used primarily by database administrators
who have database keys which were displayed in error messages,
or shown on an RMU/SHOW STATISTICS display and wish to display
the associated row.
The dbkey string literal is prefixed by _DBKEY, or _ROWID to
identify it as a special DBKEY literal. Some examples of valid
DBKEY literals are as follows:
o _DBKEY'23:5628:0'
An Oracle Rdb table dbkey has three parts, a logical area
(in this example 23), a page number (in this example 5628),
and a line number (in this example 0). All three parts must
be specified.
o _ROWID'23:5628:0, 45:345:15'
The DBKEY string literal may include several comma
separated dbkeys if this is used to reference a view
table. Each DBKEY references a row from the view made up
of component rows from a table.
The ROWID keyword is a synonym for DBKEY.
Leading and trailing spaces are ignored, however, spaces may
not be embedded within the numeric values in the DBKEY.
Errors will be reported if the DBKEY is for a different table,
is incorrectly formatted, or does not reference a row. The
reported errors are shown in the following example. A question
mark is placed within the string to highlight the syntax
error.
SQL> select * from employees where dbkey = _dbkey'1,2,3';
%RDB-F-CONVERT_ERROR, invalid or unsupported data conversion
-RDMS-E-DBKFORMAT, database key format incorrect "1,?2,3" - unexpected
character
SQL> select * from employees where dbkey = _dbkey'-1:+2:0';
%RDB-F-CONVERT_ERROR, invalid or unsupported data conversion
-RDMS-E-DBKFORMAT, database key format incorrect "-1:+?2:0" - unexpected
character
SQL> select * from employees where dbkey = _dbkey'23:1:1';
%RDB-E-NO_RECORD, access by dbkey failed because dbkey is no longer associated
with a record
-RDMS-F-INVDBK, 23:1:1 is not a valid dbkey