0
I have the following problem: I have a decimal field(11,2) When the calculated value gives (example) 60,345 My SQL 4 Insert saves 60.34 But Mariadb Insert 10.4 (and Mysql 8) record 60.35 This is causing me to divert pennies in reports. Has anyone ever been there? Would they know how to reconcile?
0
Guys! I used truncate() in the calculation and the 3 banks presented the same result in the reports. Thank you all! Moderation, is my first topic, so I don’t know how to give as solved! D
Explain better, detail with the SQL command. When were you asked the following: "(ROUND(value, 2) or TRUNCATE(value, 2))?" You said, "I did that, and then Mysql4 started recording one penny less and my disagreement went from one to two cents between the bases". But now it worked in Mysql version 4 and Mariadb 10.4 (and Mysql 8), all went well? Only with TRUNCATE. Details. Hug!
@Fabianomonteiro that’s right... The ROUND command(field,2) increased the difference to 2 cents (decreasing one cent in My 4) and keeping the results of Mariadb and My 8. With the TRUNCATE command (field,2) the 3 banks presented the same values in the report.
Browser other questions tagged mysql mariadb
You are not signed in. Login or sign up in order to post.
You have already tried to force the calculated value round to the desired number of decimal places (ROUND(value, 2) or TRUNCATE(value, 2))?
– anonimo
Already... I did that hj, then Mysql4 started recording 1 cent less and my disagreement went 1 to 2 cents between the bases... :/
– André Cezimbra
Mysql 4 is not very old? The current version is 8.
– anonimo
And the use of CEILING rounds up a number:
SELECT CEILING(60.345) Result;and the FLOOR that rounds down to the nearest integer:SELECT FLOOR(60.345) Result;But you need the pennies....– Fabiano Monteiro
anonymimo I am working on a system migration... but clients still use Mysql 4, will be migrated gradually, so I need to maintain the compatibility of banks...
– André Cezimbra
Fabiano Monteiro, yes... I need the pennies, so Ceiling and the floor don’t help me.
– André Cezimbra
How are you performing the calculation? It is a SUM() based on the column value by the returned/filtered rows by a WHERE?
– Guilherme Nascimento
@Andrécezimbra You commented that when using ROUND() you started recording 1 less in Mysql 4, and the divergence went from 1 to 2. Since n I have version 4 to test, then try this here in INSERT:
... values(ROUND(60.345, 2)+00.02);– Fabiano Monteiro
I don’t know how Mysql does the rounding in cases where there is a 5 after the desired number of decimals. Some software instead of always rounding up round to the nearest pair in order to distribute evenly. Example with zero decimal places: ROUND(3.5) = 4, ROUND(2.5) = 2. It may be that this is happening, I found nothing about it in the Mariadb documentation. A question: You are applying rounding to the results of your calculations?
– anonimo
@Guilhermenascimento is the accounting value calculation, so it’s a pretty crazy calculation, totals per item - various discounts, and some of those discounts are other calculations. So it’s pretty complicated.
– André Cezimbra
anonimo are not applied rounding in the calculations, but as the calculation is resulting in 3 decimal places, and the field is decimal(11,2) hence the bank itself is "solving" to record, hence My 4 seems to be truncating, and the other 2 rounding up.
– André Cezimbra
@Did Andrécezimbra do what I said? About
ROUND(60.345, 2)+00.02– Fabiano Monteiro
@Fabianomonteiro then... then will play 2 cents more on Mariadb and Mysql 8. :/
– André Cezimbra
@Andrécezimbra in the case of Mysql 4 will not play 2 cents. Vc tested? You need to use the rule only when inserting in Mysql 4, only in it
ROUND(60.345, 2)+00.02result 60.35, since the ROUND plays -2 on the pennies in version of Mysql 4, in your question Mysql is 4, right?– Fabiano Monteiro
@Fabianomonteiro I need to keep a single calculation, because there may be cases where the client has Mysql 8 on the server, but keep Mysql 4 in a terminal, in this case would generate divergence in the report, since the source code remains the same.
– André Cezimbra
@Andrécezimbra, I get it. Anyway, everything will be a matter of rule, you will have to recognize the older Mysql, the version 4, and then insert its rules. You can put in SELECT, whatever it is, it will take a rule detecting the version of Mysql to serve what it needs.
– Fabiano Monteiro