Interesting. I run the same SQL (except I used table t1 instead of table1 'cos I'm lazy) and got this (SQLite 3.7.15):
sqlite> delete from t1; sqlite> select * from t1; sqlite> insert into t1 values(1); sqlite> insert into t1 values(10); sqlite> insert into t1 values(11); sqlite> insert into t1 values(21); sqlite> insert into t1 values(31); sqlite> insert into t1 values(41); sqlite> insert into t1 values(51); sqlite> insert into t1 values(61); sqlite> insert into t1 values(71); sqlite> insert into t1 values(81); sqlite> insert into t1 values(91); sqlite> insert into t1 values(99); sqlite> insert into t1 values(100); sqlite> insert into t1 values(101); sqlite> select * from t1; age_band ---------- 1 10 11 21 31 41 51 61 71 81 91 99 100 101 sqlite> .read sql.txt sqlite> select * from t1; age_band ---------- 0 - 10 0 - 10 11 - 20 21 - 30 31 - 40 41 - 50 51 - 60 61 - 70 71 - 80 81 - 90 91 - 100 91 - 100 91 - 100 over 100 I guess some intermediate results are being picked up on the way. It will be interesting to hear the explanation. Staffan On Thu, Nov 22, 2012 at 11:30 AM, Bart Smissaert <bart.smissa...@gmail.com>wrote: > Have a table with one field holding numbers from 0 to about 110. > The field has no data type, so it is not integer or text. > Now I run the following SQL: > > UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10' > when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN > 21 AND 30 then ' 21 - 30' when age_band BETWEEN 31 AND 40 then ' 31 > - 40' when age_band BETWEEN 41 AND 50 then ' 41 - 50' when age_band > BETWEEN 51 AND 60 then ' 51 - 60' when age_band BETWEEN 61 AND 70 > then ' 61 - 70' when age_band BETWEEN 71 AND 80 then ' 71 - 80' when > age_band BETWEEN 81 AND 90 then ' 81 - 90' when age_band BETWEEN 91 > AND 100 then ' 91 - 100' when age_band > 100 then ' over 100' else > age_band end) > > Now there are 2 things wrong with the result. > Firstly there are no ' 91 - 100' results and there should be as there > are numbers in > this range. > Secondly, the number 100 remains as it is, so it shows as 100 in the > output. > I thought that as the field has no date affinity, it should be handled > as integer > numbers and still should hold the text conversions fine. > > Thanks for any advice as to what is going on here. > > RBS > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
