Android Question SQLite "OVER PARTTION" not supported in B4A ?!

Kanne · Apr 14, 2020

Hi,
I wanted to add some statistics to my App and now have the problem, that it seems that "OVER" is not supported by B4A ?!

B4X:

SELECT Spieler, count(*) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag) as Part_SUM
from  Spieler_Spieltag

Executing the statement in "DB Browser for SQLite" works fine, but in B4A I get an error:

** Activity (auswahl) Resume **
Error occurred on line: 2470 (table)
android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling:
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
...

or

** Activity (auswahl) Resume **
Error occurred on line: 2470 (table)
android.database.sqlite.SQLiteException: near "OVER": syntax error (code 1): , while compiling: SELECT Spieltag.ID_Spieltag as Spieltag, Spieltag.Datum, Spieler, Punkte, Platz ,ifnull(count(*) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) as Listen ,ifnull(SUM(Punkte) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) as Punkte_Summe ,ifnull(SUM(Platz) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) as Platz_Summe ,printf("%.2f",ifnull(AVG(Punkte) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) ) as Punkte_Schnitt ,printf("%.2f",ifnull(AVG(Platz) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) ) as Platz_Schnitt FROM Spieler_Spieltag JOIN Spieltag ON Spieler_Spieltag.ID_Spieltag = Spieltag.ID_Spieltag WHERE Spieltag.Gruppentext='Malle 20/21' ORDER BY Spieltag.ID_Spieltag, Platz
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
...

OliverA · Apr 19, 2020

Erel said:
This feature was added in SQLite v3.25

Now I remember! If you use the SQLCipher version, it has an updated SQLite base. Note: you don’t have to use the encryption portion of that library.
Links:
1) https://www.b4x.com/android/forum/threads/sqlite3-fts5.110369/post-688824
2) https://www.b4x.com/android/forum/threads/android-database-encryption-with-sqlcipher-library.14965/

aeric · Apr 20, 2020

Kanne said:
I will take a look on B4XTable, but in the moment I'm very lucky to have flexible table running with help from Klaus.
The SQL-statements you posted may solve the formating when using B4XTable but not the data:
you will not get managed running subtotals by using group statements. So formating was not the request of this thread.

My query is group by Spieler. So the same Spieler would have same value. Not sure how you want to get the Punkte_Summe. If let say you want the Punkte_Summe = SUM by the Datum, then inside the subquery B you can GROUP by Datum.

SQL:

GROUP By SS.Datum  -- Line #17

SQL:

WHERE A.Datum = B.Datum -- Line #20

SQL:

(SELECT SS.Datum,  -- Line #8

aeric said:

View attachment 92014

SQL:

SELECT
A.Spieltag, A.Datum, A.Spieler, A.Punkte, A.Platz,
B.Listen, B.Punkte_Summe, B.Platz_Summe, B.Punkte_Schnitt, B.Platz_Schnitt
FROM
(SELECT S.ID_Spieltag as Spieltag, S.Datum, SS.Spieler, SS.Punkte, SS.Platz
FROM Spieler_Spieltag SS
JOIN Spieltag S ON SS.ID_Spieltag = S.ID_Spieltag) A,
(SELECT SS.Spieler,
ifnull(count(*), 0) as Listen,
ifnull(SUM(SS.Punkte), 0) as Punkte_Summe,
ifnull(SUM(SS.Platz), 0) as Platz_Summe,
printf("%.2f", ifnull(AVG(Punkte), 0)) as Punkte_Schnitt,
printf("%.2f", ifnull(AVG(Platz), 0)) as Platz_Schnitt
FROM Spieler_Spieltag SS
JOIN Spieltag S ON SS.ID_Spieltag = S.ID_Spieltag
WHERE S.Gruppentext = 'Malle 20/21'
GROUP By SS.Spieler
--ORDER BY SS.Spieler
) B
WHERE A.Spieler = B.Spieler
ORDER BY A.Spieltag, A.Platz

Kanne · Apr 20, 2020

I know what a "grouped by" does. Even grouping by detailed fields does make a running subtotal ! I will now stop discussing that in this thread.

pcicom · Apr 20, 2020

Kanne said:
Hi,
I wanted to add some statistics to my App and now have the problem, that it seems that "OVER" is not supported by B4A ?!

B4X:

SELECT Spieler, count(*) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag) as Part_SUM from Spieler_Spieltag

Executing the statement in "DB Browser for SQLite" works fine, but in B4A I get an error:

or

B4X:

SELECT Spieler,Spieldtag, count(*)  as Part_SUM
from  Spieler_Spieltag GROUP BY 1,2 

or  OPCIONAL ORDER

SELECT Spieler,Spieldtag, count(*)  as Part_SUM
from  Spieler_Spieltag GROUP BY 1,2 ORDER BY 1,2

1 = Field Spieler :       1 is a position field on query select
2 = Field Speldtag  :  2 is a position field on query select


[/QUOTE]

Kanne · Apr 21, 2020

please read the complete thread:
the sqlite-windows-function are available from version 3.25 - so you need a very actual Android on your device, on older versons you get a sql-error (like me - that was the reasdon for this thread)

OliverA · Apr 22, 2020

Kanne said:
on older versons you get a sql-error

did you try sqlcipher?

RB Smissaert · Apr 22, 2020

OliverA said:
did you try sqlcipher?

It works with the latest SQLCipher version, 3.25.2.

RBS

RB Smissaert · Apr 22, 2020

RB Smissaert said:
It works with the latest SQLCipher version, 3.25.2.

RBS

To prove it works try these 3 statements:

create table t0(x integer primary key, y text)

insert into t0 values(1, 'aaa'), (2, 'ccc'), (3, 'bbb')

select x, y, row_number() over (order by y) as row_number from t0 order by y

RBS

Erel · Apr 22, 2020

The question was:
"SQLite "OVER PARTTION" not supported in B4A ?!"

The answer is:
1. No such thing as "not supported in B4A". It is the internal native SQLite engine that does all the work.
2. This feature was added in SQLite v3.25

Now go here: https://developer.android.com/reference/android/database/sqlite/package-summary
You will see that the latest documented version in Android is 3.19.

There was also a remark about the unneeded usage of ?!.

For any other discussion please start a new thread.

Android Question SQLite "OVER PARTTION" not supported in B4A ?!

Kanne

Member

Attachments

OliverA

Expert

aeric

Expert

Kanne

Member

pcicom

Member

Kanne

Member

OliverA

Expert

RB Smissaert

Well-Known Member

RB Smissaert

Well-Known Member

Erel

B4X founder

Similar Threads

Android Question SQLite "OVER PARTTION" not supported in B4A ?!

Member

Attachments

Expert

Expert

Member

Member

Member

Expert

Well-Known Member

Well-Known Member

B4X founder

Similar Threads

Privacy & Transparency

Privacy & Transparency