7 Replies Latest reply on Sep 6, 2021 4:08 AM by Nico Heinze

    Target decimal issue

    Chang Liu New Member

      Hi,

       

      I have an issue when loading data to target.

      Souce are double(17,2) columns in salesforce.

      Target are 3 varchar2 (18) columns in Oracle.

      Souce data:

      123456789012345.12234567890123451.23345678901234512.34

      Target data:

      123456789012345.12234567890123451.21345678901234512.31

      As you can see, in the 2nd and 3rd columns in the target, the decimal part are not correct.

      What could be the possible reason and how to fix it?

       

      Thanks & Regards,

      Chang

        • 1. Re: Target decimal issue
          user140848 Guru

          Hi Chang,

           

          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.

           

          Regards, Sacha

          • 2. Re: Target decimal issue
            Chang Liu New Member

            Hi Sacha,

             

            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,

            hang

            • 3. Re: Target decimal issue
              Nico Heinze Guru

              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.

               

              Regards,

              Nico

              • 4. Re: Target decimal issue
                user140848 Guru

                Hi Nico,

                 

                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.

                 

                Regards, Sacha

                • 5. Re: Target decimal issue
                  Nico Heinze Guru

                  Hi Sacha,

                   

                  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.

                   

                  Regards,

                  Nico

                  • 6. Re: Target decimal issue
                    user140848 Guru

                    Hi Nico,

                     

                    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?

                     

                    Regards, Sacha

                    • 7. Re: Target decimal issue
                      Nico Heinze Guru

                      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,

                      Nico