Decimal calculations
Moderators: chulett, rschirm, roy
Decimal calculations
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
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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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 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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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?
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Re: Decimal calculations
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
MaDFroG
Re: Decimal calculations
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
MaDFroG
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
The row generator does not support the decimal datatype. The row generator converts decimals to floats.nPower wrote: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
Try putting your initial values into a sequential file and get the values from the file versus using the row generator.
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.
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.