Decimal calculations

[nPower]

I've got a problem multiplying 2 decimals and getting the right answer

3 stage variables a,b,c all defined as decimal 38,15
a = 1.254
b= 610097.000
c = a * b ====> 765061.637999999975424

close but not quite

Any idea how I can the right answer and maybe even why it gives the wrong one

[ArndW]

Go to the generated code for the transform stage and see what datatypes and maths are used. Since floating point numbers cannot represent all values small errors can creep in, usually the use of a representation with more precision helps but can never remove all imprecision. In your case you know both operands have only 3 digits precision after the decimal, so it makes sense to round the result to that precision as well.

[nPower]

All columns are Decial (38,15) seems enough precision to me
The values I supplied are examples not a complete list can't round as I don't know what precision I need on each one.

Another one I've had is

a = 10000.0
b = 1.245

This gives decimal places so no rounding would work as it should effectively be an integer result

[ArndW]

Where are you seeing your answer? If you do the computation in a transform stage into a Decimal(38,15) column and write that to a sequential file what value do you see?

[sachin1]

taking your values into consideration with DECIMAL(length=38 and scale=15), writing values in sequential files gives answer as 765061.638.

[ArndW]

Sachin1 - thanks for testing that, I was pretty certain that would happen. So the rounding into a single-wide floating point is probably happening at the database level, not at the time the calculation is performed.

[nPower]

Folks... Did I mention a database ?

the job is a row generator to give me a row to process nothing more.
then a transformer with 3 stage variables 2 with hardcoded values and a peek output for the 3 stage variables.

it's a mock up of what i have in a real job, in an attempt to isolate the problem.

No database, no input files or datasets, even the row from the generator has no bearing on the process

[ArndW]

If you replace the PEEK with a sequential file output do you get the results you expect? If so, then the peek is doing the incorrect conversion.

[nPower]

The peek, a sequential file, a dataset all produce the incorrect answer.
This job runs fine on a UNIX 7.5.1 installation

[ArndW]

That sounds like the number to string math conversion used on Windows is not quad-precision.

This certainly sounds like an error in DataStage that needs to be submitted to your support provider. Out of curiosity, does it work correctly if you explicitly code in a DecimalToString() call and peek that result?

[Zhang Bo]

i think this is because decimal(38,15) is beyond the presision of Float which datastage may use to process decimal,i got the same result as you when using decimal(38,15),but when i used decimal(15,3),i got the exact right result,you can have a try and ensure whether you need a decimal(38,15),if not,reduce the presision,waiting for your feed back

[Zhang Bo]

how did you provide values for the stage variables?

[Zhang Bo]

how did you provide values for the stage variables? set initial value or get value from job parameter?if got from job parameter,that maybe the root cause,you can't choose decimal for parameter type,if take float for replace,error occurs

[Ultramundane]

Folks... Did I mention a database ?

the job is a row generator to give me a row to process nothing more.
then a transformer with 3 stage variables 2 with hardcoded values and a peek output for the 3 stage variables.

it's a mock up of what i have in a real job, in an attempt to isolate the problem.

No database, no input files or datasets, even the row from the generator has no bearing on the process

The row generator does not support the decimal datatype. The row generator converts decimals to floats.

Try putting your initial values into a sequential file and get the values from the file versus using the row generator.

[nPower]

Folks

Thanks for trying, IBM are looking for a patch.

To answer a few questions, I'll repeat what I've said before

1. The values are hard coded into the stage variables - initial value not deivation

2. The row generator is ther to provide a row not data so it doesn't matter that it doesn't support the Decimal data type.

3. I know that if I specify it as a decimal nn,3 I get the right answer, the point is I know that 3 decimals places will not be the right answer for all values.