1 Reply Latest reply on Apr 13, 2018 9:26 AM by Nico Heinze

    Replace Double datatype column with NULL value

    mayur marathe Seasoned Veteran

      Hi All

      I have mapping in which we are getting the data from Web-service and placing data in table, mapping structure is like this

       

      Source - SQ- Java transformation - Lookup - exp- router - 2 instances of same target

       

      My data comes as conversion rate for currencies , if the rate comes as zero then i need to replace that 0 with NULL , datatype in router is double and in target table data type is Float , i have tried replacestr, replacechr,reg_replace (by making the datatype from double to strin ) but it is still  placing the data as zero.

       

      can you anyone help in this issue

       

      Thanks

      mayur

        • 1. Re: Replace Double datatype column with NULL value
          Nico Heinze Guru

          That's most likely because the Double data type is a floating-point number type. And if you have to write e.g. to a target which expects a DECIMAL(16,2), then the Double value will be rounded to 2 fractional digits. But that doesn't mean that the Double value really has a fractional part of 0, only a value "near" 0.

          Probably that's the point in your mapping: the Double value may be something like 0.00000056357, and that is not equal to 0.

          "Truncating" the Double value to e.g. 4 fractional digits usually won't help because the 4 fractional digits are not accurate either, this is still a floating-point value.

           

          There are basically two things you can do:

          Assign the Double value to a DECIMAL(18,2) before the Router and compare this (now rounded) value to 0.0; if it's "equal" to 0.0, then forward NULL through the Router, otherwise forward the DECIMAL value through the Router.

           

          Or you assign the Double value to a String, compare this string to 0.0 and - if equal - forward it as NULL, and then you forward this String value to the target.

           

          That's a "natural" behaviour of floating-point values.

           

          Regards,

          Nico