I have a SQL query which executes fine in “Tools” | “SQL…” If I go to “Queries” “Create Query in SQL View…” and enter the exact same text, it fails to save with a syntax error “SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE”
Any idea why it works in one place but not the other?
I should add that I’m using LO 6.1.3.2 (x64) on Windows 10 Pro x64, version 1809.
Hello,
It appears you have code at the end of you statement which the interpreter doesn’t like (looks like ‘$end’). Either remove that portion or turn on Run SQL command directly. When you execute in Tools->SQL there is no interpreter & it automatically runs directly.
There’s no such code at the end of my SQL statement to remove unfortunately. Would not Tools->SQL also complain about any such extraneous code on the end?
Please post the SQL you are using. No, Tools->SQL is not the same as the query section.
Here it is; it selects the last day of the next month
select
“Days” +
case mod(year(now), 400)
when 0
then 1
else
case mod(year(now), 100)
when 0
then 0
else
case mod(year(now), 4)
when 0
then 1
else 0
end
end
end
as “LastDay”
from
“DaysPerMonth”
where
“Month” = mod(month(now()), 12) + 1
As stated in the answer - turn on Run SQL command directly in the query. It runs without a problem. Something in the statement (may even be the case section - not sure) is not liked by the interpreter/parser. The ‘Run directly’ bypasses that as does Tools->SQL...
Ah, I see now, not only does that just run the query, but it also makes it subsequently savable! Very nice!
When you save a query from the query design view, Base interposes some interpreter/parser that may not like your SQL code. You can work around this by using ‘Edit’ | ‘Run SQL command directly’. This will not produce any noticeable effect, however, you should be able to save the query from the query designer now. In my testing, it seems to work no matter what you have entered for the query text, actually.
@gregss Your statement is not quite correct. With ‘Run SQL command directly’ turned on, you will not be able to use parameters in this mode. There may be other items but this is the biggest.
That’s a useful limitation to be aware of; thanks! I haven’t gotten around to figuring out how to use parameters in queries yet, so I will keep this in mind for that time.
I know the post is old, and the solution affered works, but nevertheless I want to mention that this quirk persists, even for super-simple queries.
like " SELECT 1; " I think something is amiss.
Strangely, after executing ‘directly’ and saving the query, it runs without error!
(I came across this when trying to use the sqlite functions, which odbc does offer, apparently; but the same workaround works, at least for a date() that I tried.)
I use Libreoffice 7.0.4.2 (the version from the standard repo on my MXLINUX 21) and connect to a sqlite3 database with odbc (all recently downloaded – I am a newbie kicking off MSACCESS).
Depending on the database in use, this may be a valid query. However, most database engines expect a FROM clause. The database in use is indicated in the status bar of your Base document with more details in menu:Edit>Database>Connection | Properties.
LibreOffice Base is far away from being like MS Access. This is just a small addition to this office suite whereas Access is a database development suite. A LIbreOffice installation weighs less than MS Access alone. Many Linux distributions pre-install LO without Base. The difference amounts to ~40 MB including several database drivers for external databases.
Edit: Ooops, it’s about SQLite.
Direct SQL is the equivalent of “Pass Through” in MS Access.
Good Morning Villeroy,
Does this query example be of any help to the topic?
SELECT "IDCourse", "Student", "begin", "end" FROM "Table1" WHERE "begin" BETWEEN {d '2024-01-15' } AND {d '2024-02-15' }
Thank you for your kindness
Thank you for taking the trouble to respond. However, I think that database particularities are not to blame. (sqlite3 in my case). I just tried to find the simplest query that showed this behaviour, hence “SELECT 1;” , but here is another one:
select wisseldatum from wisselkoersen;
executes (F5) without further ado;
but now I want to leave off the time part:
select date(wisseldatum) from wisselkoersen;
F5 gives an error, but the command executes (and can be saved) after Edit → ‘Run SQL command directly’ is chosen (and also executes with Tools → SQL … )
So it really seems a matter of the translation from Base into the connector.
I am happy that there is a workaround, but if you want me to try some other variation in order to pinpoint the trouble, I am willing to help.