MULTIPLY_ALT
The MULTIPLY_ALT scalar function returns the product of the two arguments. This function is an alternative to the multiplication operator and is especially useful when the sum of the precisions of the arguments exceeds 31.
>>-MULTIPLY_ALT(exact-numeric-expression-1,exact-numeric-expression-2)-><
The schema is SYSIBM.
Each argument must be an expression that returns the value of one of the following built-in numeric data types: DECIMAL, BIGINT, INTEGER, or SMALLINT.
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(31, p+p')
- The scale is:
- 0 if the scale of both arguments is 0
- MIN(31, s+s') if p+p' is less than or equal to 31
- MAX( MIN(3, s+s'), 31-(p-s+p'-s') ) if p+p' is greater than 31.
The result can be null; if any 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 you want a scale of at least 3 and the sum of the precisions exceeds 31. 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 data types from the MULTIPLY_ALT function with the result data type of the multiplication operator when decimal data is used:
| Type of Argument1 | Type of Argument2 | 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) |