| Author |
Message
|
| javaforvivek |
 Posted: Sun Dec 18, 2005 11:26 pm Post subject: problem with round function |
 |
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Hi, WBIMB 5 + CSD5 on Win2k
I have a following input message:
<Document>
<Amount>3.705</Amount>
</Document>
My input domain is XML. in my compute node, I have following code:
| Code: |
CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
DECLARE amount DECIMAL;
SET amount = InputRoot.XML.Document.Amount;
SET OutputRoot.XML.Document.Amount = ROUND(amount , 2);
END;
|
The expected output is
<Document>
<Amount>3.71</Amount>
</Document>
but I am getting:
<Document>
<Amount>3.7</Amount>
</Document>
What's the problem??
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Sun Dec 18, 2005 11:45 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi javaforvivek,
Did you check the value that is stored in the DECIMAL amount ?
I have a feeling it is not 3.705.
Regards. |
|
| Back to top |
|
|
| javaforvivek |
| Posted: Mon Dec 19, 2005 12:32 am Post subject: |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Yes, I checked it in Flow Debug.. it is 3.705
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| javaforvivek |
| Posted: Mon Dec 19, 2005 12:36 am Post subject: |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
This is what I found in ESQL - DECIMAL datatype help:
| Quote: |
All automatic rounding is banker's or half even symmetric rounding. The rules of this are:
When the first dropped digit is 4 or less, the first retained digit is unchanged
When the first dropped digit is 6 or more, the first retained digit is incremented
When the first dropped digit is 5, the first retained digit is incremented if it is odd, and unchanged if it is even. Therefore, both 1.5 and 2.5 round to 2 while 3.5 and 4.5 both round to 4
Negative numbers are rounded according to the same rule
|
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Mon Dec 19, 2005 12:51 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Great, u solved your problem 
Please mark the topic solved, I'm sure this will be help in the future.
Regards. |
|
| Back to top |
|
|
| javaforvivek |
| Posted: Mon Dec 19, 2005 4:41 am Post subject: |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Elvis,
This is not what I was looking for.
My ultimate aim to round off the amount values in my output message to two decimal places.
I dont know how many decimal places will be there in each amount field.
What I want is 'normal' working of ROUND functionality.
i.e.,
If a decimal has first dropped digit >= 5, then increment the first retained digit by one
If a decimal has has first dropped digit <= 4, then the first retained digit will be unchanged.
For e.g.,
If I have 1.2345 and want to round it to two decimal places, it should give me 1.24. The required algorithm is like this:
integer a = 1.2345;
then a = 1.235 (when first dropped digit is >= 5, increment the first retained digit by one)
then a = 1.24 (when first dropped digit is >= 5, increment the first retained digit by one)
This is what I am looking for..
The Banker's Rounding rule will work like this:
Integer a = 1.2345
then a = 1.234 (When the first dropped digit is 5, the first retained digit is incremented if it is odd, and unchanged if it is even)
then a = 1.23 (When the first dropped digit is 4 or less, the first retained digit is unchanged)
If you observe the outcome, it is different in the two algorithms.
I want the first result.. i.e., a = 1.24
and
NOT a = 1.23
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Dec 19, 2005 4:56 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
The documentation for v5 ROUND claims that the banker's algorithm (that you don't want) only applies for values of type DECIMAL.
You could multiply your values by the next larger power of ten to the precision you want, conver to integer, round, and then divide to restore your decimal.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| javaforvivek |
| Posted: Tue Dec 20, 2005 3:10 am Post subject: |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Jeff,
I dont really get what you have said.
Can you please illustrate it by using my first input message, i.e.,
<Document>
<Amount>3.705</Amount>
</Document>
Thanks for the advice.
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Dec 20, 2005 4:30 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Okay, so we want to round 3.705 by one position.
So we multiply by 10, raised to a power. The power in this case is 3, because we want to shift the 5 to after the decimal.
3.705 * 1000 = 3705.00
Then we turn that into an INTEGER.
| Code: |
declare myTempInt INTEGER;
set myTempInt = cast (InputBody.Document.Amount *1000 as INTEGER); |
Then we round.
| Code: |
| set myTempInt = ROUND(myTempInt); |
Because we're working with an integer, and not a decimal, my theory is that this will use the simpler rounding algorithm that you want, and not use the banker's algorithm.
But I can't test it myself.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Dec 20, 2005 11:31 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| jefflowrey wrote: |
Okay, so we want to round 3.705 by one position.
So we multiply by 10, raised to a power. The power in this case is 3, because we want to shift the 5 to after the decimal.
3.705 * 1000 = 3705.00
Then we turn that into an INTEGER.
| Code: |
declare myTempInt INTEGER;
set myTempInt = cast (InputBody.Document.Amount *1000 as INTEGER); |
Then we round.
| Code: |
| set myTempInt = ROUND(myTempInt); |
Because we're working with an integer, and not a decimal, my theory is that this will use the simpler rounding algorithm that you want, and not use the banker's algorithm.
But I can't test it myself. |
Sorry Jeff that won't work. As a syllogism try to tell me what the result of rounding an integer is ??
Just do simple maths:
before the round add 0.5 of the rounding units to the number and you should have something that fits the bill ....
rounded = (original * 10**decimals + 0.5) * 10 **(-decimals)
so in case of 3 decimals:
| Code: |
rounded = (original * 1000 +0.5)/1000 or
rounded = (original * 10000 + 5) /10000 |
Now of course you have to make sure that all the implicit casts in the math formula go the way you want....
Enjoy  |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Dec 20, 2005 11:42 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Dec 20, 2005 2:59 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Sure but by using the ESQL ROUND function wouldn't you get the same rounding rule (bankers) you were trying to escape ?
My question is really: what makes you think that using an Integer instead of a decimal will change anything to the way the rounding algorithm is performed ?
Isn't each rounding algorithm a mathematical construct using a *10**x + addition function + integer function + *10**-x function and variations on the same ?? Allowing x to be negative does not change anything to the algorithm.
Notice I forgot the Integer function in my exemple...
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Dec 20, 2005 7:10 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
The reason I suspect that using an Integer may use a different algorithm is because the documentation is specific that the DECIMAL type uses the banker's algorithm. It is non-specific about the algorithm used for any other type.
| Quote: |
| When rounding a DECIMAL, the banker's or half even symmetric rounding rules are used. |
Thus, I thought a quick check might be productive.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Dec 20, 2005 9:04 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Hmmm....
Let's go back a second and check between precision and display as I think there is some confusion there:
| javaforvivek wrote: |
What I want is 'normal' working of ROUND functionality.
i.e.,
If a decimal has first dropped digit >= 5, then increment the first retained digit by one
If a decimal has has first dropped digit <= 4, then the first retained digit will be unchanged.
For e.g.,
If I have 1.2345 and want to round it to two decimal places, it should give me 1.24. The required algorithm is like this:
integer a = 1.2345;
then a = 1.235 (when first dropped digit is >= 5, increment the first retained digit by one)
then a = 1.24 (when first dropped digit is >= 5, increment the first retained digit by one)
This is what I am looking for..
The Banker's Rounding rule will work like this:
Integer a = 1.2345
then a = 1.234 (When the first dropped digit is 5, the first retained digit is incremented if it is odd, and unchanged if it is even)
then a = 1.23 (When the first dropped digit is 4 or less, the first retained digit is unchanged)
If you observe the outcome, it is different in the two algorithms.
I want the first result.. i.e., a = 1.24
and
NOT a = 1.23 |
Well this I have a problem with:
if a=1.2345 rounding to 2 decimals (see formula)
should give you 1.23 = Int(1.2345*100+.5)/100 or Int(123.95)/100
Looking at precision and rounding can make rounding confusing:
value 1.2346 display 3 decimals = 1.235 rounds at 2 dec 1.23
value 1.2346 rounds @3 dec 1.235 rounded again @ 2 dec 1.24
Notice that in the blue line the rounding algorithm was performed twice: once on the original value @3 decimals and once on the rounded value to bring it to 2 decimals. Thus you do not get the same result as if you performed the algorithm only once to round @ 2 decimals...
Understand the difference between rounding display and precision...
Sorry |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed Dec 21, 2005 10:20 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi,
So have we come to a conclusion that it cannot be done within a single line of code and using complex Mathematics ?
You could call a function, which will do something like this
call a function by providing the decimal number and the precision as inputs.
In the function:
WHILE ( precision has not been met OR precision is not lesser or equal already )
[
------if (the decimal at the last position is 5)
------{
-----------remove it and increment the previous by 1.
------}
------else
------{
----------ROUND the decimal.
------}
]
Just a suggestion 
Regards. |
|
| Back to top |
|
|
|
|
