I am trying to select the first 20 rows from a table in Base, but I keep getting syntax error. Apparently LO does not accept the “LIMIT” (or “FIRST”) constraint on a SELECT statement. According to the Firebird database web site this is a perfectly legal statement. I have tried using this in a Query SQL statement and in the “SQL” option under “Tools” and it fails consistently. If I remove the “LIMIT 20” statement, the SELECT statement works correctly.
Hello,
Have had no problem with limit in either HSQLDB or Firebird embedded. Please post your entire SQL statement, your OS, and the specific LO version you are using.
Windows 10, LO 6.4, Firebird 3.0. Application I am trying to develop is for golf handicapping.
Here is the SQL:
SELECT LIMIT 20 “Last Name”, “First Name”, “Game Date”, “Game Occurrance”, “Course ID”, “TeeBox ID”, “Game Score”, “Differential”
FROM “Query_Games”
WHERE “Last Name” = :PlayerName ORDER BY “Game Date” DESC
If I remove the LIMIT 20 it works correctly.
FIRST and SKIP do work immediately after Select but you must turn on Run SQL command directly (toolbar toggle or on Edit menu ) else you get a syntax error.
Thank you and Ratslinger. I made LIMIT a predicate and apparently, because the source data is a query, I needed to include the table name (in this case a query) and an “alias” for each item selected. It is now working.