What happens if you CREATE VIEW with those tests? Does QGIS read the VIEW differently?
Thanks, Alex On 12/10/2014 10:57 AM, Alexandre Neto wrote: > Hello all, > > To make some tests I have the folllowing table: > > General info > > > Relation type: > > Table > > Rows: > > 3 > > SpatiaLite > > > Column: > > geometry > > Geometry: > > MULTIPOLYGON > > Dimension: > > XY > > Spatial ref: > > WGS 84 (4326) > > Extent: > > (unknown) (find out <action:extent/get>) > > Fields > > > # > > Name > > Type > > Null > > Default > > 0 > > pkuid > > integer > > Y > > > > 1 > > coluna1 > > text > > Y > > > > 2 > > coluna2 > > real > > Y > > > > 3 > > coluna3 > > integer > > Y > > > > 4 > > geometry > > MULTIPOLYGON > > Y > > > > > If I do this: > > SELECT > *, > coluna2/1.0 AS test_field > FROM > testes > > when I open it with DBmanager the test_field return a QSTRING with type > name TEXT > > http://imgur.com/GHeMyiW > > Using cast: > > SELECT > *, > cast((coluna2/1.0) as real) AS test_field > FROM > testes > > I get the same. > > Thanks for the help. > > Alexandre Neto > > > On Wed, Dec 10, 2014 at 6:16 PM, Alex Mandel <tech_...@wildintellect.com> > wrote: > >> On 12/10/2014 09:55 AM, Goyo wrote: >>> 2014-12-10 1:50 GMT+01:00 Alexandre Neto <senhor.n...@gmail.com>: >>>> Hello, >>>> >>>> When querying a spatialite database in the database manager, if you >> create >>>> some calculations (say add two columns values) the resulting column >> returns >>>> as TEXT instead of the expected numeric field. >>>> >>>> Is this intended or a bug? >>>> >>>> Trying to cast it as real, double or float did not work either. >>> >>> Calculated columns in SQLite do not have a data type, the actual >>> values in the column do. If you execute this query: >>> >>> SELECT "col1" + "col2" AS "thevalue", typeof("col1" + "col2") as >>> "thetype" FROM "table" >>> >>> You should have "thetype"='real' in every row (provided "col1" and >>> "col2" always contain real values) but the column "thevalue" does not >>> have a data type assigned. >>> >>> I guess QGIS needs to assign a data type to each column and chooses >>> the more general type when the column does not have one without >>> looking at the actual values (which could be expensive and also >>> misleading if the data change). >>> >>> Goyo >>> _______________________________________________ >>> Qgis-user mailing list >>> Qgis-user@lists.osgeo.org >>> http://lists.osgeo.org/mailman/listinfo/qgis-user >>> >> >> There are two ways in SQLITE to get the numeric type you want, you can >> do a CAST() or you can force it with a multiplication (*1 or *1.0). It's >> really critical if you do a division to *1.0 somewhere in the statement >> otherwise you'll only get an integer. >> >> Note this is not a QGIS specific thing, it applies to general use of >> SQLITE. >> >> You said you tried a CAST, can you paste what you tried? >> >> Thanks, >> Alex >> _______________________________________________ >> Qgis-user mailing list >> Qgis-user@lists.osgeo.org >> http://lists.osgeo.org/mailman/listinfo/qgis-user >> > > > > _______________________________________________ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/qgis-user > _______________________________________________ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user