3

I've encountered an issue: when I try to execute in Spatialite_GUI query that contains WITH clause I get SQL error: near "with": syntax error. Also WITH unlike SELECT and other keywords is not highlighted inside SQL-querry window. The same query in SQLite Manager works just fine. Found no treads on this issue. Is this my local problem?

Spatialite_GUI 1.7.1-1.5, SQLite Manager 0.8.1, OpenSUSE 13.1.

EDIT:

Actually the issue is deeper. DB Manager in QGIS 2.4 also throws the same error for the WITH clause (WHITH in sql window is highlighted in this case).

General info

SQLite version: 3.7.17

SpatiaLite

Library: 4.1.1 GEOS: 3.4.2-CAPI-1.8.2 r3921 Proj: Rel. 4.8.0, 6 March 2012

1
  • The example query from sqlite.org/lang_with.html WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000) SELECT x FROM cnt; works for me with spatialite-gui 1.8.0-dev.
    – user30184
    Commented Oct 11, 2014 at 22:01

1 Answer 1

3

You are running against SQLite version 3.7.17, but Common Table Expressions (WITH clause) were not supported until 3.8.3. See change log here: http://www.sqlite.org/changes.html

Unless you are trying to do a recursive query, you can either write your WITH clause as a view or use a subquery.

6
  • Ok, but why I'm able to use WITH clause from within SQLite Manager for the same database? Commented Oct 12, 2014 at 8:39
  • @SS_Rebelious I am unable to check the bindings at the moment. Can you append that to your answer?
    – Scro
    Commented Oct 12, 2014 at 8:46
  • What exactly do you want me to do? I don't think that I understood you. Commented Oct 12, 2014 at 8:49
  • The same general info that you posted for DM Manager regarding the SQLite version, etc. Wait, are you referring to the SQLite Manager plugin for Firefox? If so, it likely is compiled with a recent version of SQLite. To clarify, whether or not you can use a Common Table Expression depends on the client that you are using, not the version of the database file.
    – Scro
    Commented Oct 12, 2014 at 9:00
  • Yes it is the plugin for Firefox (version 0.8.1, added to question). Seems that my only option is to update SQLite to 3.8. The main need for WITH was a recursive query... Commented Oct 12, 2014 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.