How can we help you today?

Start a new topic
Discussions DbVisualizer Forums Support

cannot use common table expressions (CTEs) in sqlite queries

[This topic is migrated from our old forums. The original author name has been removed]

I cannot use common table expressions (CTEs, or "with clauses") in sqlite queries. Here's an example query that I'm trying to run against my rxnorm database (this runs in the sqlite command-line): with r as (select * from rxnconso where sab='RXNORM' and tty in ('SCD')) select * from r; I think support for CTEs was added in sqlite 3.8.3 (about 5 months ago). Matt
4 CommentsSorted by Oldest First
[This reply is migrated from our old forums.]

Re: cannot use common table expressions (CTEs) in sqlite queries
Matt, The SQLite version packaged with DbVisualizer is 3.7.2 and can be easily replaced with a newer version. In the following web site you can download the latest snapshot and use it with DbVisualizer: https://bitbucket.org/xerial/sqlite-jdbc Once downloaded open the Driver Manager in DbVisualizer and make a copy of the SQLite driver entry. For the new entry point to the downloaded jar file. In the Connection details use the new driver entry and connect. You should see the new version in the connection message box. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: cannot use common table expressions (CTEs) in sqlite queries
Couple interesting points... When I dropped the latest xerial sqlite-jdbc driver jar file in ~/.dbvis/jdbc, it does pickup the jar and say that the sqlite lite jdbc driver was updated every time I startup DbVis. However, when I connect to my database, and open the connection properties window by double clicking on the connection, this is what it says: SQLite 3.6.23.1 SQLiteJDBC pure Second point, the latest version of the xerial sqlite-jdbc driver on that page is for 2.8.2, and the CTE support wasn't added until 2.8.3. I'm not sure what the driver is responsible for (does it just pass the query through to the database or does the jdbc driver handle the query?) Any other ideas?
[This reply is migrated from our old forums.]

Re: cannot use common table expressions (CTEs) in sqlite queries
Matt, Just want to let you know the 3.8.6 package is now available. Download and replace the sqlite.jar in your ~/.dbvis/jdbc. https://bitbucket.org/xerial/sqlite-jdbc This version will be included in the upcoming DbVisualizer 9.2 version. Regards Roger
[This reply is migrated from our old forums.]

Re: cannot use common table expressions (CTEs) in sqlite queries
Matt, I just want to inform you that SQLite is updated in the beta version for the upcoming 9.2 version. http://www.dbvis.com/download/beta/ Regards Roger
Comments to this discussion are now closed!
More topics in Support
See all 1297 topics