New features added to SQL Prompt Watch now.

No highlighting in the ORDER BY of a windowed function

a.higginsa.higgins Posts: 86 Bronze 2
edited June 24, 2016 12:24PM in SQL Prompt
When I use a windowed function, such as ROW_NUMBER(), "highlight matching objects" does not pick up on aliases and column names within the ORDER BY section.

For example, in the following code, placing the caret on the alias 'mt' or on the column 'y' does not highlight the matching uses of those objects within the ROW_NUMBER() function's ORDER BY:
DECLARE	@MyTable TABLE (x INT,y BIT)

	SELECT
		mt.x
		,mt.y
		,ROW_NUMBER() OVER 
			(
				PARTITION BY mt.x 
				ORDER BY mt.y		-- neither 'mt' or 'y' pick up highlighting
			)
	FROM
		@MyTable mt
	ORDER BY mt.x

Comments

  • Aaron LAaron L Posts: 596 New member
    Hi a.higgins,

    Which version of SQL Prompt are you currently using? I just tested this here on 7.2.4 and it appears to be working:

    sfu3ohF.png

    If you're not on the latest version could you see if upgrading helps?

    Thanks,
    Aaron.
  • a.higginsa.higgins Posts: 86 Bronze 2
    Up until this morning, I was using 7.2.2.273, and the highlighting didn't work:

    2i95qnb.png

    I downloaded 7.2.4.291 an hour ago, and apparently something you did fixed this issue, since I'm also not seeing it now. Reading over the release notes, perhaps it was a side effect of "Support ticket 67262: Fix for rename alias/variable inside OVER clauses not working"?
  • Aaron LAaron L Posts: 596 New member
    I'm glad to hear it's now working for you!

    I think you're right and it's likely the fix for support ticket 67262 also fixed the issue you were seeing as the same underlying system is used for both to detect where objects are in the script.

    Thanks,
    Aaron.
Sign In or Register to comment.