You forgot the last part of the chapter (emphasis is mine..)
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26>
The whole chapter From the manual..
================================
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26>
14.2.5.2. Supported query language
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26>
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#supported-query-language>
The underlying engine uses SQLite and SpatiaLite to operate.
It means you can use all of the SQL your local installation of SQLite
understands.
Functions from SQLite and spatial functions from SpatiaLite can also be
used in a virtual layer query. For instance, creating a point layer out
of an attribute-only layer can be done with a query similar to:
SELECTid,MakePoint(x,y,4326)asgeometry
FROMcoordinates
/*Functions of QGIS expressions
<https://docs.qgis.org/3.22/en/docs/user_manual/expressions/functions_list.html#functions-list>*//*can
also be used in a virtual layer query. <-- You forgot this part
*/
//
To refer the geometry column of a layer, use the name |geometry|.
Contrary to a pure SQL query, all the fields of a virtual layer query
must be named. Don’t forget to use the |as| keyword to name your columns
if they are the result of a computation or a function call.
================================
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26>
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26>
Med venlig hilsen / Best regards
Bo Victor Thomsen
Den 28-02-2023 kl. 17:37 skrev Antonio Valanzano via QGIS-User:
Does someone know which dialect of SQL QGIS uses when working with
virtual layers ?
I have got a shapefile named "subway" and when i run the following
query using the DB Manager
SELECT s.gid, s.geometry
FROM subway s
WHERE (strpos(s.routes, 'Q') <> 0);
it produces a result that I can add to the map canvas as a virtual layer.
Such result is in contrast with the QGIS documentation for version 3.22
----------------------------------------------------------------------------------------------------------------------------
14.2.5. Creating virtual layers
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id24>
The SQL query will be executed, regardless of the underlying provider
of the |airports| layer, even if this provider does not directly
support SQL queries.
14.2.5.2. Supported query language
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26>
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#supported-query-language>
The underlying engine uses SQLite and SpatiaLite to operate.
It means you can use all of the SQL your local installation of SQLite
understands.
--------------------------------------------------------------------------------------------------------------------------
However the query I have used contains a function "strpos" which is
not part of the SQL language as understood by SQLite.
If I run the same query on a layer which is part of a Spatialite
database the DB Manager produces the error "no such function: strpos".
If I run the same query on a layer which is part of a PostGIS database
the DB Manager produces the same result of the virtual layer created
from the shapefile.
So the question is "which dialect does QGIS use for running the query"?
Does it depend on the type of layer on which the query is applied ?
Is there a default SQL, which is used in case of a provider that does
not directly support SQL queries (such as shapefiles)?
Any info would help.
Antonio Valanzano
_______________________________________________
QGIS-User mailing list
QGIS-User@lists.osgeo.org
List info:https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe:https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
QGIS-User mailing list
QGIS-User@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
