When you use integer values in calculations, the PowerCenter Integration Service sometimes converts integer values to floating-point numbers before it performs the calculation. For example, to evaluate MOD( 12.00, 5 ), the PowerCenter Integration Service converts the integer value “5” to a floating-point number before it performs the division operation. The PowerCenter Integration Service converts integer values to double or decimal values depending on whether you enable high precision.
The PowerCenter Integration Service converts integer values in the following arithmetic operations:
Functions and calculations that cannot introduce decimal points.
For example, integer addition, subtraction, and multiplication, and functions such as CUME, MOVINGSUM, and SUM.
Non-scientific functions and calculations that can introduce decimal points.
For example, integer division, and functions such as AVG, MEDIAN, and PERCENTILE.
All scientific functions and the EXP, LN, LOG, POWER, and SQRT functions.
The transformation Double data type supports precision of up to 15 digits, while the Bigint data type supports precision of up to 19 digits. Therefore, precision loss can occur in calculations that produce Bigint values with precision of more than 15 digits.
For example, an expression transformation contains the following calculation:
POWER( BIGINTVAL, EXPVAL )
Before it performs the calculation, the PowerCenter Integration Service converts the inputs to the POWER function to double values. If the BIGINTVAL port contains the Bigint value 9223372036854775807, the PowerCenter Integration Service converts this value to 9.22337203685478e+18, losing the last 4 digits of precision. If the EXPVAL port contains the value 1.0 and the result port is a Bigint, this calculation produces a row error since the result, 9223372036854780000, exceeds the maximum bigint value.
When you use an Integer data type in a calculation that can produce decimal values and you enable high precision, the PowerCenter Integration Service converts the integer values to decimal values.
For transformations that support the Decimal data type with precision up to 28 digits, precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits in high precision mode. In this case, the PowerCenter Integration Service stores the result as a double. If the port precision is less than or equal to 28 digits and the result produces a value greater than 28 digits in high precision mode, the PowerCenter Integration Service rejects the row.