On 8/02/2009 8:33 AM, Simon wrote:
> Difficult to say for sure, but it's possible the Indx of 0 were
> inserted with another type (ie, the string "0" and of course, 0 !=
> "0")
If the column is declared as integer (as the OP said) you need to try
harder than '0' ... not trimmimg leading/trailing spaces will do the
trick. Also remember the way that NULL behaves.
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> create table foo (indx integer);
sqlite> insert into foo values(0);
sqlite> insert into foo values('0');
sqlite> insert into foo values('0.0');
sqlite> insert into foo values(null);
sqlite> insert into foo values(0.0);
sqlite> insert into foo values(' 0');
sqlite> insert into foo values('0 ');
sqlite> select typeof(indx), count(*) from foo group by typeof(indx);
integer|4
null|1
text|2
sqlite> select rowid, '<' || ifnull(indx, 'NULL') || '>' from foo where
indx != 0 or indx is null;
4|<NULL>
6|< 0>
7|<0 >
sqlite> select rowid, '<' || ifnull(indx, 'NULL') || '>' from foo where
indx = 0;
1|<0>
2|<0>
3|<0>
5|<0>
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
