What are the challenges you face when working across database platforms? Take the survey

Defect Submission: SUM breaks Suggestions Window

njahnckenjahncke Posts: 23
edited June 3, 2010 8:00PM in SQL Prompt Previous Versions
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.

1. Enter the following code:
USE [AdventureWorks2008]

	SUM([SOD].[OrderQty]) AS [OrderQtyTotal],
	[Sales].[SalesOrderDetail] AS SOD
	[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.

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


  • Options
    MikeyCMikeyC Posts: 249 Bronze 3
    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.
  • Options
    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.
  • Options
    MikeyCMikeyC Posts: 249 Bronze 3
    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.
Sign In or Register to comment.