SQL Anywhere database - double, decimal and round values

Author

double, decimal and round values

George

2005-06-22, 1:23 pm

Hi everyone !

I've got a big problem vith my ASA 8.0.3 db:

in some treatments, I calculate a double value (invoice full taxes total)
from lots of +, / * operations.

most columns in my exression are double as data type.

my expression:
BASE_HT1 * ((100 + TAUX_TVA1) / 100) +
BASE_HT2 * ((100 + TAUX_TVA2) / 100) +
BASE_HT3 * ((100 + TAUX_TVA3) / 100) +
BASE_HT4 * ((100 + TAUX_TVA4) / 100) +
PORT_TAXABLE_HT * ((100 + TAUX_TVA_PORT) / 100) +
PORT_NON_TAXABLE + SHIP + OTHER_TAXES

ex: this result give 1045.0049999999999.....

I need to round this number at 2 digits: round(my_expression,
2).
This expression return 1045 (1045.00000...) but it's not true.
The real value sould be 1045.01 as 1045.00499999 goes to 1045.005 then goes
to 1045.01.

What's the problem ?

David Kerber

2005-06-22, 1:23 pm

In article <42b99779@forums-1-dub>, george.nospam@yahoo-nospam.fr
says...
> Hi everyone !
>
> I've got a big problem vith my ASA 8.0.3 db:
>
> in some treatments, I calculate a double value (invoice full taxes total)
> from lots of +, / * operations.
>
> most columns in my exression are double as data type.
>
> my expression:
> BASE_HT1 * ((100 + TAUX_TVA1) / 100) +
> BASE_HT2 * ((100 + TAUX_TVA2) / 100) +
> BASE_HT3 * ((100 + TAUX_TVA3) / 100) +
> BASE_HT4 * ((100 + TAUX_TVA4) / 100) +
> PORT_TAXABLE_HT * ((100 + TAUX_TVA_PORT) / 100) +
> PORT_NON_TAXABLE + SHIP + OTHER_TAXES
>
> ex: this result give 1045.0049999999999.....
>
> I need to round this number at 2 digits: round(my_expression,
2).
> This expression return 1045 (1045.00000...) but it's not true.
> The real value sould be 1045.01 as 1045.00499999 goes to 1045.005 then goes
> to 1045.01.
>
> What's the problem ?

It is rounding correctly, because the 3rd digit is a '4' and not a '5'.
To get it to round the way you want, you would need to round twice, the
first time to 3 digits, and then round that result to 2 digits.

Another workaround would be to multiply all your starting values by 100,
store and work with them as integers, and then divide again by 100 for
display purposes only.

Your result is one of the pitfalls of using floating point numbers for
calculations which need to be accurate to a given number of decimal
points. You should probably be using "Decimal" or "NUmeric" data types
to deal properly with financial calculations.

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Greg Fenton

2005-06-22, 1:23 pm

George wrote:
> I need to round this number at 2 digits: round(my_expression,
2).
> This expression return 1045 (1045.00000...) but it's not true.
> The real value sould be 1045.01 as 1045.00499999 goes to 1045.005 then goes
> to 1045.01.
>

No, 1045.004999999(...) rounds to 1045.00, even using "new math".

The reason is that .0049999(...) is less than .005.

When rounding to two significant digits, you only look at the third
significant digit to determine whether to round up or down. You don't
look at the fourth significant digit to round the third significant to
round the second.

Hope this helps,
greg.fenon
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

George

2005-06-23, 3:23 am

Ok , I'm agree with you and with mathematics rules ;-)

But, even Sybase is ok on round operations, PowerBuilder (and Infomaker) (of
toppic here) seems to be wrong.
I've noticed that on my app front-end. A computed field with this
expression:
"round(1045. 00499999999999999,2)
" returns 1045.01

BUT

round(1045.004999999999,2) ... and less "9" digits.... returns 1045 !!!

Of course, I'm in the first case (with an infinity range of decimal digits)
and PB doesn't return same values as Sybase.

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> a écrit dans le message de
news: 42b9a5f5$1@forums-2-dub...
> George wrote:
>
> No, 1045.004999999(...) rounds to 1045.00, even using "new math".
>
> The reason is that .0049999(...) is less than .005.
>
> When rounding to two significant digits, you only look at the third
> significant digit to determine whether to round up or down. You don't
> look at the fourth significant digit to round the third significant to
> round the second.
>
> Hope this helps,
> greg.fenon
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Breck Carter [TeamSybase]

