#1731 Compairing 0's values not working as expacted!

[Abid Maqbool]

Let I have a table as below:

-- PUBLIC.PUBLIC.TBL_CUSTOMER_LEDGER definition

-- Drop table

-- DROP TABLE TBL_CUSTOMER_LEDGER;

CREATE TABLE TBL_CUSTOMER_LEDGER (
    ID INTEGER NOT NULL IDENTITY,
    CUSTOMER_ID INTEGER,
    "DATE" DATE,
    DESCRIPTION VARCHAR(1000),
    AMOUNT DOUBLE,
    IDS VARCHAR(1000),
    COMMENT VARCHAR(1000),
    CONSTRAINT SYS_PK_10128 PRIMARY KEY (ID)
);
CREATE INDEX TBL_CUSTOMER_LEDGER_CUSTOMER_ID_IDX ON TBL_CUSTOMER_LEDGER (CUSTOMER_ID);
CREATE INDEX TBL_CUSTOMER_LEDGER_IDS_IDX ON TBL_CUSTOMER_LEDGER (IDS);

And I am trying to execute following querey!

SELECT * FROM tbl_customer_ledger WHERE
    customer_id = 19
    AND NOT (
        (description LIKE 'Invoice # %' OR description LIKE 'Invoice Return # %' OR description LIKE 'Sale Item Return # %')
        AND amount = 0
  )
ORDER BY id;

but as you are seeing its not workin,

ID |CUSTOMER_ID|DATE      |DESCRIPTION                 |AMOUNT |IDS   |COMMENT              |
---+-----------+----------+----------------------------+-------+------+---------------------+
 83|         19|2025-04-12|Opening Balance (Receivable)| 2000.0|83    |                     |
 84|         19|2025-04-12|Cash Out (Cash)             | 1500.0|CLC84 |                     |
 85|         19|2025-04-12|Adjustment (Cash)           |-3000.0|CLC85 |                     |
 86|         19|2025-04-12|Adjustment (Bank)           | -500.0|CLB86 |                     |
 87|         19|2025-04-12|Invoice # 92 (Cash)         |   40.0|IC92  |                     |
 88|         19|2025-04-12|Invoice # 93 (Cash)         |   60.0|IC93  |                     |
 89|         19|2025-04-12|Invoice # 94 (Cash)         | -100.0|IC94  |                     |
 90|         19|2025-04-12|Invoice Return # 94 (Cash)  |  100.0|IRC94 |                     |
 91|         19|2025-04-12|Invoice # 95 (Cash)         | -100.0|IC95  |                     |
 92|         19|2025-04-12|Invoice Return # 95 (Cash)  |   60.0|IRC95 |                     |
 93|         19|2025-04-12|Sale Item Return # 8 (Cash) |   -0.0|SIRC8 |                     |
 94|         19|2025-04-12|Sale Item Return # 9 (Cash) |   60.0|SIRC9 |                     |
 95|         19|2025-04-12|Adjustment (Cash)           | -100.0|CLC95 |                     |
 96|         19|2025-04-12|Sale Item Return # 10 (Cash)|   -0.0|SIRC10|                     |
 97|         19|2025-04-12|Invoice # 96 (Cash)         |    4.0|IC96  |                     |
 98|         19|2025-04-12|Adjustment (Cash)           |  -16.0|CLC98 |                     |
 99|         19|2025-04-12|Invoice # 97 (Cash)         |   -2.0|IC97  |                     |
100|         19|2025-04-13|Adjustment (Cash)           |   -0.0|CLC100|                     |
101|         19|2025-04-13|Adjustment (Cash)           |-1200.0|CLC101|مکمل ادائیگی         |
102|         19|2025-04-13|Discount                    | -150.0|CL102 |فیس میں رعایت        |
103|         19|2025-04-13|Cash Out (Bank)             |  500.0|CLB103|زائد ادائیگی کی واپسی|
104|         19|2025-04-13|Adjustment (Bank)           |-1000.0|CLB104|بینک سے ایڈجسٹمنٹ    |
105|         19|2025-04-13|Cash Out (Cash)             |  700.0|CLC105|نقد واپسی            |

e.g. values -0.0 are also showing, i think, overall 0, 0.0, -0.0 means same?

only this is working,

ABS(amount) < 0.000001

but this is not clean solution>

[Abid Maqbool]

some others solutions are also working e.g.
AND CAST(amount AS DECIMAL) = 0
or
ABS(amount) < 0.000001
or
CAST(amount AS INT) = 0
etc. but these are not looking like clean one,

why it's not working?

amount = 0

[Fred Toussi]

The type DOUBLE is for floating point values which are stored in an approximate fashion. Use a DECIMAL type such as DECIMAL(16,2) to store exact monetary values. The DECIMAL(16,2) can be used for currencies such as the Dollar or Euro for values over many trillion units.