Other [SOLVED]SQLite ROW_NUMBER don't work

(Exception) java.lang.Exception: android.database.sqlite.SQLiteException: near "OVER": syntax error (code 1 SQLITE_ERROR[1]): , while compiling:
INSERT INTO RigheOrdine (sigla, serie, numero, data, cliente, riga, articolo, quantita, quantitamx) SELECT ? as sigla, ? as serie, ? as numero , ? as data, ? as cliente, (ROW_NUMBER() OVER(ORDER BY r.riga))+1000 riga, r.articolo, r.quantita, 0 as quantitamx FROM RigheOrdineSpeciale r INNER JOIN Articoli a ON r.articolo=a.codice WHERE cliente=?; Using other value instead of "(ROW_NUMBER() OVER(ORDER BY r.riga))+1000" (for example a fixed data or a field r.riga) all work correctly but result isn't my pourpose.
I'll try to use ROW_NUMBER function on SQLiteStuio in my Winows PC wtith the same database and all work correctly.
This is my Code but i'am nearly sure thet is correct:

Public Sub ReplaceWithSpecialOrder(o As Order) As Boolean
    Dim success As Boolean=False
    mSQL.BeginTransaction
        mSQL.ExecNonQuery2($"UPDATE RigheOrdine SET quantita=0
                            WHERE sigla=? AND serie=? AND numero=? AND data=? AND cliente=?"$, _
                    Array As String (o.document,o.Serial,o.Number,DateTime.Date(o.date),o.Customer.code))
        mSQL.ExecNonQuery2($"INSERT INTO RigheOrdine (sigla, serie, numero, data, cliente, riga, articolo, quantita, quantitamx)
                            SELECT ? as sigla, ? as serie, ? as numero , ? as data, ? as cliente, (ROW_NUMBER () OVER (ORDER BY riga))+1000 riga, r.articolo, r.quantita, 0 as quantitamx
                                FROM RigheOrdineSpeciale r INNER JOIN Articoli a ON r.articolo=a.codice
                            WHERE cliente=?;"$, _
                            Array As String (o.Document, o.Serial, o.Number, DateTime.Date(o.date), o.Customer.Code,o.Customer.Code))
        mSQL.TransactionSuccessful
        success=True
        'Solo se ha rilevato i dati di testata ordine legge le relative righe
        If o.IsInitialized Then
            Dim rs As ResultSet
            rs=mSQL.ExecQuery2($"SELECT riga, articolo, quantita, quantitamx
                        FROM RigheOrdine
                        WHERE sigla=? AND serie=? AND numero=? AND data=? AND cliente=?"$, _
                    Array As String (o.document,o.Serial,o.Number,DateTime.Date(o.date),o.Customer.code))
            Do While rs.NextRow
                Dim r As OrderRow
                r.Initialize
                r.InitializeFromDB(rs)
                o.Rows.Add(r)
            rs.Close
        End If
    Catch
        Log(LastException)
    End Try
    mSQL.EndTransaction
    Return success
End Sub

I know that i can solve using only a SELECT statement and then cycling the ResultSet, using an INSERT, i could write my data on table, but is less performant.
Someone could help me?
Thanks

I have found the solution.
Is a SQLite version problem. ROW_NUMBER is avaible only from SDK 30 (Anroid 11) and i'll try with an Android 10.
P.S.: Sorry but i'am non able to delete this thread

This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies. Accept Learn more…