2005-06-23, 7:23 am

Indeed, it looks like PowerBuilder has one or more problems (but not
SQL Anywhere).

To summarize: Using PB 10.0 build 4510 with SQL Anywhere 9.0.2.3124, a
DataWindow based on the following SELECT:

SELECT round(1045. 00499999999999999,2)
AS a,
round(1045.004999999999,2) AS b,
1045.00499999999999999 AS c,
1045.004999999999 AS d

....displays these values (3rd one wrong):

1045.000000000000000
1045.000000000000
1045.005000000000000
1045.004999999999

....but displays this in dbisql (all correct):

a,b,c,d
1045. 00000000000000000,10
45.000000000000,1045. 00499999999999999,10
45.004999999999

DataWindow computed fields based on these expressions:

round(1045. 00499999999999999,2)

round(1045.004999999999,2)
1045.00499999999999999
1045.004999999999

....display these values (2nd one correct):

1045.01
1045
1045.005
1045.005

There may be a rational explanation; I suggest you post this on a
PowerBuilder newsgroup.

Breck

On 23 Jun 2005 01:09:26 -0700, "George"
<george.nospam@yahoo-nospam.fr> wrote:

>Ok , I'm agree with you and with mathematics rules ;-)
>
>But, even Sybase is ok on round operations, PowerBuilder (and Infomaker) (of
>toppic here) seems to be wrong.
>I've noticed that on my app front-end. A computed field with this
>expression:
>"round(1045. 00499999999999999,2)
" returns 1045.01
>
>BUT
>
>round(1045.004999999999,2) ... and less "9" digits.... returns 1045 !!!
>
>Of course, I'm in the first case (with an infinity range of decimal digits)
>and PB doesn't return same values as Sybase.
>
>"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> a écrit dans le message de
>news: 42b9a5f5$1@forums-2-dub...
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

George

2005-06-23, 7:23 am

Thanks.

I will post this on PB newsgroup

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> a écrit dans le
message de news: c04lb1dupq7dh7qhkmaq
ot3nh4fnnfverq@4ax.com...
> Indeed, it looks like PowerBuilder has one or more problems (but not
> SQL Anywhere).
>
> To summarize: Using PB 10.0 build 4510 with SQL Anywhere 9.0.2.3124, a
> DataWindow based on the following SELECT:
>
> SELECT round(1045. 00499999999999999,2)
AS a,
> round(1045.004999999999,2) AS b,
> 1045.00499999999999999 AS c,
> 1045.004999999999 AS d
>
> ...displays these values (3rd one wrong):
>
> 1045.000000000000000
> 1045.000000000000
> 1045.005000000000000
> 1045.004999999999
>
> ...but displays this in dbisql (all correct):
>
> a,b,c,d
> 1045. 00000000000000000,10
45.000000000000,1045. 00499999999999999,10
45.004999999999
>
> DataWindow computed fields based on these expressions:
>
> round(1045. 00499999999999999,2)

> round(1045.004999999999,2)
> 1045.00499999999999999
> 1045.004999999999
>
> ...display these values (2nd one correct):
>
> 1045.01
> 1045
> 1045.005
> 1045.005
>
> There may be a rational explanation; I suggest you post this on a
> PowerBuilder newsgroup.
>
> Breck
>
>
>
>
> On 23 Jun 2005 01:09:26 -0700, "George"
> <george.nospam@yahoo-nospam.fr> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

John Smirnios

2005-06-23, 11:23 am

Remember that doubles only have about 15 or 16 decimal digits of
accuracy. 1045.00499999999999999 has way too many digits to be stored
accurately in a double. Depending on the conversion & rounding routines
used by your software, you may get slightly different double values to
represent that value. The differences in rounding that you see are
completely reasonable behaviour given the nature of doubles.

-john

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

George wrote:
> Thanks.
>
> I will post this on PB newsgroup
>
>
> "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> a écrit dans le
> message de news: c04lb1dupq7dh7qhkmaq
ot3nh4fnnfverq@4ax.com...
>
>
>
>

Breck Carter [TeamSybase]

2005-06-23, 11:23 am

On 23 Jun 2005 08:13:02 -0700, John Smirnios
< smirnios_at_sybase_d
ot_com> wrote:

