Defect Submission: SUM breaks Suggestions Window

njahncke

DESCRIPTION:
Use of the aggregate function SUM() in a SELECT list prevents suggestions pertaining to the context of the query from displaying prior to the placement of the function call within the SELECT list. This most likely applies to other functions as well.

STEPS TO REPRODUCE:
1. Enter the following code:

USE [AdventureWorks2008]
GO

SELECT
	
	SUM([SOD].[OrderQty]) AS [OrderQtyTotal],
	
FROM
	[Sales].[SalesOrderDetail] AS SOD
WHERE
	[SOD].[SalesOrderID] = 43659

2. Place the cursor on line 5 and type "Prod". The SQL Prompt suggestions window should prompt for the entry of [SOD].[ProductID]. Instead, it prompts at the table level ([Production].[Product]).

3. Place the cursor on line 7 and type "Prod". The SQL Prompt suggestions window prompts for the entry of [SOD].[ProductID] as expected.

NOTE:
This behavior is not a result of the layout of the code used in the example.

MikeyC

The problem is that you have a query that doesn't have the correct syntax at that point.

If at line 5 you put a comma first, and then type PROD in front of the comma you will get the correct suggestions.

On line 7 it works because you have the comma in place already.

njahncke

Sure there are workarounds, but it's still a usability issue. If they feel the behavior is in line with the product's requirements and don't want to fix it, whatever. I don't really care, I just like SQL Prompt and want to see it get better.

MikeyC

In your example the only valid suggestions I can come up with at line 5 would be ALL, DISTINCT, and TOP.

I agree it would be nice if it could offer suggestions, but how would they know when you were typing an alias, and when you were adding a new column if you don't supply the correct syntax? (i.e. the comma)

I run into too, but I am getting used to putting the comma in before adding a new column to the SELECT list now to avoid it.