1  Literals
   Literals, which are also called constants, specify a value.

   The following diagram shows the format of literals:

 literal =
 
 --+-> numeric-literal ----+--->
   +-> string-literal -----+
   +-> date-time-literal --+
   +-> interval-literal ---+
   +-> dbkey-literal ------+

   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
 

2  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:

 numeric-literal =
 
 --+------+-+-+> <digit> +-+------------------------+-+-+
   +-> + -+ | +----<-----+ +-> . --+------->------+-+ | |
   +-> - -+ |                      +-+> <digit> +-+   | |
            |                        +----<-----+     | |
            +-> . -+> <digit> +-----------------------+ |
                   +----<-----+                         |
  +-----------------------------------------------------+
  ++---------->-----------------+-->
   +-- E -+------+-+> <digit> +-+
          +-> + -+ +----<-----+
          +-> - -+

 

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.
 

3  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.
 

4  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'
 

3  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'
 

2  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:

 date-time-literal =
 
 --+-> TIME --> ' --> time-body --> ' ------------------------+-->
   +-> DATE -+---------+-> ' --> date-body --> ' -------------+
   |         +-> ANSI -+                                      |
   +-> TIMESTAMP --> ' --+-> date-body   time-body -+-->  ' --+
                         +-> date-body : time-body -+
 



 time-body =
 
 ---> <hours> : <minutes> :  <seconds> ---+
    +---------------------<---------------+
    ++---------------------------+--------->
     +-> . <fractional-seconds> -+



 date-body =
 
 ---> <year> - <month> - <day> -->
 

                                  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:

 interval-literal =
 
 --> INTERVAL --> ' --> <interval-body> --> ' --> interval-qualifier -->
 



 interval-body =
 
 +----++-> years -+---------------+
 +> + ||          +> - +          |
 +> - ++---------------+> months -+-------------------------------------+>
       +> days +--------------+                                         |
       |       +> : +         |                                         |
       +------------+> hours ++-----------+                             |
       |                     +> : +       |                             |
       +--------------------------+> min ++-----------+                 |
       |                                 +> : +       |                 |
       +--------------------------------------+> sec ++-----------------+
       |                                             +> : +             |
       +--------------------------------------------------+> . frac-sec +
 



 interval-qualifier =
 
 --+-> YEAR ---> prec --+-------------+-----------------+->
   |                    +-> TO MONTH -+                 |
   +-> MONTH --> prec ----------------------------------+
   +-> DAY ----> prec --+-------------------------------+
   |                    +-> TO -+-> HOUR ---------------+
   |                            +-> MINUTE -------------+
   |                            +-> SECOND -> frac -----+
   +-> HOUR ---> prec --+-------------------------------+
   |                    +-> TO -+-> MINUTE -------------+
   |                            +-> SECOND -> frac -----+
   +-> MINUTE -> prec --+-------------------------------+
   |                    +-> TO SECOND ------> frac -----+
   +-> SECOND -> seconds-prec --------------------------+
 



 frac =
 
 --+---------------------------+->
   +--> ( <numeric-literal> ) -+
 



 prec =
 
 --+---------------------------+->
   +--> ( <numeric-literal> ) -+
 



 seconds-prec =
 
 --+--------------------------------------+-->
   +-> ( <numeric-literal-1> ---+         |
      +-------------------------+         |
      ++--------------------------+-> ) --+
       +> , <numeric-literal-2> --+
 

      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