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