Incorrect syntax with Windows Functions and Where clauses

SOZDBASOZDBA Posts: 4 New member
edited December 7, 2017 1:59PM in SQL Prompt
Let me preface this by saying that I am a great admirer of SQL Prompt, from a productivity point of view but also as I think that it could be used as a learning aid for people trying to learn T-SQL.

With that I mind I'd like to point something out...

Given the following code:
USE tempdb;

CREATE TABLE dbo.DummyTable (
    DummyId     int     IDENTITY,
    PartitionId tinyint NOT NULL
);

INSERT INTO dbo.DummyTable (
    PartitionId
)
SELECT TOP (1000)
    number % 5
FROM    [master].dbo.spt_values
WHERE   [type] = N'P';

SELECT  DummyId,
        PartitionId,
        ROW_NUMBER() OVER (PARTITION BY PartitionId ORDER BY DummyId DESC) AS LastN
FROM    dbo.DummyTable;

and a new TSQL user is given a request to find the last 2 rows per PartitionId so they decide to use a Windows Function.
Given the autocomplete features of SQL Prompt, they see the option to use the column LastN in the WHERE clause.

tsrskx7mmj6e.png

Not only that but SQL Prompt auto-expands this column out to its windows function and doesn't highlight it as incorrect syntax

01ygf3auvv3h.png

Nothing is given to the user to indicate that this isn't supported, the only indication is when they attempt to run the code and it errors out

97p01c2s9v0o.png

As I said at the start; I love using SQL Prompt, especially as a teaching tool but I think something should be done about the above. Either not allowing this to be selected as an option in the dropdown options, or highlighting it as an error.

Apologies if this is the wrong place or already highlighted.

UPDATE:

Just a follow up to confirm that this still occurs in Version 9.0.1.3179.

The new Code Analysis also does not highlight this.

Best Answer

  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi

    Thank you for your forum post.

    Using your instructions I replicated the problem and submitted a new Bug Report whose reference is SP-6771.

    I cannot provide an ETA as to when a Bug Fix version will become available. I will update this forum post when a fix version does become available.

    Thank you for bringing this problem to our attention.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com

Answers

Sign In or Register to comment.