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

  • 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 Oct 11 '14 at 22:01
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.

  • Ok, but why I'm able to use WITH clause from within SQLite Manager for the same database? – SS_Rebelious Oct 12 '14 at 8:39
  • @SS_Rebelious I am unable to check the bindings at the moment. Can you append that to your answer? – Scro Oct 12 '14 at 8:46
  • What exactly do you want me to do? I don't think that I understood you. – SS_Rebelious Oct 12 '14 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 Oct 12 '14 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... – SS_Rebelious Oct 12 '14 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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