How to select from static list of values in android sqlite, syntax error near values(

Question

I am trying to create a static list of timestamps so that i can join them agains another table to create chart data. So far I have a query in this format SELECT * FROM (VALUES('a'),('b'),('c'),('d')) AS tbl ,which is working in sqlitestudio but not in android 4.4. When I run the query in the phone I get the error

android.database.sqlite.SQLiteException: near "VALUES": syntax error (code 1): , while compiling: SELECT * ...

I have also tried wrapping the values term inside another select like this SELECT * FROM (SELECT * FROM (VALUES('a'),('b'),('c'),('d'))) AS tbl but I still get the same error.

The full query now looks like this

SELECT *  FROM (select * from ( VALUES  (1458111312025), 
                                    (1455667200000), 
                                    (1455753600000), 
                                    (1455840000000), 
                                    (1455926400000), 
                                    (1456012800000), 
                                    (1456099200000), 
                                    (1456185600000), 
                                    (1456272000000), 
                                    (1456358400000), 
                                    (1456444800000), 
                                    (1456531200000), 
                                    (1456617600000), 
                                    (1456704000000), 
                                    (1456790400000), 
                                    (1456876800000), 
                                    (1456963200000), 
                                    (1457049600000), 
                                    (1457136000000), 
                                    (1457222400000), 
                                    (1457308800000), 
                                    (1457395200000), 
                                    (1457481600000), 
                                    (1457568000000), 
                                    (1457654400000), 
                                    (1457740800000), 
                                    (1457827200000), 
                                    (1457913600000), 
                                    (1458000000000), 
                                    (1458086400000))) i  LEFT JOIN (  
                SELECT  (osysdate- (osysdate % 86400000) ) interval, SUM(field004) totalval FROM onlineactivities WHERE field003 !=300000 and osysdate> 1455605711999 GROUP BY interval ) 
                onl ON i.'' = onl.interval;;

Note that this works in sqlitestudio with sqlite version 3.8.10 but not in android kitkat (not sure about the sqlite version in it) What could be the problem?

Also please check out this question which is what I am trying to do but with sqlite and this answer

score 1 · Accepted Answer · edited May 23 '17 at 11:45

1

The VALUES syntax is available since SQLite 3.8.3, which is not available in every Android version.

To be compatible with earlier SQLite versions, you have to use a compound query:

SELECT 1458111312025 UNION ALL
SELECT 1455667200000 UNION ALL
...

Alternatively, put all the values into a temporary table.

edited May 23 '17 at 11:45

Community

1
1

answered Mar 16 '16 at 08:46

CL.

173,858
17
217
259

Thanks, I also tried using union but it increases the number of terms beyond the supported limit sometimes and crashes. The list of values is dynamically generated by code and can be quite long – Japheth Ongeri - inkalimeva Mar 16 '16 at 08:57
That's no different from VALUES. – CL. Mar 16 '16 at 08:58
I have tested the query using values... and it works in marshmallow but not in 4.4 without any terms problems. I guess this is just an issue with the sqlite version in 4.4 – Japheth Ongeri - inkalimeva Mar 21 '16 at 13:36

How to select from static list of values in android sqlite, syntax error near values(

1 Answers1