Servoy 5: Decimal calculation issue

Classic Servoy
1

Hi everybody,

We have in our solution the following calculation:

function cal_total_line_cost()
{
return (fld_quantity*fld_unit_cost)-(fld_discount*fld_quantity*fld_unit_cost);
}

The result is stored in the database.
Here are the datatypes:
cal_total_line_cost → numeric(19.2)
fld_quantity → integer
fld_unit_cost → numeric(19.2)
fld_discount → numeric(19.3)

In Servoy 4 the result given was correct. But now on Servoy 5 it seems to round the result from the first two digits of the number.
Here are few examples:
fld_quantity = 1
fld_unit_cost = 192.48
fld_discount = 0.000
cal_total_line_cost = 190.00 (which is incorrect, should be 192.48)

fld_quantity = 2
fld_unit_cost = 2.51
fld_discount = 0.000
cal_total_line_cost = 5.00 (which is incorrect, should be 5.02)

fld_quantity = 5
fld_unit_cost = 1.64
fld_discount = 0.350
cal_total_line_cost = 5.30 (which is incorrect, should be 5.33)

2

After several test we found the source of the problem.

In fact, for some reason, if you save the value returned by a calculation into a field of datatype numeric(x,y), y will be the number of digits before and after the ‘,’
For example, 23.56 in numeric(5,2) will save 24.00 (rounded up)
23.56 in numeric(5,3) will save 23.60 (rounded up)
23.56 in numeric(5,4) will save 23.56

We use PostgreSQL 8.3 and 8.4

3

do you have a quick example for this?

Can you test also if this is a pure db problem? So can you test on sybase?

4

Hi,

We cannot test it on other database. But it can be reproduced very easily.
Here is what you need to do:

  • Create database with a table having a column “calculation” of type “numeric(19,2)”
  • Create a solution in Servoy 5 linked to this database
  • Create a calculation on the table having the name “calculation” (same name as column name to make it stored) and which return a NUMBER
  • In this calculation just put the following code “return 345.67”
  • In your solution make sure the calculation is run at least once
  • Run your solution in Developer
  • Check the result stored in the database. Should be 350.00
  • Change the database datatype to “numeric(19,5)”
  • Restart Developer
  • Run again the solution
  • Check the result stored in the database. Should be 345.67

Of course you can edit an existing solution to test it as well.
Also we have checked PostgreSQL doc to make sure we don’t miss-understand the datatype syntax. But it behaves differently than the doc: http://www.postgresql.org/docs/8.3/inte … IC-DECIMAL

NUMERIC(precision, scale)
The number 23.5141 has a precision of 6 and a scale of 4.

5

please create a case with this info.

6

Case 260294 created