Float To Numeric (25,7)

[gsherry1]

Hello Forum,

I am mapping floats sourced from SQL Enterprise to target of Numeric(25,7).
The size of the target almost guarantees that I will have some visible difference in the actual values, especially since I have no specifications for truncation or rounding.

My average mapping from float to such a target results in a difference of 0.001.

The worst difference in actual magnitude between source and target is
Source:
3178248133570.72

Target:
3178248011776.0000000

With a difference of:
121794.720214844

I realize this is accurate to 7 digits, and I might be asking for a little much to get something better. I've tried several things to reduce the issue:
1) Change source metadata from float to double. This is something that DataStage seems to do automatically with a warning:

Input: When checking operator: When binding output interface field "FIELD_NAME" to field "FIELD_NAME": Implicit conversion; from source type "dfloat" to result type "sfloat": Possible range/precision limitation

2) Using an explicit conversion function from float (15) to numeric (25,7):
DfloatToDecimal(LookupResults.FIELD_NAME, "round_inf")

Both of these changes resulted in the same results as relying on implicit conversion to target.

From reading some of the posts here, it seems that this is fairly typical for such conversions, although I am pressed to ask if anybody has any other suggestions for improving the result (other than making the target a float as well). My Business users are not going to be impressed with the above example.

Also, is DfloatToDecimal(LookupResults.FIELD_NAME, "round_inf") the preferred conversion method and in which situations will it outperform the implicit conversion?

Your input is appreciated.

Thanks,

Greg

[ArndW]

Greg, does PX do any better if you input the data as a string and then convert to double precision? It might be that the loss of precision can be avoided by doing that.

[gsherry1]

Thanks for your suggestion ArndW,

I changed all source metadata columns to varchar(50) from float(15), and used the StringToDecimal() function to convert to Numeric(25,7) target.

For the most part, the function did a good job in conversion. However there was some major issues when the source string contained more than 8-9 digits:

8 to 9 digit source string -> mapped to 0.000 on target.
7 to 8 digit source string -> Fairly good representation in target.
<7 digit source string -> Source and target differed by less than 10E-14 and usually calculated a difference of 0. Better than previous method for some small values.

Average precision in target differed by 0.04 when I exclude the mapped to 0 cases. The average amount of digits that matched was 7.


I do not understand why 8 to 9 digit strings are coming back as 0. This does not occur during the read from the source database into DataStage, but in the StringToDecimal function itself. Any suggestions?


Greg

[ArndW]

I think it is time to delve into the environment variables for precision in PX - I'm not at a PC with the PDFs right now, but perhaps you can find the relevant settings or someone here will add them to this thread.

[sri1dhar]

I had similar issue with floats when using Sybase Enterprise stage. I tried several things that didn't help.

Finally it worked when I used Basic Transormer and Sybase OC stage instead of PX Transformer, Sybase Enterprise stage with float defined as decimal in the column metadata.

Eventually we ended up developing a server job becuase we didn't want to use BASIC Transformer in PX jobs.

[gsherry1]

Not certain if these were the environment variables to which you were referring, but setting the following on my jobs did not have any affect on the target being mapped to zero when using StringToDecimal(), or the average deviation between source and target when using StringToDecimal() or DFloatToDecimal():

$APT_DECIMAL_INTERM_SCALE = 30
$APT_DECIMAL_INTERM_PRECISION = 70



Greg

[gsherry1]

Update:

By changing my imported metadata source fields from float to the same as my target decimal(25,7), all these problems dissapear. DataStage still throws a conversion precision warning upon reading the data into the DataStage job, but the precision loss is much more acceptable (10E-12 typically).

I usually consider best practice to use the datatypes to read in data that the import generator chooses for you, and then perform an explicit conversion to target datatype. However, in this case that had unacceptable results.

If anybody has a solution that doesn't require me to change my imported source metadata, I'll be happy to hear it.

Greg

[ray.wurlod]

Change the source!

[blewip]

I know this has been marked as resolved, however I don't think this works properly. If the float is defined as a Decimal in the input (Sybase) stage, we are relying on an implicit conversion. I am finding that for certain fields we are losing records

Security_View,0: Unable to set output decimal field value from argument string '9.9999999999999995e-07' in function setOutputFieldAsDecimal() for field '24' in output dataset '0'.

This is not giving me a precision warning, it is dropping the record. Better to define the input as double, then convert to Decimal using a explicit conversion.

DecimalToDFloat(From_Security_View_Data.CPN_RT)