Share via

Dbeaver : SQL Server WINDOW functions giving syntax error

Danielle Stanley 1 Reputation point
2022-08-22T12:17:54.69+00:00

I have installed DBeaver as a flatpak in my Pop!_OS laptop. DBeaver is connected to an SQL server 2019 image in a volume using DOCKER. SQL scripts run well, except for the following WINDOW functions:

WINDOW w AS()
NTH_VALUE()
WINDOW eg.:

-- ALTERNATE WAY TO WRITE SQL QUERY USING WINDOW FUNCTIONS
SELECT *,
FIRST_VALUE(PRODUCT_NAME) OVER W AS MOST_EXP_PRODUCT,
LAST_VALUE(PRODUCT_NAME) OVER W AS LEAST_EXP_PRODUCT
FROM PRODUCT
WHERE PRODUCT_CATEGORY ='PHONE'
WINDOW W AS (PARTITION BY PRODUCT_CATEGORY ORDER BY PRICE DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
GO
Listing here the detailed error log:

!ENTRY org.jkiss.dbeaver.model 4 0 2022-08-17 14:26:35.971
!MESSAGE SQL Error [102] [S0001]: Incorrect syntax near 'w'.

Error position: line: 1
!SUBENTRY 1 org.jkiss.dbeaver.model 4 0 2022-08-17 14:26:35.971
!MESSAGE SQL Error [102] [S0001]: Incorrect syntax near 'w'.
!SUBENTRY 2 org.jkiss.dbeaver.model 4 0 2022-08-17 14:26:35.971
!MESSAGE Incorrect syntax near 'w'.
!STACK 0
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'w'.
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:94)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
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:3643)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:172)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4945)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
NTH_VALUE e.g.:

--NTH Value --write a query to display the 2nd most expensive product under each category

SELECT p.*,
NTH_VALUE(product_name)
OVER (PARTITION BY p.product_category
ORDER BY p.price DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sec_most_exp_product
FROM product p
It gives error:

SQL Error [195] [S0010]: 'nth_value' is not a recognized built-in function name.

Error position: line: 1
I also observed that the LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() functions are not syntax highlighted or show autocomplete, though they do work properly. I checked the Window() and NTH_VALUE syntax's online but unable to figure out the correct syntax it is asking here.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Wayne Erfling 6 Reputation points
    2022-08-22T16:02:56.35+00:00

    @Danielle-Stanley-5458:

    As far as I can tell:

    SELECT - WINDOW (that's how it appears in the left nav bar) is only present with SQL Server 2022:
    https://learn.microsoft.com/en-us/sql/t-sql/queries/select-window-transact-sql?view=sql-server-ver16

    And I don't see that NTH_VALUE() is available in Transact-SQL at all.

    You may already know how to calculate "nth_value" using other methods, for example:
    https://www.sqlservergeeks.com/find-nth-max-value-sql-server/

    1 person found this answer helpful.
  2. Isabellaz-1451 3,616 Reputation points
    2022-08-23T03:29:51.513+00:00

    Hi @Danielle-Stanley-5458

    Maybe you can use ranking window function like RANK() OR DENSE_RANK() OR ROW_Number()

    More details, please refer to :https://www.sqltutorial.org/sql-window-functions/

    Best Regards,
    Isabella

    0 comments