[Qgis-user] SQL language in DB Manager

[Bo Victor Thomsen]

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 at lists.osgeo.org
> List info:https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe:https://lists.osgeo.org/mailman/listinfo/qgis-user
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20230301/9dbbda80/attachment.htm>