NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
E-27
Considerations—Expressions
Assume the operands are defined as follows:
COL1 LARGEINT precision=18, scale=0, magnitude=18
COL2 LARGEINT precision=18, scale=0, magnitude=18
100.00 constant precision=5, scale=2, magnitude=3
SQL evaluates the expression as follows:
°
First perform the multiplication, ( COL1 * 100.00 ). The resulting scale is (0 +
2) = 2. The resulting precision is (18 + 3 + 2) = 23. The precision is greater than
18, so it is set to 18. The resulting magnitude is (18 - 2) = 16.
°
Next perform the division, ( result / COL2 ). The resulting precision is 18,
the resulting scale is (18 - 16 - 0) = 2, and the resulting magnitude is (18 - 2) =
16.
°
Third, perform the subtraction, (100.00 - result ). The resulting scale is
MAX(2,2) = 2. The resulting precision is MAX(3,16) + 1 + 2 = 19. Result
precision is greater than 18, so it is set to 18. A divide was previously done, so
the scale becomes MAX(0, (18 - (19 - 2)) = 1. The resulting magnitude is (18 -
17) = 1.
•
Conversion of numeric types for arithmetic operations
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 for the
evaluation. The maximum possible precision for exact numeric data types is 18
digits. The maximum for REAL and DOUBLE PRECISION data types is
approximately 16 digits.
•
Restrictions on operations with date-time or INTERVAL operands
You can use date-time and INTERVAL operands with arithmetic operators only in
the following combinations:
Operand 1 Operator Operand 2 Result Type Notes
Date-time - Date-time INTERVAL a, b
Date-time + or - INTERVAL DATETIME a, c, d
INTERVAL + Date-time DATETIME a, c, d
INTERVAL + or - INTERVAL INTERVAL a, e
INTERVAL * or / Numeric INTERVAL a, f
Numeric * INTERVAL INTERVAL a
INTERVAL / INTERVAL Numeric g