SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-50
Numeric Value Expressions
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 NonStop SQL/MX 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
NonStop SQL/MX 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).
NonStop SQL/MX converts floating-point data types following these rules:
•
NonStop SQL/MX cannot convert a Tandem REAL or a FLOAT data type with
precision between 1 and 22 bits to IEEE REAL, because the Tandem exponent will
not fit in an IEEE REAL data type. The precision of a Tandem data type will be
maintained correctly.
•
There is no equivalent to a Tandem 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 or host variables as REAL. If
you want more exponent and more precision, declare it as DOUBLE or FLOAT.
Suppose that you have an SQL/MP table that includes a column, mympcol,
declared as REAL. If you create an SQL/MX table with a column mymxcol,
declared as REAL, you would not be able to convert the SQL/MP column mympcol
into the SQL/MX column mymxcol. You should declare the SQL/MX column as
type FLOAT or DOUBLE PRECISION.