1. Using Oracle Analytics for Oracle Taleo Enterprise Edition
  2. Expressing Literals

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.

Note:

When treating literals as 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+