Databases and financial calculations

[Claude Moore]

Simple question: is it safe to use a DBMS (such SQL Server or MySql) Decimal(a,b) type to perform financial calculations (multiplications and sums) without incurring in any rounding problem ?
Suppose I have a table like this:

ORDER             ARTICLE       QTY          PRICE       VAT        LINE_AMOUNT

XYZ                  ABC              1              2.03         10           ------
XYZ                  DEF              2              7.22         10           ------
XYZ                  GHI              3              5.04         10           ------


Suppose that PRICE is the unit price, defined as decimal (6,2).  Is it safe to calculate the total amount of each row  as  UPDATE MYTABLE   SET LINE_AMOUNT =  QTY * PRICE * (1 + VAT / 100.00) and derive the total amount of   an order, let's say XYZ, as SELECT SUM(LINE_AMOUNT) WHERE ORDER = 'XYZ', or any
rounding loss may occour ?

[Tim Holloway]

It depends on your datatype in the database AND in the application.

If you define a numeric value in your database as NUMERIC(6,2), then it should always be accurate to 2 decimal places. If you do database calculations on the database server, the standard math rules about precision apply.

However, if you pull values and compute them in a Java application you may have a problem, since there is no "normal" datatype for decimal values except for floating-point, and so if you pull your JDBC/ORM values as float or double, you'll get the standard floating-point fuzz. To get around that, you'd have to either scale the numbers or use a decimal-friendly class like BigDecimal. Which, alas, doesn't have operators, so it's cumbersome to work with.

This is one of those cases where COBOL actually worked better (still does). It's ironic, because all of the major microprocessors I know of - plus IBM mainframes - have native decimal instructions in their hardware, but those features were not codified into the Java language.

[Claude Moore]

Tim Holloway wrote:It depends on your datatype in the database AND in the application.
If you define a numeric value in your database as NUMERIC(6,2), then it should always be accurate to 2 decimal places. If you do database calculations on the database server, the standard math rules about precision apply.
However, if you pull values and compute them in a Java application you may have a problem, since there is no "normal" datatype for decimal values except for floating-point, and so if you pull your JDBC/ORM values as float or double, you'll get the standard floating-point fuzz. To get around that, you'd have to either scale the numbers or use a decimal-friendly class like BigDecimal. Which, alas, doesn't have operators, so it's cumbersome to work with.

Well, the complete scenario is pretty... insane. I will receive the data via an XML file, from an application A. This application will compute the total amount of the order and will send this information to a third party application C. For some obscure reason, A isn't able to tell my application the total amount  of the order in
the XML file it will exchange with me, so that I need to re-calculate the total and send to the very same application C.
Now, I don't know anything about the A application, and my experience tells me that when you have two applications calculating the very same value, there's a chance than soon or later you'll get different results.
Using BigDecimal... yes, it may be a solution, but it involves rounding mode and I cant' say which rounding mode application A would apply. For this reason, I was wondering if I can use the database server - which DOES have decimal type, and I can assume it would work like in COBOL - all operation performed with a given precision.
Obviously, the right thing should be force Application A to expose directly its summation, so that there's no chance of changing rounding.

[Stephan van Hulst]

You're going about this the wrong way.

Instead of guessing how your application is supposed to work, you should ask for a specification. If the specification says: "the same way as application A", then you ask for the specification of application A.

[Tim Holloway]

Just FYI, COBOL specs say round up. But a lot of early database apps were not COBOL, and likewise the pre-database systems like IMS. So assuming "just like COBOL" could be an unsafe assumption.

On top of which, I'd wager a lot of DBMS's have a property you can set for round-up/round-down (truncate).

[Les Morgan]

Claude,

many databases have a type to use for financial transactions--MS SQL Server has money that does not round, but basically has 2 integer parts.  you have to actually design your data types according to your data range to reduce or eliminate the possibility of underflow, overflow, and rounding.  look at your data and see what functionality you need to implement and then check your data types to eliminate or minimize err.

Les

[Claude Moore]

I'm afraid there's no much hope for getting specs. The data are exported 'as are', from a legacy API.

[Les Morgan]

Claude,

i work with data systems that have billions of rows that we import from external sources on an "as is" basis.  that does not stop us from checking our own business needs and decided what needs to be done with each column of data.  Once we know the use we will be putting the data to, then we check to see if the datatypes will support the err which we need to stay within.  if they do not, then we massage the data.  once the data has been curated, it is read for our use.  once we have the data under our control, we are always free to use it within the bounds of our data sharing agreements.  in any case, there are always the ability to get specs.

i have had projects that both ends of the channel refused to make any decisions, when i am faced with that, i look over what i think the path should be.  i then write the specs and distribute them to the concerned parties and with an activation date of the specifications taking effect.  after the activation date, i use my specs and continue the project.  one thing i am always sure of when i produce the specification, business may be run and the err will be within acceptable standards and there is a clear audit trail of what is happening.

Les

Claude Moore wrote:I'm afraid there's no much hope for getting specs. The data are exported 'as are', from a legacy API.