MULTIPLY_ALT
The MULTIPLY_ALT scalar function returns the product of the two arguments as a decimal value. It is provided as an alternative to the multiplication operator, especially when the sum of the precisions of the arguments exceeds 63.
- expression-1
- An expression that returns a value of any built-in numeric data type (other than floating-point or decimal floating-point), character-string, or graphic-string data type. A string argument is cast to double-precision floating point before evaluating the function. For more information about converting strings to double-precision floating point, see DOUBLE_PRECISION or DOUBLE.
- expression-2
- An expression that returns a value of any built-in numeric data type (other than floating-point or decimal floating-point), character-string, or graphic-string data type. A string argument is cast to double-precision floating point before evaluating the function. For more information about converting strings to double-precision floating point, see DOUBLE_PRECISION or DOUBLE. expression-2 cannot be zero.
The result of the function is a DECIMAL. The precision and scale of the result are determined as follows, using the symbols p and s to denote the precision and scale of the first argument, and the symbols p' and s' to denote the precision and scale of the second argument.
- The precision is MIN(mp, p+p')
- The scale is:
- 0 if the scale of both arguments is 0
- MIN(ms, s+s') if p+p' is less than or equal to mp
- MIN(ms, MAX( MIN(3, s+s'), mp-(p-s+p'-s') ) ) if p+p' is greater than mp.
For information about the values of p, s, ms, and mp, see Decimal arithmetic in SQL.
If either argument can be null, the result can be null; if either argument is null, the result is the null value.
The MULTIPLY_ALT function is a better choice than the multiplication operator when performing decimal arithmetic where a scale of at least 3 is wanted and the sum of the precisions exceeds 63. In these cases, the internal computation is performed so that overflows are avoided and then assigned to the result type value using truncation for any loss of scale in the final result. Note that the possibility of overflow of the final result is still possible when the scale is 3.
The following table compares the result types using MULTIPLY_ALT and the multiplication operator when the maximum precision is 31 and the maximum scale is 31:
| Type of Argument 1 | Type of Argument 2 | Result using MULTIPLY_ALT | Result using multiplication operator |
|---|---|---|---|
| DECIMAL(31,3) | DECIMAL(15,8) | DECIMAL(31,3) | DECIMAL(31,11) |
| DECIMAL(26,23) | DECIMAL(10,1) | DECIMAL(31,19) | DECIMAL(31,24) |
| DECIMAL(18,17) | DECIMAL(20,19) | DECIMAL(31,29) | DECIMAL(31,31) |
| DECIMAL(16,3) | DECIMAL(17,8) | DECIMAL(31,9) | DECIMAL(31,11) |
| DECIMAL(26,5) | DECIMAL(11,0) | DECIMAL(31,3) | DECIMAL(31,5) |
| DECIMAL(21,1) | DECIMAL(15,1) | DECIMAL(31,2) | DECIMAL(31,2) |
Examples
- Multiply two values where the data type of the first argument
is DECIMAL(26,3) and the data type of the second argument is DECIMAL(9,8).
The data type of the result is DECIMAL(31,7).
Returns the value 536504678578875294857887.5277415.SELECT MULTIPLY_ALT(98765432109876543210987.654,5.43210987) FROM SYSIBM.SYSDUMMY1Note that the complete product of these two numbers is 536504678578875294857887.52774154498, but the last 4 digits are truncated to match the scale of the result data type. Using the multiplication operator with the same values will cause an arithmetic overflow, since the result data type is DECIMAL(31,11) and the result value has 24 digits left of the decimal, but the result data type only supports 20 digits.