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.