[Qgis-user] Spatialite queries return text instead of numeric values

[Alex Mandel]

On 12/10/2014 09:55 AM, Goyo wrote:
> 2014-12-10 1:50 GMT+01:00 Alexandre Neto <senhor.neto at 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 at 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