<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
You forgot the last part of the chapter (emphasis is mine..) <br>
<a class="toc-backref"
href="https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26"><span
class="section-number"></span></a><br>
<p>The whole chapter From the manual..</p>
<p>================================<br>
</p>
<p><a class="toc-backref"
href="https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26"><span
class="section-number"></span></a></p>
<h3><a class="toc-backref"
href="https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26"><span
class="section-number">14.2.5.2. </span>Supported query
language</a><a class="headerlink"
href="https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#supported-query-language"
title="Permalink to this headline"></a></h3>
<p>The underlying engine uses SQLite and SpatiaLite to operate.</p>
<p>It means you can use all of the SQL your local installation of
SQLite
understands.</p>
<p>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:</p>
<div class="highlight-sql notranslate">
<div class="highlight">
<pre id="codecell2"><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">id</span><span class="p">,</span><span class="w"> </span><span class="n">MakePoint</span><span class="p">(</span><span class="n">x</span><span class="p">,</span><span class="w"> </span><span class="n">y</span><span class="p">,</span><span class="w"> </span><span class="mi">4326</span><span class="p">)</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="n">geometry</span>
<span class="k">FROM</span><span class="w"> </span><span class="n">coordinates</span>
</pre>
</div>
</div>
<p><i><b><a class="reference internal"
href="https://docs.qgis.org/3.22/en/docs/user_manual/expressions/functions_list.html#functions-list"><span
class="std std-ref">Functions of QGIS expressions</span></a></b></i><i><b>
can also be used in a
virtual layer query. <-- You forgot this part<br>
</b></i></p>
<i>
</i>
<p>To refer the geometry column of a layer, use the name <code
class="docutils literal notranslate"><span class="pre">geometry</span></code>.</p>
<p>Contrary to a pure SQL query, all the fields of a virtual layer
query must
be named. Don’t forget to use the <code class="docutils literal
notranslate"><span class="pre">as</span></code> keyword to name
your columns if they
are the result of a computation or a function call.</p>
<p>================================<br>
</p>
<p><a class="toc-backref"
href="https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26"><span
class="section-number"></span></a></p>
<h3><a class="toc-backref"
href="https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26"><span
class="section-number"></span></a></h3>
<p></p>
<p></p>
<pre class="moz-signature" cols="72">
Med venlig hilsen / Best regards
Bo Victor Thomsen</pre>
<div class="moz-cite-prefix">Den 28-02-2023 kl. 17:37 skrev Antonio
Valanzano via QGIS-User:<br>
</div>
<blockquote type="cite"
cite="mid:CANV1FQbGQ1q5aH2EC7zx2VYba_0fcfm2fXHA_rAKAYLoJEEinQ@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">
<div>Does someone know which dialect of SQL QGIS uses when
working with virtual layers ?</div>
<div><br>
</div>
<div>I have got a shapefile named "subway" and when i run the
following query using the DB Manager <br>
</div>
<div><br>
</div>
<div>SELECT s.gid, s.geometry<br>
FROM subway s<br>
WHERE (strpos(s.routes, 'Q') <> 0);</div>
<div><br>
</div>
<div>it produces a result that I can add to the map canvas as a
virtual layer.</div>
<div><br>
</div>
<div>Such result is in contrast with the QGIS documentation for
version 3.22<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>----------------------------------------------------------------------------------------------------------------------------<br>
</div>
<div>
<h2><a class="gmail-toc-backref"
href="https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id24"
moz-do-not-send="true"><span class="gmail-section-number">14.2.5.
</span>Creating virtual layers</a></h2>
</div>
<div>
The SQL query will be executed, regardless of the underlying
provider of the
<code class="gmail-docutils gmail-literal gmail-notranslate"><span
class="gmail-pre">airports</span></code> layer, even if
this provider does not directly support SQL
queries. <br>
</div>
<div><br>
</div>
<div>
<h3><a class="gmail-toc-backref"
href="https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26"
moz-do-not-send="true"><span class="gmail-section-number">14.2.5.2.
</span>Supported query language</a><a
class="gmail-headerlink"
href="https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#supported-query-language"
title="Permalink to this headline" moz-do-not-send="true"></a></h3>
<p>The underlying engine uses SQLite and SpatiaLite to
operate.</p>
<p>It means you can use all of the SQL your local installation
of SQLite
understands.</p>
</div>
<div>--------------------------------------------------------------------------------------------------------------------------</div>
<div><br>
</div>
<div><br>
</div>
<div>
However the query I have used contains a function "strpos"
which is not part of the SQL language as understood by SQLite.</div>
<div><br>
</div>
<div>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".</div>
<div><br>
</div>
<div>
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.
</div>
<div><br>
</div>
<div><br>
</div>
<div>So the question is "which dialect does QGIS use for running
the query"?</div>
<div><br>
</div>
<div>Does it depend on the type of layer on which the query is
applied ?</div>
<div><br>
</div>
<div>Is there a default SQL, which is used in case of a provider
that does not directly support SQL
queries (such as shapefiles)?<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>Any info would help.</div>
<div><br>
</div>
<div>Antonio Valanzano</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
QGIS-User mailing list
<a class="moz-txt-link-abbreviated" href="mailto:QGIS-User@lists.osgeo.org">QGIS-User@lists.osgeo.org</a>
List info: <a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/qgis-user">https://lists.osgeo.org/mailman/listinfo/qgis-user</a>
Unsubscribe: <a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/qgis-user">https://lists.osgeo.org/mailman/listinfo/qgis-user</a>
</pre>
</blockquote>
</body>
</html>