NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
E-26
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, then the second operand must be greater
than 0, and the result is 0. If the second operand is 0, then the first operand cannot
be 0, and the result is 1. If the first operand is negative, then 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.
•
Determining precision, magnitude, and scale of results
The following 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.
For example, a column declared as NUMERIC (18,5) has a precision of 18, a
magnitude of 13, and a scale of 5. The following 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 the following
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 maximum 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 ).
°
The resulting precision equals the sum of the magnitude and the scale.
Consider the following expression:
(100.00 - ((COL1 * 100.00) / COL2))