Same SQLite Query doesnot run in all the android version

Question

I am executing the following query in the SQLite :

SELECT * from ValueTable where SrNo = (with t as(SELECT SrNo,Value2 from ValueTable where Value2<'600' ) SELECT SrNo from t order by Value2 DESC limit 1)

The table schema is

SrNo   Value1   Value2

 1       200     450

 2       500     350

 3       100     500

So whenever I run the above query in my code, it works fine with the lollipop version. But gives error when run on the lower versions. I have no clue why. It gives the error near the 't' in the query above. What exactly am I missing ? Thanks in advance.

EDIT : Here's the error log :

     Caused by: android.database.sqlite.SQLiteException: near "t": syntax error (code 1): , while compiling: SELECT * from ValueTable where SrNo = (with t as(SELECT SrNo,Value2 from ValueTable where Value2<'600' ) SELECT SrNo from t order by Value2 DESC limit 1)
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:893)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:504)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
            at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
            at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1339)
            at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1278)
            at kunal.trial.trialsqliteinallversions.TestAdapter.getTestData(TestAdapter.java:56)
            at kunal.trial.trialsqliteinallversions.MainActivity.LoadEmployee(MainActivity.java:54)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at android.view.View$1.onClick(View.java:3843)
            at android.view.View.performClick(View.java:4463)
            at android.view.View$PerformClick.run(View.java:18789)
            at android.os.Handler.handleCallback(Handler.java:808)
            at android.os.Handler.dispatchMessage(Handler.java:103)
            at android.os.Looper.loop(Looper.java:193)
            at android.app.ActivityThread.main(ActivityThread.java:5299)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:825)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:641)
            at dalvik.system.NativeStart.main(Native Method)

not very familiar with the `with` clause, but if you're using it as in here - https://www.sqlite.org/lang_with.html , it seems youc an only use it with the recursive / ordinary statements — ılǝ, May 13 '15 at 06:40

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

WITH clause support was added in SQLite 3.8.3.

API levels below 20 are using an older version of SQLite.

Refactor your query that you get the results you want with the SQL supported on all targeted API levels.

edited May 23 '17 at 12:06

Community

1
1

answered May 13 '15 at 07:13

laalto

150,114
66
286
303

Thank you.. Just to get the direction right - was there any alternative available in the earlier version for 'with clause' or do I have to think the whole different way to implement the above query ? – Kunal May 13 '15 at 07:19
Before that one would do recursive queries with the help of a programming language. I didn't read your SQL so much that I could say if you could achieve the same without recursion. – laalto May 13 '15 at 07:23

Same SQLite Query doesnot run in all the android version

1 Answers1