I would appreciate a little help with an SQL Select I´m attempting to run against an SQLITE DB. Any ideas why this Select won´t run on?
SELECT B.ID AS Bale_ID, P.Code AS P_Code,
B.Permanent_Bale_ID, b.Bale_Storage_Location,
S.Contract_Number, S.Contract_Date AS S_Contract_Date, S.Date_Ship
FROM BALES AS B INNER JOIN SHIPMENTS AS S
ON S.ID = B.Shipment_ID
INNER JOIN Partners AS P
ON P.ID = S.Partner_ID
ORDER BY S.Date_Ship
The Select runs fine on “DB Browser for SQLite” but returns a DB error when try the statement using a DB.SQLSelect(SQL).
Thanks for your reply, this looks like the cause of my problem. I will read the link that Norman posted.
I tried this but it didn´t work either
ELECT B.ID AS Bale_ID, P.Code AS P_Code,
B.Permanent_Bale_ID, b.Bale_Storage_Location,
S.Contract_Number, S.Contract_Date AS S_Contract_Date,
S.Date_Ship
FROM BALES B INNER JOIN SHIPMENTS S
ON S.ID = B.Shipment_ID INNER JOIN Partners P
ON P.ID = S.Partner_ID ORDER BY S.Date_Ship
Still can´t get this to work. I removed all the Aliases from the columns and tables list, here is the Xojo code I´m attempting to run. I know… I know… this is just a test, I will code it better as soon as I get the SQL syntax to work. This too works on a
It might help to get an SQLite Database manager to test your query
Personally I suggest Tadpole but full disclosure… I wrote it
One of my current apps has a query that is well over 100 lines long (lots of UNION)… and Tadpole saved me hours as I could test sections to find the error…
Once each section worked… I put it into Xojo like you did above…
Suggestion… you might find this format easier to read
SQL = _
"?SELECT B.ID, P.Code, B.Permanent_Bale_ID, " +_
" B.Bale_Storage_Location, " +_
" S.Contract_Number, S.Contract_Date, " +_
"S.Date_Ship " +_
"FROM BALES B " +_
"INNER JOIN SHIPMENTS S " +_
" ON S.ID = B.Shipment_ID " +_
"INNER JOIN Partners P " +_
" ON P.ID = S.Partner_ID " +_
"ORDER BY S.Date_Ship DESC "
[quote=455141:@Dave S]It might help to get an SQLite Database manager to test your query
Personally I suggest Tadpole but full disclosure… I wrote it
One of my current apps has a query that is well over 100 lines long (lots of UNION)… and Tadpole saved me hours as I could test sections to find the error…
Once each section worked… I put it into Xojo like you did above…
Suggestion… you might find this format easier to read
SQL = _
"?SELECT B.ID, P.Code, B.Permanent_Bale_ID, " +_
" B.Bale_Storage_Location, " +_
" S.Contract_Number, S.Contract_Date, " +_
"S.Date_Ship " +_
"FROM BALES B " +_
"INNER JOIN SHIPMENTS S " +_
" ON S.ID = B.Shipment_ID " +_
"INNER JOIN Partners P " +_
" ON P.ID = S.Partner_ID " +_
"ORDER BY S.Date_Ship DESC "
[/quote]
I´ll get Tadpole! I have tested the SQL code using DB Browser for SQLite Database manager and it runs OK in it. I´ll disect the SQL syntax bit by bit. Your formatting suggestion looks good, will try!
[quote=455137:@Hector Marroquin]I´m getting a
1 - near “?SELECT”: syntax error[/quote]
Error Code 1 is a “SQL logic error or missing database”. Since the SQL looks fine, you should make sure you’re connecting to the correct db file.
Thanks for your reply. The SELECT * FROM Bales was the first thing I tested and it works fine, right now I´m rebuilding the SQL statement bit by bit, so far this simple join is working:
SELECT B.ID AS BALE_ID, B.Permanent_Bale_ID FROM Bales AS B INNER JOIN Shipments AS S ON B.SHIPMENT_ID = S.ID
[quote=455147:@Alberto DePoo]Hector, you have “invisible” characters on your code. I guess you copied the SELECT statement from some app and pasted directly to Xojo.
I think if you use the option to “clean invisible ascii characters”, then your code will work.[/quote]
Ohhhhh this never crossed my mind! Will check right now!
[quote=455147:@Alberto DePoo]Hector, you have “invisible” characters on your code. I guess you copied the SELECT statement from some app and pasted directly to Xojo.
I think if you use the option to “clean invisible ascii characters”, then your code will work.[/quote]
Thanks Alberto! You saved me a night of no sleep. I indeed copied the code from a SQLite manager where I wrote it, never thought I was getting invisible characters.