My results are diferent from the collumn

Question

I am executing a query like
SELECT L1_VLRTOT, L1_VALMERC, L1_VALMERC-L1_VLRTOT DESCONTO
FROM SL1010 WHERE D_E_L_E_T_ = ''

and the values are

L1_VLRTOT L1_VALMERC DISCOUNT
159. 7 172.4 12.7
126. 9 134.7 7.79999999999998
68. 39 89.59 21.2
352. 14 357.14 5
63. 9 65.9 2.00000000000001

I Don't know why the Values of DISCOUNT have different values as shown in the columns

Both L1_VLRTOT and L1_VALMERC are FLOAT VALUES

Does anyone know, why this is happening?

Answer 1

If you want precise numeric values, you should avoid float type. Instead, you can use decimal(p,s)/numeric(p,s), for example: decimal(6,2).
Here is the link to know more:
https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-ver16

Answer 2

Hi @André Coelho
I guess you are using the Float datatype for column L1_VLRTOT and L1_VALMERC.
Float is used to store approximate values, not exact values. It has a precision from 1 to 53 digits. Because the values cannot be stored precisely, people who use float end up with values that don’t match, columns of values that don’t quite add up, or totals that are a cent out, etc.
Please refer to this blog for more details: Newbie Mistake #1: Using float instead of decimal

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.