2

I am trying to use the following query as part of a larger query in my Android SQLite datbase, but it seems to me that the errors I'm receiving point to Android SQLite not supporting the lead() function yet.

I've tried looking through the release log on sqlite.org and was unable to find when support for window functions like lead() were added. I've also been unable to find any other source for this particular information.

My query:

...
SELECT *
  , CASE WHEN WorkTypeID != 1 THEN
      lead(StartTimeUTC, 1, StartTimeUTC)
      OVER (PARTITION BY OrderID ORDER BY StartTimeUTC)
    ELSE StartTimeUTC END AS EndTimeUTC
FROM TimeCardEntries
...

This part of my query works just fine in the windows desktop program "DB Browser for SQLite" which I was using to develop my query. However, in Android I'm getting errors like "E/SQLiteLog: (1) near "OVER": syntax error" and I get the same or similar error when trying to use some of the online validators as well.

I'm open to the possibility that I really do have a syntax error I'm overlooking but I'm leaning toward the problem being support for the lead() function.

RKRK
  • 1,284
  • 5
  • 14
  • 18
Ben Bloodworth
  • 900
  • 11
  • 26
  • `lead()` and `lag()` were added [in June 2018](https://www.sqlite.org/src/info/ef34207073c21ce8). At most, I would expect Android Q to support them, just given the timing. I'm not certain how to find a SQLite version from that page, though. – CommonsWare May 30 '19 at 20:25
  • 3.25 is when they were added. – Shawn May 30 '19 at 20:28
  • Thank you for the quick responses. This information answers my question. For compatibility my minSdkVersion is Android 5.1.1 which uses SQLite 3.8.6.1. so I will be unable to use this function. – Ben Bloodworth May 30 '19 at 20:41
  • You could [package a newer SQLite with your app](https://stackoverflow.com/a/56385429/115145), but that has development and runtime costs. – CommonsWare May 30 '19 at 21:01

0 Answers0