Other [SOLVED]SQLite ROW_NUMBER don't work

[Teech]

Using ROW_NUMBER in a SQLite statement i receive this error:

(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
    Try
        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)
            Loop
            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

 

[Teech]

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