Neoview SQL Reference Manual (R2.4)

Numeric expressions are evaluated according to these additional rules:
• An expression with a numeric operator evaluates to null if any of the operands is null.
• Dividing by 0 causes an error.
• Exponentiation is allowed only with numeric data types. If the first operand is 0 (zero), the
second operand must be greater than 0, and the result is 0. If the second operand is 0, the
first operand cannot be 0, and the result is 1. If the first operand is negative, the second
operand must be a value with an exact numeric data type and a scale of zero.
• Exponentiation is subject to rounding error. In general, results of exponentiation should be
considered approximate.
Precision, Magnitude, and Scale of Arithmetic Results
The precision, magnitude, and scale are computed during the evaluation of an arithmetic
expression. Precision is the maximum number of digits in the expression. Magnitude is the
number of digits to the left of the decimal point. Scale is the number of digits to the right of the
decimal point.
For example, a column declared as NUMERIC (18, 5) has a precision of 18, a magnitude of 13,
and a scale of 5. As another example, the literal 12345.6789 has a precision of 9, a magnitude of
5, and a scale of 4.
The maximum precision for exact numeric data types is 128 digits. The maximum precision for
the REAL data type is approximately 7 decimal digits, and the maximum precision for the
DOUBLE PRECISION data type is approximately 16 digits.
When Neoview SQL encounters an arithmetic operator in an expression, it applies these rules
(with the restriction that if the precision becomes greater than 18, the resulting precision is set
to 18 and the resulting scale is the maximum of 0 and (18- (resulted precision - resulted
scale)).
• If the operator is + or -, the resulting scale is the maximum of the scales of the operands. The
resulting precision is the maximum of the magnitudes of the operands, plus the scale of the
result, plus 1.
• If the operator is *, the resulting scale is the sum of the scales of the operands. The resulting
precision is the sum of the magnitudes of the operands and the scale of the result.
• If the operator is /, the resulting scale is the sum of the scale of the numerator and the
magnitude of the denominator. The resulting magnitude is the sum of the magnitude of the
numerator and the scale of the denominator.
For example, if the numerator is NUMERIC (7, 3) and the denominator is NUMERIC (7, 5), the
resulting scale is 3 plus 2 (or 5), and the resulting magnitude is 4 plus 5 (or 9). The expression
result is NUMERIC (14, 5).
Conversion of Numeric Types for Arithmetic Operations
Neoview SQL automatically converts between floating-point numeric types (REAL and DOUBLE
PRECISION) and other numeric types. All numeric values in the expression are first converted
to binary, with the maximum precision needed anywhere in the evaluation.
Examples of Numeric Value Expressions
These are examples of numeric value expressions:
Numeric literal.
-57
The product of the values in the SALARY column and a
numeric literal.
salary * 1.10
The product of the values in the UNIT_PRICE and
QTY_ORDERED columns.
unit_price * qty_ordered
An expression whose operands are numeric literals.
12 * (7 - 4)
Function applied to the values in a column.
COUNT (DISTINCT city)
Expressions 251