Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B1033902 


View PDF 
A numeric expression evaluates to data with any of the numeric data types (that is, INTEGER
, SHORTINTEGER
, DECIMAL
, SHORTDECIMAL,
and
NUMBER
). The data in a numeric expression can be any combination of the following:
Numeric literals
Numeric variables or formulas
Dimensions
Functions that yield numeric results
Date literals, variables, formulas, or functions
In addition, you can join any of these threepart expressions with the arithmetic operators for a more complex numeric expression. You use arithmetic operators in numeric expressions with numeric data, which returns a numeric result. You can also use some arithmetic operators in date expressions with a mix of date and numeric data, to retrieve either a date or numeric result.
A number of options determine how Oracle OLAP handles numeric expressions. These options are listed in Table 32, "Numeric Options".
Table 32 Numeric Options
Option  Description 

DECIMALOVERFLOW 
Controls the result of arithmetic operations that produce outofrange numbers. Decimal numbers are stored as a mantissa and an exponent. Decimal overflow occurs when the result of a calculation is very large and can no longer be represented by the exponent portion of the decimal representation. 
DIVIDEBYZERO 
Controls the result of division by zero. 
RANDOM.SEED.1 and RANDOM.SEED.2  (Set only) Options that specify values used by RANDOM when computing random numbers. Typically, you only set values for these options when you are developing and debugging your application programs. 
ROOTOFNEGATIVE 
A flag that allows or disallows any attempt to obtain a root of a negative number. 
You can include any type of numeric data in the same numeric expression.
The data type of the result is determined according to the following rules:
When all the data in the expression is INTEGER
or SHORTINTEGER
, and the only operations are addition, subtraction, and multiplication, then the result is INTEGER
.
When any of the data is NUMBER
, then the result is NUMBER
.
When any of the data is DECIMAL
or SHORTDECIMAL
, and no data is NUMBER
, then the result is DECIMAL
.
When you perform any division or exponentiation operations, then the result is DECIMAL
.
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 the decimal value is outside the range of an integer (approximately plus or minus 2 billion), 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 (approximately plus or minus 2 billion), 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.
When you use a dimension with a data type of TEXT
in a numeric expression, the dimension value is treated as a position (an INTEGER
) and is used numerically. The position number is based on the default status list, not on current status.
When you use dates in arithmetic expressions, the result can be numeric or it can be a date. The legal operations for dates and the data type of the result are outlined in Table 33, "Legal Operations for Dates".
Table 33 Legal Operations for Dates
All decimal data is converted to floating point format, both for storing and for calculations. In floating point format, a number is represented by means of a mantissa and an exponent. The mantissa and the exponent are stored as binary numbers. The mantissa is a binary fraction which, when multiplied by a number equal to 2 raised to the exponent, produces a number that equals or closely approximates the original decimal number.
Because there is not always an exact binary representation for a fractional decimal number, just as there is not an exact representation for the decimal value of 1/3, fractional parts of decimal numbers cannot always be represented exactly as binary fractions. Arithmetic operations on floating point numbers can result in further approximations, and the inaccuracy gradually increases with the number of operations. In addition to the approximation factor, the available number of significant digits affects the exactness of the result.
For all of these reasons, a result computed by the TOTAL, AVERAGE, or other aggregation functions on a DECIMAL
or SHORTDECIMAL
variable can differ in the least significant digits from a result you compute by hand. Because the SHORTDECIMAL
data type provides a maximum of only seven significant digits, you see more of these differences with SHORTDECIMAL
data. Therefore, you might want to use the NUMBER
data type when accuracy is more important than computational speed, such as variables that contain currency amounts.
Another result of the fact that some fractional decimal numbers cannot be exactly represented by binary fractions is that for such numbers, the DECIMAL
data type offers a different and closer approximation than the SHORTDECIMAL
data type, because it has more significant digits. This can lead to problems when SHORTDECIMAL
and DECIMAL
data types are mixed in a comparison expression. For information on how to handle such comparisons, see "Boolean Expressions" .
You can control the following types of errors:
Division by zero. When you divide an NA value by zero, then the result is NA; no error occurs. Dividing a nonNA value by zero normally produces an error. When a dividebyzero error occurs when you are making a calculation on dimensioned data, then you can end up with partial results. When you use REPORT or an assignment statement (SET), values are reported or stored as they are calculated, so the division by zero halts the loop before it has gone through all the values.
When you want to suppress the dividebyzero error, then you can change the value of the DIVIDEBYZERO option to YES
. This means that the result of any division by zero is NA and no error occurs. This allows the calculation of the other values of a dimensioned expression to continue.
Root of negative numbers. It is normally an error to try to take the root of a negative number (which includes raising a number to a noninteger power). When you want to suppress the error message and allow the calculation of roots for nonnegative values of the expression to continue, then set the ROOTOFNEGATIVE option to YES
.
Overflow errors. The DECIMALOVERFLOW option works in a similar manner to DIVIDEBYZERO. It lets you control whether an error is generated when a calculation produces a decimal result larger than it can handle.