Page 1 of 1
Problem with SQLite query
Posted: Fri Mar 08, 2024 10:44 pm
by doctorized
I make an app that handles school library. I try to run the query:
Code: Select all
Select L.book_id, B.Title, count(*) from Lendings L left join Books B on L.book_id = B.id where L.stud_id like '2023-2024%' group by L.book_id order by count(*) DESC
and I get error:
The exactly same query with
DB Brower for SQLite is running just fine. Any suggestions why?
Re: Problem with SQLite query
Posted: Sat Mar 09, 2024 1:12 am
by ChrisR
No tables to test it but to get L.book_id, B.Title, count(*), Group By should probably use the same fields: L.book_id, B.Title
Code: Select all
Select L.book_id, B.Title, count(*) from Lendings L left join Books B on L.book_id = B.id where L.stud_id like '2023-2024%' group by L.book_id, B.Title order by count(*) DESC
Re: Problem with SQLite query
Posted: Sat Mar 09, 2024 9:41 am
by doctorized
I have:
Code: Select all
CREATE TABLE Books (id TEXT PRIMARY KEY, Title TEXT, Writer TEXT, Publisher TEXT, Year INT, Copies TINYINT, ISBN TEXT)
CREATE TABLE Lendings (book_id TEXT, stud_id TEXT, LDate DATE, Returned TINYINT, RDate DATE)
Your query returns the same error.
On the other hand,
runs just fine.
Re: Problem with SQLite query
Posted: Sat Mar 09, 2024 11:03 am
by mk-soft
Without Data no error here
Code: Select all
UseSQLiteDatabase()
#Database = 0
Define dbfile.s, sql.s, r1
Procedure CheckDatabaseUpdate(Database, Query$)
Result = DatabaseUpdate(Database, Query$)
If Result = 0
Debug DatabaseError()
EndIf
ProcedureReturn Result
EndProcedure
Procedure CheckDatabaseQuery(Database, Query$)
Result = DatabaseQuery(Database, Query$)
If Result = 0
Debug DatabaseError()
EndIf
ProcedureReturn Result
EndProcedure
dbfile = ":memory:"
If OpenDatabase(#Database, dbfile, "", "")
Debug "Create Tables ..."
sql = "CREATE TABLE Books (id TEXT PRIMARY KEY, Title TEXT, Writer TEXT, Publisher TEXT, Year INT, Copies TINYINT, ISBN TEXT)"
CheckDatabaseUpdate(#Database, sql)
sql = "CREATE TABLE Lendings (book_id TEXT, stud_id TEXT, LDate DATE, Returned TINYINT, RDate DATE)"
CheckDatabaseUpdate(#Database, sql)
Debug "Query ..."
;sql = "select count(*) from books"
sql = "Select L.book_id, B.Title, count(*) from Lendings L left join Books B on L.book_id = B.id where L.stud_id like '2023-2024%' group by L.book_id order by count(*) DESC"
If CheckDatabaseQuery(#Database, sql)
Debug "Result ..."
While NextDatabaseRow(#Database)
Debug GetDatabaseString(#Database, 0)
Wend
FinishDatabaseQuery(#Database)
EndIf
Debug "End ..."
CloseDatabase(#Database)
EndIf