Converting from One Data Type to Another

In may cases, Oracle OLAP performs automatic data type conversion for you as discussed in "Automatic Conversion of Textual Data Types" and "Automatic Conversion of Numeric Data Types". Additionally there area number of OLAP DML functions that you can use to convert values from one data type to another. See Table A-24, "Data Type Conversion Functions" for a list of these functions.

Automatic Conversion of Textual Data Types

Oracle OLAP automatically converts NTEXT values to TEXT when they are specified as arguments to OLAP DML statements. This can result in data loss when the NTEXT values cannot be represented in the database character set

Automatic Conversion of Numeric Data Types

Oracle OLAP automatically converts SHORTINTEGER variables, as well as INTEGER variables with a fixed width of 1 byte, to INTEGER (with a width of 4 bytes) for calculations. When you calculate a total of SHORTINTEGER variables, then you can obtain and report a result greater than 32,767 or less than -32,768. When you calculate a total of 1-byte INTEGER variables, then you can obtain and report a result greater than 127 or less than -128. However, when you try to assign the result to a SHORTINTEGER variable or a 1-byte INTEGER variable respectively, then the variable is set to NA.

Oracle OLAP automatically converts numeric data types according to the following rules:

  • When you use a value with the SHORTINTEGER or SHORTDECIMAL data type in an expression, then the value is converted to its long counterpart before using it. See "Boolean Expressions" for information about problems that can occur when you mix SHORTDECIMAL and DECIMAL data types in a comparison expression.

  • When you save the results of a calculation as a value with the SHORTINTEGER data type, then NA is stored when the result is outside the range of a SHORTINTEGER (-32768 to 32767).

  • When you assign the value of a DECIMAL expression to an object with the INTEGER data type, then the value is rounded before storing or using it.

    Note:

    When a DECIMAL value is outside the range of an INTEGER, then an NA is stored.
  • When you use a decimal value where a value with the INTEGER data type is required, then the value is rounded before storing or using it.

    Note:

    When the DECIMAL value is outside the range of an INTEGER, then an NA is stored.
  • When you assign the value of a decimal expression to a variable with the SHORTDECIMAL data type, then only the first 7 significant digits are stored.

  • When you combine NUMBER values with other numeric data types, then all values are converted to NUMBER.

When these conversions are not what you want, then you can use the CONVERT, TO_CHAR, TO_NCHAR, TO_NUMBER, or TO_DATE functions to get different results.