On 4 Dec 2016, at 6:55am, Frank Millman <fr...@chagford.com> wrote: > If a column contains a ‘1’, I would expect sqlite3 to return true when > testing for ‘1’, but in my example it returns false.
I think I’ve found the problem. Here is your syntax: SELECT * FROM test_1 WHERE CASE WHEN tran_type = 'inv' THEN posted END = '1' SQLite does not have a BOOL type. Instead it uses the integers 0 and 1 to indicate boolean values. So it interprets your "posted" column as wanting to store integers. So this command returns … SELECT posted,typeof(posted),posted='1' FROM test_1; <— [1, integer, 1] So the result of your CASE expression can be 1, but not '1'. And in SQLite … SELECT 1='1'; <— [0] You might like to use the following syntax instead: SELECT * FROM test_1 WHERE tran_type = 'inv' AND posted; This should work in both SQLite and PostgreSQL, and be extremely fast if you have an index on (tran_type,posted). If you absolutely must let the SELECT stay as it is, declare the "posted" column as TEXT, not BOOL. Then your original SELECT statement should work as intended. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
