How to open a Spatialite non-spatial view in QGIS

Question

I'm using Qgis 1.8.0, and spatialite 2.3 in a Windows Vista machine.

I have a database in Spatialite, and I have created a view from one of its tables. When I open it in QGIS, using add Spatialite Layer (and enabling non-spatial tables), the attribute table shows ERROR in all of its lines and rows, but the number of rows and attributes seems correct.

I have tried both with spatial and non-spatial views withou sucess and opening tables (spatial or not) works well.

My table and view is something like this:

CREATE TABLE "Accoes"(
pkuid integer primary key autoincrement,
"UnidadeID" text,
"Tipo" text,
"Descricao" text,
"Data" text,
"Responsavel" text)

CREATE VIEW AccoesPoltest AS
SELECT c.* FROM Accoes as c

I have already read all info provided in this related question: Can Qgis Read Spatialite Views?

I also found a very old bug ticket from QGIS, with the exact same issue, but it was closed as invalid: http://trac.osgeo.org/qgis/ticket/2178

This is one of my first approaches with Qgis + Spatialite, so probably something is missing. Any idea? Thanks

Alexandre Neto

Answer 1

I got my answer by following the SpatiaLite Cookbook. I didn't have a ROWID in my view, and it seams to be must in order to open in QGIS.

Therefore, for my specific case, All I needed to do is assign "pkuid" key as ROWID, just like this:

CREATE TABLE "Accoes"(
pkuid integer primary key autoincrement,
"UnidadeID" text,
"Tipo" text,
"Descricao" text,
"Data" text,
"Responsavel" text)

CREATE VIEW AccoesPoltest as
SELECT c.pkuid as ROWID, c.* FROM Accoes as c