Expressing Literals
A literal is a nonnull value corresponding to a given data type. Literals are typically constant values, or in other words, they are values that are taken as they are. A literal value must comply with the data type that it represents.
SQL provides mechanisms for expressing literals in SQL statements.
Character Literals
A character literal represents a value of CHARACTER or VARCHAR data type. To express a character literal, enclose the character string in single quotes ( ' ). The number of characters enclosed between the single quotes implies the length of the literal.
Examples
'Oracle
BI Server'
'abc123'
Datetime Literals
The SQL 92 standard defines three kinds of 'typed' datetime literals, in the following formats:
DATE 'yyyy-mm-dd'
TIME 'hh:mm:ss'
TIMESTAMP 'yyyy-mm-dd hh:mm:ss'
To express a typed datetime literal, use the keywords DATE, TIME, or TIMESTAMP followed by a datetime string enclosed in single quotation marks,
as in the preceding example. Two digits are required for all nonyear
components even if the value is a single digit.
Examples
DATE '2000-08-15'
TIME '11:55:25'
TIMESTAMP '1999-03-15 11:55:25'
Numeric Literals
A numeric literal represents
a value of a numeric data type (such as INTEGER, DECIMAL, or FLOAT). To express a numeric
literal, type the number as part of a SQL statement.
Do not surround numeric literals with single quotes. Doing so expresses the literal as a character literal.
NUMERIC, be aware of the
Oracle standard double promotion rules, including the following: DOUBLE/NUMBER = DOUBLE , DOUBLE * NUMBER = DOUBLE Because the parsing of numeric literals happens very early
in the query processing before the actual data source is known, internally,
the Oracle BI Server treats decimal numbers as NUMERIC if
ENABLE_ NUMERIC_DATA_TYPE is set to YES , regardless of data
source type. When NUMERIC is enabled and
the Oracle BI Server executes an expression internally involving decimal
literals, the server treats the literals as NUMERIC even if the back-end data source does not support the NUMERIC data type. However, the type promotion rules still apply. For example,
if the Oracle BI Server retrieves the data from a data source as DOUBLE and combines that with a NUMERIC literal during internal execution, the final result is still be
converted to DOUBLE.Numeric literals include:
Integer Literals
Decimal Literals
Floating Point Literals
Integer Literals To express an
integer constant as a literal, specify the integer as part of a SQL
statement (for example, in the SELECT list). Precede
the integer with a plus sign (+) to indicate the integer is positive,
or a minus sign (-) to indicate the integer is negative. Unsigned
integers are assumed to be positive.
Examples
234
+2
567934
Decimal Literals To express a decimal literal, specify a decimal number. Precede the number with a plus sign (+) to indicate the number is positive, or a minus sign (-) to indicate the number is negative. Unsigned numbers are assumed to be positive.
Examples
1.223
-22.456
+33.456789
Floating Point Literals To express floating point numbers as literal constants, enter a decimal literal followed by the letter E (either uppercase or lowercase), followed by the plus sign (+) to indicate a positive exponent, or the minus sign (-) to indicate a negative exponent. No spaces are allowed between the integer, the letter E, and the sign of the exponent.
Examples
333.456E-
1.23e+