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

Status
Not open for further replies.

Kanne

Member
Licensed User
Longtime User
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)
...
 

Attachments

  • 2020-04-14_103227.jpg
    2020-04-14_103227.jpg
    130.4 KB · Views: 146

OliverA

Expert
Licensed User
Upvote 0

aeric

Expert
Licensed User
Longtime User
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

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
 
Last edited:
Upvote 0

Kanne

Member
Licensed User
Longtime User
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.
 
Upvote 0

pcicom

Member
Licensed User
Longtime User
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]
 
Upvote 0

Kanne

Member
Licensed User
Longtime User
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)
 
Upvote 0

OliverA

Expert
Licensed User
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
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.
 
Upvote 0
Status
Not open for further replies.
Top