Incorrect syntax with Windows Functions and Where clauses
SOZDBA
Posts: 4 New member
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:
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.
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.
Tagged:
Best Answer
-
Eddie D
Posts: 1,802 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
Answers
The new Code Analysis also does not highlight this.