1 2 Previous Next 18 Replies Latest reply on Apr 10, 2020 5:47 AM by Arun Kumar

    Additional decimals 9999.. showing up

    Arun Kumar Guru

      Hello,

       

      I have a situation where the value output in the target is showing additional decimal digits. For example, in the source (Oracle view, data type is number) the value is 38471.56 but my target (flat file .txt is showing 38471.559999). When data is pulled from the source SQ shows double data type

       

      This is what i have done, none of the below worked

       

      SQ (Double) --> EXP (nstring) --> Target (nstring)

      SQ(Double)--->Decimal to  Char - > Tgt nstring

      SQ(Double)--->Decimal - > Tgt number

       

      Tried with both high precision on and off.

       

      Any suggestions on how to fix this?

       

      Thanks,
      Arun

        • 1. Re: Additional decimals 9999.. showing up
          user126898 Guru

          On the SQ to EXP where you used DECIMAL did you define the precision and scale to be 10,2 ?

          • 2. Re: Additional decimals 9999.. showing up
            user126898 Guru

            Here is a quick mock up of what you are doing.  Defining the SQ as a number 15,2 kept the decimals the same. 

             

             

             

             

            • 3. Re: Additional decimals 9999.. showing up
              Arun Kumar Guru

              It won't be always 2 decimals,it could be like 4443.46518. Any value has to be brought as is from source.

              • 4. Re: Additional decimals 9999.. showing up
                user126898 Guru

                 

                 

                 

                 

                You can make the inbound decimal whatever scale you want, the trim will reomve the trailing 0's that it puts on the fields.

                1 of 1 people found this helpful
                • 5. Re: Additional decimals 9999.. showing up
                  Nico Heinze Guru

                  The background is a little tricky.

                  PowerCenter cannot source the DOUBLE data type from Oracle. PowerCenter can source NUMBER and NUMBER(P,S) values, but no DOUBLE.

                  Meaning a DOUBLE value always gets converted to some NUMBER (or NUMBER(p,s)) before being delivered to PowerCenter by the Oracle client. And as DOUBLE values are inherently inaccurate (thanks to the technical details of floating-point arithmetic, the Wikipedia articles about this matter are really good and informative), there are rounding errors when converting a DOUBLE value to any NUMBER data type. And these rounding errors show up throughout the whole mapping.

                   

                  There's not much you can do about it.

                  It's highly unlikely that PowerCenter will ever be able to source DOUBLE values from Oracle as a DOUBLE data type. Not impossible, just unlikely. So I wouldn't count on Informatica to implement that.

                  So there are only two other choices you have (which I can see).

                  First you can use a SQL Override in your Source Qualifier which converts the DOUBLE value to some NUMBER value and delivers this value to PowerCenter. That MAY lead to Oracle rounding the value in a useful manner.

                  Or you will have to check for yourself whether you got a series of ...9999 at the end or (just as possible) a ...0001. And if so, round this stuff somehow.

                  Not easy but I don't see any other way.

                  Except (of course) changing the data type in Oracle.

                  To be frank I cannot understand why anyone (who understands this matter) would ever use a DOUBLE value in a commercial database. They are inherently inaccurate, that's just a matter of fact.

                   

                  Regards,

                  Nico

                  1 of 1 people found this helpful
                  • 6. Re: Additional decimals 9999.. showing up
                    Arun Kumar Guru

                    In Oracle it is Number datatype, where as in Informatica it is showing as double.

                     

                    I saw this KB below, which says how to do if target is a Oracle table. But my issue target is a flat file.

                     

                    https://kb.informatica.com/solution/23/Pages/5/159142.aspx

                    • 7. Re: Additional decimals 9999.. showing up
                      Arun Kumar Guru

                      It works only in some cases. Since the datatype in Oracle is number, it can hold both decimal and non decimals.

                      I have values like 11230 and 1154.325. It works for 1154.325 but for 11230, the last 0 is truncated and output becomes 1123.

                      • 8. Re: Additional decimals 9999.. showing up
                        Nico Heinze Guru

                        You should extend the expression term. Right now all trailing zeroes are removed. That of course is correct only if these trailing zeroes belong to a fractional part of the number. If the number is an integer (i.e. no decimal point), then of course the trailing zeroes must remain where they are.

                         

                        So, my suggestion is to first translate the number into a variable port of type String using TO_CHAR().

                        Next you use the INSTR() function to search for a decimal point '.' in this string.

                        If there is none, forward the string as is.

                        If there is one, then remove trailing zeroes and store the result in another variable port of type String.

                        If this second string ends with a period '.' (which can happen if the number is e.g. 13.0000), then remove the period, otherwise you can forward the string as is.

                        Regards,
                        Nico
                        • 9. Re: Additional decimals 9999.. showing up
                          Arun Kumar Guru

                          I handled your first two suggestions but how to code the third one (13.0000)? My code so far is as below -

                           

                          iif(instr(to_char(ASSET_NO),'.')=0,to_char(ASSET_NO), iif(instr(to_char(ASSET_NO),'.')>0,rtrim(to_char(ASSET_NO),'0'),to_char(ASSET_NO)))

                           

                          Both datatype in the expression and target is nstring.

                          • 10. Re: Additional decimals 9999.. showing up
                            Nico Heinze Guru

                            I would split up the whole thing into several variable ports, this makes the details easier to read and to maintain.

                             

                            First a variable port v_asset_no of type String:

                                To_Char( ASSET_NO)

                            Now a variable port v_pos_dot of type Integer:

                                InStr( v_asset_no, '.')

                            Finally an output port of type String:

                                IIF( v_pos_dot = 0,

                                  v_asset_no,

                                  RTrim( v_asset_no, '0')

                                )

                             

                            Regards,

                            Nico

                            • 11. Re: Additional decimals 9999.. showing up
                              Arun Kumar Guru

                              This is working if the value has a decimal places like 0.35, 1235.414 etc. But the value do not have a decimal place like 1061450 or 1075623, then the output leaves a dot '.' at the end as below

                               

                              1061450.

                              1075623.

                               

                              How will it behave if the value is 0.001?

                              • 12. Re: Additional decimals 9999.. showing up
                                Nico Heinze Guru

                                First I dare to disagree; if the number has no fractional part, then TO_CHAR() should deliver a string without a decimal point. If it does, we have some other problem.

                                 

                                Second this is exactly one case where you simply have a challenge to solve. This is nothing that can be handled automatically without additional effort. How is the computer supposed to "know" that this number should be rounded to 0? But that 0.002 should not be rounded? Or should it?

                                 

                                That's why I've written (in my initial response) that you have to cater for such cases yourself. You must find some logic which you can then "formulate" in PowerCenter transformation language (or whatever you consider appropriate here).

                                 

                                Regards,

                                Nico

                                • 13. Re: Additional decimals 9999.. showing up
                                  user126898 Guru

                                  take a look at the exp and see if it works.  I have done some tests and everything seems to be coming out correctly.

                                   

                                  You could cast your field in a variable port to clean things up but here is the rough draft.

                                   

                                  IIF(INSTR(TO_CHAR(PARTY_ID),'.') > 0,

                                  IIF(RTRIM(SUBSTR(TO_CHAR(PARTY_ID),INSTR(TO_CHAR(PARTY_ID),'.')+1),'0') ='',

                                  SUBSTR(TO_CHAR(PARTY_ID),1,INSTR(TO_CHAR(PARTY_ID),'.')-1),

                                  RTRIM(TO_CHAR(PARTY_ID),'0')

                                  )

                                  ,

                                  --RTRIM(TO_CHAR(PARTY_ID),'0'),

                                  TO_CHAR(PARTY_ID))

                                   

                                   

                                   

                                  • 14. Re: Additional decimals 9999.. showing up
                                    user126898 Guru

                                    First a variable port v_asset_no of type String:

                                        To_Char( ASSET_NO)

                                    Now a variable port v_pos_dot of type Integer:

                                        InStr( v_asset_no, '.')

                                    Finally an output port of type String:

                                        IIF( v_pos_dot = 0,

                                          v_asset_no,

                                          RTrim( v_asset_no, '0')

                                        )

                                     

                                    in this code 13.0000 will result in 13. because if the string has an "." the code just trims the trailing zeros leaving the .

                                     

                                    It is not the to_char but the rtrim which leaves the .

                                    1 2 Previous Next