I want to select, say, rows 6 through 12 of a sqlite database. So I tried a subquery, but it does not work:
SQLExecute[conn,"SELECT * FROM (SELECT row_number() OVER (ORDER BY id) AS RowNum, * FROM chains) WHERE (6 <= RowNum AND RowNum <= 12);"
and generates
JDBC::error: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)
but the same SQL code works fine from within sqlite3 itself.
Every variation of subquery I've tried gives the same error. The error message makes it sound like SQLite is complaining, rather than JDBC, or Mathematica, but I don't see how that can be since it works fine if I run sqlite3 from the commandline. Non-subquery selects work, so there's nothing wrong with the connection.
I guess I do not understand how to use SQLExecute.
How do I extract a fixed range of rows from a SQLite database?
I'm using 12.0.0 for Mac OS X x86 (64-bit) (April 7, 2019) and sqlite3 version 3.27.2.
Explicitly, this fails:
In[425]:= conn = OpenSQLConnection[JDBC["SQLite", "chains2d005.sqlite"]];
In[426]:= SQLExecute[conn, "SELECT * FROM (SELECT row_number() OVER (ORDER BY id) AS RowNum, * FROM chains) WHERE (6 <= RowNum AND RowNum <= 12);"]
During evaluation of In[426]:= JDBC::error: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)
Out[426]= $Failed
But this works:
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> SELECT * FROM (SELECT row_number() OVER (ORDER BY id) AS RowNum, * FROM chains) WHERE (6 <= RowNum AND RowNum <= 12);
I copy/paste the SQL so it is the same, although I've stared at it so long now, maybe I'm missing something.
SQLExecute[conn, "select sqlite_version();"]– Rohit Namjoshi Dec 16 '19 at 17:31