“SELECT * FROM __table WHERE currencyfield > 0.0” doesn’t work for me, it gives all records, but “SELECT * FROM __table WHERE ABS(currencyfield) > 0” does seem to work.
Could anyone give me any advice?
Thank you.
Can you use an external SQLite app and check the contexts and show us? Like:
$ sqlite3 myDB
SQLite version 3.8.5 2014-05-29 12:36:14
Enter “.help” for usage hints.
sqlite> SELECT currencyfield FROM __table LIMIT 2 ;
10.0
20.0
sqlite>
Also check the created schema:
$ sqlite3 myDB
SQLite version 3.8.5 2014-05-29 12:36:14
Enter “.help” for usage hints.
sqlite> .schema __table
sqlite>
Thank you. Here are the results;
sqlite> SELECT debitamount FROM __bankstatement LIMIT 6;
340
0
43
0
13.68
620
sqlite>
.schema lists the 2 fields correctly
…
“debitamount” CURRENCY,
“creditamount” CURRENCY,
…
Regards
sqlite doesn’t have a currency type, so it’s anyone’s guess what type of value it’s actually storing. It would be instructive to see the results of
SELECT debitamount FROM __bankstatement order by debitamount
to see if it’s using a numeric or string collation.
Interestingly I tried to use CURRENCY as data type and noticed the same behavior as NUMERIC.
Numeric converts to INTEGER or REAL depending on the contents.
In my test, after inserting 1 and 1.1 into a “CURRENCY” FIELD requesting the typeof(FIELD), 1 returned integer, and 1.1 real.
And a select as SELECT FIELD from MyTable WHERE FIELD > 1.0; I just got 1.1 as expected.
0.0 amounts does not come either for > 0.0, differently of your test.
So I don’t know what’s going on. 
What happens if you do the following? :
SELECT debitamount, typeof(debitamount) AS T from __bankstatement WHERE T<>“integer” AND T<>“real” LIMIT 6;
– Just looking for some possible garbage
Because ABS(“GARBAGE”) = 0.0