I think you've already found a good solution to your problem by directly modifying the master table, so this is mostly an academic curiosity, but would it be a viable alternative to use a VIEW? In other words, rather than messing with the TABLE that holds the data, could you simply create a VIEW that exposes the information in the type you want (via a cast?), then your excel can access the data via the VIEW rather than the TABLE?
To reiterate, I'm merely asking about viability here. Marshall Sent from my iPhone On Oct 16, 2011, at 4:48 AM, "Frank Missel" <i...@missel.sg> wrote: > Hi Dan, > >> The two values in table "t" are stored in integer form. Were you to > magically >> change the database schema without rebuilding the underlying >> b-trees: >> >> CREATE TABLE t(a TEXT); >> >> and then execute the same SELECT, it would not work. SQLite would search >> the index for text value '1', not integer value 1 (since it assumes that > all >> values had the TEXT affinity applied to them when they were inserted). And >> the query would return no rows. > > You are right. However, in my case it is a bit special in that the values in > the fields actually already are stored correctly as REAL numbers but because > the declared data type is DECIMAL (which is used in some databases), Excel > will not treat the real numbers as such. Changing the declared data type > does the trick! > > As for text to integer as in your example, I guess it could also be done. > First the declared data type could be changed as I have described. After > that an update statement could set the <column> = CAST(<column> as INTEGER). > But you are right that indexes in this case would have to be rebuilt with > the REINDEX statement. However, that might still be better than having to > recreate the whole table and build indexes. > > > /Frank > > _______________________________________________ > 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
