SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
E-24
Considerations—Expressions
Dividing by 0 causes an error.
Exponentiation is allowed only with numeric data types but the operands can be of
any numeric type. If the first operand is 0, 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
an integer.
Exponentiation is subject to rounding error. Results should be considered to be
approximate. If your application requires exact values, use the exponentiation
function in your host language.

This paragraphs describe how SQL computes the precision and scale 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. Precision equals the sum of
the magnitude and the scale.
For example, a column declared as NUMERIC (18,5) has a precision of 18, a
magnitude of 13, and a scale of 5. This literal has a precision of 9, a magnitude of
5, and a scale of 4:
12345.6789
SQL computes precision, magnitude, and scale during the evaluation of an
expression. When SQL detects an operator in the expression, it applies This rules:

If the operand is + or -, the resulting scale is the maximum of the scales of the
first and second operands. The resulting precision is the maximum of the
magnitudes of the first and second operands, plus the scale of the result, plus
1.
If the operator is *, the resulting scale is the sum of the scales of the first and
second operands. The resulting precision is the sum of the magnitude of the
first operand, the magnitude of the second operand, and the scale of the result.

If the precision becomes greater than 18, the resulting precision is set to 18. If
the expression contained a division operator (/), the resulting scale is the
maximum of 0 and (18- (result precision - result scale)). Both
operands are truncated to the resulting scale.

If the operator is /, the resulting precision equals 18 and the resulting scale is
the maximum of 0 and (18 - magnitude operand1 - scale operand2).
Consider this expression:
(100.00 - ((COL1 * 100.00) / COL2))
Suppose that the operands are defined:
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