Does JOIN works with LIMIT and OFFSET along WHERE clause

Question

I'm working on spring boot project where I am trying to run following query on both sql client and spring boot code.

SELECT two.STORE_NBR, two.TLE_WORK_ORDER_ID, 
       tj.SERVICE_START_TS, tj.SERVICE_END_TS, tj.WIN_NBR 
FROM       TLE_WORK_ORDER AS two 
INNER JOIN TLE_TECHNICIAN_JOB AS tj 
        ON ((two.TLE_WORK_ORDER_ID = tj.TLE_WORK_ORDER_ID)) 
WHERE CREATION_TIMESTAMP LIKE '%2017-11-13%' 
LIMIT 5 OFFSET 2;

The data type of CREATION_TIMESTAMP is string.

This query is throwing an error.

Can someone explain that what is the issue with this query?

The query is throwing following error:

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [102] [S0001]: Incorrect syntax near 'LIMIT'. at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:577) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:486) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:172) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:493) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:894) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3710) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:172) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4949) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'LIMIT'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3274) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:743) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131) at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:96) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131) ... 12 more

Can you update your post with what error your query is throwing? Also what's the DBMS you're dealing with? — lemon, Jan 27 '23 at 19:05
@lemon I have updated question with this information. Kindly look at updated question . — Umair, Jan 27 '23 at 20:28
LIMIT is unknown in sql-server, see: [What is the T-SQL equivalent of MySQL syntax LIMIT x, y?](https://stackoverflow.com/questions/10440688/what-is-the-t-sql-equivalent-of-mysql-syntax-limit-x-y) — Luuk, Jan 27 '23 at 20:31
Choose a proper date/time data type instead. https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver16#DateandTimeDataTypes — jarlh, Jan 27 '23 at 20:33
Use `ORDER BY tj.SERVICE_START_TS OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY` in place of `LIMIT 5 OFFSET 2`. — lemon, Jan 27 '23 at 20:34
Reading the (long) error message should have solved the problem. (I do mean this part: "**Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'LIMIT'**" ) The error message indicate that you use [tag:dbeaver], so I added this tag. Conclusion: Do not be afraid of long error messages, just start reading (and thinking!), line by line — Luuk, Jan 28 '23 at 09:12

score 0 · Answer 1 · answered Jan 27 '23 at 20:41

Since you're using SQL-Server, you can't use LIMIT <N> OFFSET <M> notation. You should rather use OFFSET FETCH, that uses the ORDER BY clause to determine the order of the rows, before truncating your result set.

In your specific query, you seem to be using "tj.SERVICE_START_TS", "tj.SERVICE_END_TS" as two timestamp fields, to be used in the ORDER BY clause.

Your final query should look like:

SELECT two.STORE_NBR, two.TLE_WORK_ORDER_ID, 
       tj.SERVICE_START_TS, tj.SERVICE_END_TS, tj.WIN_NBR 
FROM       TLE_WORK_ORDER AS two 
INNER JOIN TLE_TECHNICIAN_JOB AS tj 
        ON ((two.TLE_WORK_ORDER_ID = tj.TLE_WORK_ORDER_ID)) 
WHERE CREATION_TIMESTAMP LIKE '%2017-11-13%' 
ORDER BY tj.SERVICE_START_TS, tj.SERVICE_END_TS 
OFFSET 2 ROWS 
FETCH NEXT 5 ROWS ONLY;

Recommendation: It's a bad habit to use VARCHAR datatypes for date fields, in place of the DATE, DATETIME or TIMESTAMP fields. SQL-Server operates faster and gives you tool to deal with dates.

Does JOIN works with LIMIT and OFFSET along WHERE clause

1 Answers1