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