Ask Your Question

Valid SQL query won't save

asked 2018-12-12 05:39:41 +0100

gregss gravatar image

updated 2018-12-16 07:29:27 +0100

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 (x64) on Windows 10 Pro x64, version 1809.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-12-12 07:10:07 +0100

Ratslinger gravatar image


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.

edit flag offensive delete link more


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?

gregss gravatar imagegregss ( 2018-12-16 07:39:16 +0100 )edit

Please post the SQL you are using. No, Tools->SQL is not the same as the query section.

Ratslinger gravatar imageRatslinger ( 2018-12-16 16:54:39 +0100 )edit

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

gregss gravatar imagegregss ( 2018-12-18 11:09:17 +0100 )edit

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...

Ratslinger gravatar imageRatslinger ( 2018-12-18 23:20:30 +0100 )edit

Ah, I see now, not only does that just run the query, but it also makes it subsequently savable! Very nice!

gregss gravatar imagegregss ( 2018-12-20 05:11:41 +0100 )edit

answered 2018-12-20 05:25:18 +0100

gregss gravatar image

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.

edit flag offensive delete link more


@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.

Ratslinger gravatar imageRatslinger ( 2018-12-20 21:52:11 +0100 )edit

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.

gregss gravatar imagegregss ( 2018-12-21 08:32:04 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-12-12 05:39:41 +0100

Seen: 291 times

Last updated: Dec 20 '18