Incorrect syntax with Windows Functions and Where clauses

SOZDBA

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.



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



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



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.

Eddie D

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

SOZDBA

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.