Ask Your Question

Greater than zero is showing some 0 in query design. [closed]

asked 2019-09-22 16:22:40 +0100

LO_user1 gravatar image

Hi! I have found a strang behavior in LO Base query result. Attached is a sample DB to show the problem. I have transactions of customers payments for two months. Column 'Fee' shows the amount to pay. If a customer have to pay full amount, 'Fee' will multiply by 2. If some customer have to pay partial amount for one month like 60%, it will be multiply by (0.6 + 1). Now after getting the payment transactions and showing balances of customers, some have paid their full, so balance will be zero against them. When other have due amounts against them. Finally I want to show only the customers which have some dues. So the 'Due' column criterion is set > 0. But strangely it hide some customers who paid their full amounts but show some of them as 0 balance. I checked and found that it shows only them who have partial payments like 60%. Even 50% is OK like 100%. Is it a bug? And is their any correction needed in query design to eliminate the problem? C:\fakepath\Trial.odb

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by LO_user1
close date 2019-09-23 19:04:40.263142

1 Answer

Sort by » oldest newest most voted

answered 2019-09-22 18:21:37 +0100

m.a.riosv gravatar image

Maybe because partial payments or the sum of them was not properly rounded at two decimals , can suffer the problem of computer precision with decimal numbers having a periodic representation in binary. Test rounded 'Due' in the criterion, or rounded the value when it's written in the field.

edit flag offensive delete link more


OP can see this in result of Query2 if displayed calculated amount is formatted with 6 decimal places. It carries forward from there.

Ratslinger gravatar imageRatslinger ( 2019-09-22 19:40:28 +0100 )edit

With Query4 as SELECT "St_ID", "Total Paid", "To_Pay", "Due Amount" FROM "Query3" WHERE round("Due Amount",2) > 0 seems to work as you expect.

m.a.riosv gravatar imagem.a.riosv ( 2019-09-23 01:16:47 +0100 )edit

Thank you! it is working. I always use ROUND to get an integer value from decimal. Here the result was already round so did not tried. I think as the data type is float so problem appeared. On integer data type may be not comes like that. But need decimal value here so float data type used.

LO_user1 gravatar imageLO_user1 ( 2019-09-23 19:03:06 +0100 )edit


You have closed the question as the question is answered, right answer was accepted but no answer was marked as the correct (or accepted) answer.

As you have been helped, please help others to know the question has been answered by clicking on the ✔ in upper left area of answer which satisfied the question.

Ratslinger gravatar imageRatslinger ( 2019-09-23 20:57:23 +0100 )edit

Question Tools

1 follower


Asked: 2019-09-22 16:22:40 +0100

Seen: 27 times

Last updated: Sep 22