Neoview SQL Reference Manual (R2.2)

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 18 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. The maximum
precision for exact numeric data types is 18 digits. The maximum precision for REAL and DOUBLE
PRECISION data types is approximately 16.5 digits (54 bits).
Neoview SQL converts floating-point data types following these rules:
• Neoview SQL cannot convert an HP REAL or a FLOAT data type with precision between
1 and 22 bits to IEEE REAL, because the HP exponent will not fit in an IEEE REAL data type.
The precision of an HP data type will be maintained correctly.
• There is no equivalent to an HP REAL in IEEE floating-point data type which preserves the
precision and exponent. If you want a small floating-point data type with less exponent and
less storage, declare columns as REAL. If you want more exponent and more precision,
declare it as DOUBLE or FLOAT.
218 SQL Language Elements