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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-49
Numeric Value Expressions
•
Host variables of type NUMERIC, PIC S9()V9(), DECIMAL, SMALLINT, INTEGER,
LARGEINT, FLOAT, REAL, and DOUBLE PRECISION
•
Dynamic parameters
•
Numeric value functions
•
Aggregate functions, sequence functions, scalar subqueries, CASE expressions,
or CAST expressions that return numeric values
Considerations for Numeric Value Expressions
Order of Evaluation
1. Expressions within parentheses
2. Unary operators
3. Exponentiation
4. Multiplication and division
5. Addition and subtraction
Operators at the same level are evaluated from left to right for all operators except
exponentiation. Exponentiation operators at the same level are evaluated from right to
left. For example, X + Y + Z is evaluated as (X + Y) + Z, whereas X ** Y ** Z is
evaluated as X ** (Y ** Z).
Additional Rules for Arithmetic Operations
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.