The double data type only has around 15 digits of precision. Therefore you can expect to see this sort of issue with numeric values with more than 15 digits.
If you need more precision than this, your best option is to use the decimal datatype in Informatica, with high precision enabled for the session (Properties tab, Performance group, option "Enable high precision".) This configuration can support up to 28 digits of precision.
Thanks for the prompt comment.
Double type is in Salesforce, it seems it holds the number very well. So I recon there is no issue with the double in source.
Addtionally, it is fobbiden to enable the high precision option due to force change to existed columns.
Is there any reason else that could be considered?
Thanks & Regards,
I don't know Salesforce, so this is a dumb question, but if the SFDC data type DOUBLE(17,2) is indeed implemented as a DOUBLE data type (as it's named in PowerCenter), then JanLeendert's remark holds valid (please see the Wikipedia article about "Floating-Point Arithmetic" for some explanations and many details). DOUBLE values can hold no more than 15 digits in accurate manner, no matter what other people say. That's a plain fact.
If, however, this value is read as a DECIMAL by the mapping, then please check whether the length of the DECIMAL (and the data type) is passed on through the mapping correctly. If, for example, some value uses any numeric function other than addition, subtraction, multiplication, or division based on this value, then the number will implicitly (and without notice anywhere) be converted to a DOUBLE value before processing continues. And such conversion may cause this trouble.
So at this point it boils down to the question whether PowerCenter receives this value as a DOUBLE or as a DECIMAL data type.
DOUBLE values inherently cannot be accurate to more than 15 digits (see IEEE 754 for details). In this case you will have to take other measures to extract the value as a String or as a DECIMAL value from Salesforce; after having done so, you can safely move forward this value through your mapping without losing any precision.
DECIMAL values can hold (without Enable High Precision) at most 18 digits in total, so a DECIMAL(17,2) is well within boundaries and should work fine.
BTW what do you mean that you cannot set Enable High Precision? I admit it, I did not understand your remark.
The help docs for my version of Informatica say that with high precision off, decimal data types of precision 15 to 28 are handled as double - hence why I advised turning high precision on. Where did you get the limit of 18?
Chang, if high precision is not an option then I think Nico's suggestion of extracting the value from Salesforce as a string and then just passing that through your Informatica mapping is the best way to go.
I think the info about 18 digits in total is from the Designer manual (chapter Data Types), but I would have to double-check first, I've written that off the top of my head. Nevertheless I am 100% sure about this fact.
And yes, without Enable High Precision everything from DECIMAL 19 through 28 will automatically be converted to a DOUBLE.
That's where I got my information from as well! See for example the below table.
The wording in the help file does suggest that possibly a straight source to target mapping with no transformations might not be affected. But if there's any intervening transformation using decimals over 15 digits precision, they will be converted to double internally in the transformation unless the high precision option is selected.
I looked in a few places and couldn't see anywhere that mentioned 18 digits for decimal datatype.
Still all this is a bit of a tangent, I suppose.
Chang, you said you weren't allowed to enable high precision for fear of changing behaviour elsewhere. Is the person making this rule aware that high precision can be enabled for just this one session without affecting any other session?
Good question. To be honest I can't find it at this moment. I'll have to search for these details and come back here later, whenever I find the time to look after it.
Thanks and regards,