SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
E-25
Considerations—Expressions
SQL evaluates the expression:

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.

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.

You can use date-time and INTERVAL operands with arithmetic operators only in
these combinations:
a. In a date-time or INTERVAL expression, you can specify fields for the result
with a range of fields following the expression. For example, this expression
gives the result 09-17:
( DATE "1988-09-22" - INTERVAL "5" DAY ) MONTH TO DAY
b. If you subtract a date-time value from another date-time value, both values
must have the same range of date-time field.
c. Adding an INTERVAL of MONTHS to a DATE value results in a value of the
same day plus the specified number of months. Because different months have
different lengths, this is an approximate result.
d. Date-time and INTERVAL arithmetic that involves MONTH and DAY fields can
yield unexpected results, depending on how the fields are used. For example,
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