>Remember that doubles only have about 15 or 16 decimal digits of
>accuracy. 1045.00499999999999999 has way too many digits to be stored
>accurately in a double. Depending on the conversion & rounding routines
>used by your software, you may get slightly different double values to
>represent that value. The differences in rounding that you see are
>completely reasonable behaviour given the nature of doubles.
>
>-john

Yes, but... are doubles involved at all?

SELECT EXPRTYPE ( 'SELECT 1045.00499999999999999', 1 );

returns 'numeric(21,17)'

I think it is a PB issue; SQL Anywhere and dbisql seem to have no
problems as per a previous post.

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

anil k goel

2005-06-23, 11:23 am

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:02mlb112743q0eb
vu8n75ucnr3kan46tra@
4ax.com...
> On 23 Jun 2005 08:13:02 -0700, John Smirnios
> < smirnios_at_sybase_d
ot_com> wrote:
>
>
> Yes, but... are doubles involved at all?
>
> SELECT EXPRTYPE ( 'SELECT 1045.00499999999999999', 1 );
>
> returns 'numeric(21,17)'

Unless, I've missed something altogether in this thread, even ASA can have
these kind of issues with doubles, as John points out. As an example,
consider:

SELECT round(cast(1045.004999999999999 as double),2),
round(cast(1045.00499999999999 as double),2)

The one extra 9 in the first select list item will make it round up to
1045.01 while the second item rounds to 1045. So, while the round function
is working correctly, the fact of the matter is that the original number
has lost precision when converted to a double.

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

Breck Carter [TeamSybase]

2005-06-23, 8:23 pm

On 23 Jun 2005 09:17:13 -0700, "anil k goel"
<firstname.no-junk.lastname@spam.ianywhere.com> wrote:

>So, I did miss something -- please ignore previous post...

Is "John Smirnios" your mild-mannered alter ego personality, or vice
versa? :)

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

anil k goel

2005-06-24, 9:23 am

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:7h4mb15v70jp9p4
sdploou5auebsrd6ivn@
4ax.com...
> On 23 Jun 2005 09:17:13 -0700, "anil k goel"
> <firstname.no-junk.lastname@spam.ianywhere.com> wrote:
>
>
> Is "John Smirnios" your mild-mannered alter ego personality, or vice
> versa? :)

You talking to *me* ?? ;-)

I realized what I missed a nanosecond after hitting the "Send" button.
Trying to cancel the post wasn't fruitful...

-anil

Graeme Perrow

2005-06-24, 11:24 am

Breck Carter [TeamSybase] wrote:
> On 23 Jun 2005 09:17:13 -0700, "anil k goel"
> <firstname.no-junk.lastname@spam.ianywhere.com> wrote:
>
>
>
>
> Is "John Smirnios" your mild-mannered alter ego personality, or vice
> versa? :)
>

Breck's onto you, Anil -- I mean John. Just wait until he finds out that
you're also "Glenn Paulley"...

--
Graeme Perrow
Senior Software Developer
gperrow _at_ ianywhere _dot_ com
iAnywhere Solutions Inc.
A Sybase company

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer/

Breck Carter [TeamSybase]

2005-06-26, 9:23 am

On 24 Jun 2005 09:35:16 -0700, Graeme Perrow
< gperrowNO@SPAMianywh
ere.PLEASEcom> wrote:

>Breck's onto you, Anil -- I mean John. Just wait until he finds out that
>you're also "Glenn Paulley"...

I haven't met "Ani Nica" yet... hmmmm... never in the same room at the
same time... I wonder... :)

Breck O'Drifting Thread

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

John Smirnios

2005-06-28, 9:23 am

I think you're right -- there may be no doubles involved. Just ignore
me/Anil/Ani/whomever else you may think I am :)

-john.

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Breck Carter [TeamSybase] wrote:
> On 23 Jun 2005 08:13:02 -0700, John Smirnios
> < smirnios_at_sybase_d
ot_com> wrote:
>
>
>
>
> Yes, but... are doubles involved at all?
>
> SELECT EXPRTYPE ( 'SELECT 1045.00499999999999999', 1 );
>
> returns 'numeric(21,17)'
>
> I think it is a PB issue; SQL Anywhere and dbisql seem to have no
> problems as per a previous post.
>
> Breck
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Sponsored Links