Android exception in sql query that runs fines in DB Browser

user_13337291 0
Viewed 42

When running the following query on Android

select * 
from EICRChecklistItems 
where (Sect, S_Sect, SS_Sect) in (
  select Sect, S_Sect, SS_Sect 
  from EICRCheckList 
  where Version=2018 
  order by id
) 
order by id

I get the exception:

android.database.sqlite.SQLiteException: near ",": syntax error (code 1)

Columns Sect, S_Sect and SS_Sect are all defined as integer.

But when running on DB Browser against a copy of the same database it executes correctly

Any pointers as to what may be causing this would be greatly appreciated.

private var EICR_CHECK_ITEMS_TABLE = "EICRChecklistItems"
private var EICR_CHECKLIST_TABLE = "EICRCheckList"
val cursor1: Cursor = writableDatabase.rawQuery(
            "select * from $EICR_CHECK_ITEMS_TABLE where (Sect, S_Sect, SS_Sect) in (select Sect, S_Sect, SS_Sect from $EICR_CHECKLIST_TABLE where Version='2018' order by id) order by id",  null)
kotlin android-sqlite sqlite android
1 Answers

Since your SQLite's version is prior to 3.15.0 and you can't use ROW VALUES, you can write your query with EXISTS instead of the operator IN:

SELECT ei.* 
FROM EICRChecklistItems ei 
WHERE EXISTS (
  SELECT 1 FROM EICRCheckList el
  WHERE el.Version = 2018
  AND el.Sect = ei.Sect AND el.S_Sect = ei.S_Sect AND el.SS_Sect = ei.SS_Sect
)
ORDER BY ei.id

Note that the ORDER BY clause that you had inside the subquery in your code is actually useless.

user_104988280
Related