[SA 7.5.501 Pro] - Analytic functions

[SA 7.5.501 Pro] - Analytic functions

Analytic functions (LAG, LEAD, PERCENTILE_CONT etc.) and ranking functions (RANK, ROW_NUMBER, etc.) do not fit very well to Custom Assistance settings. I can't get CA to show parameters properly for LEAD, for example. I guess there'll be problem with all the multi parenthesis set functions like ROW_NUMBER() OVER (ORDER BY whatever) (two sets) or PERCENTILE_CONT(whatever_numeric) WITHIN GROUP (ORDER BY order_by_expression) OVER (PARTITION BY whatever_else) (three sets).

Also, the analytic functions are not syntax colored either.

Are these going to be fixed in v8?

I think all of that can be fixed in SA options. Please take a look at Options -> DB Options -> SQL Server -> Custom Assistance -> [built-in functions] section. Please add or update analytical functions as needed.

That's the place where the whole thing fails. Many of those functions bear two or three sets of parenthesis instead of one, like ROW_NUMBER

	Quote:
	ROW_NUMBER() OVER ([partition_by_expression] group_by_expression)

(2 sets)
or PERCENTILE_CONT

	Quote:
	PERCENTILE_CONT (numeric_literal) WITHIN GROUP ( ORDER BY order_by_expression) OVER ([partition_by_clause])

(3 sets)
resulting in the function not to auto-complete properly, since Custom Assistance seems not to be prepared for that. It takes things enclosed in the first set as parameters/arguments and the rest of of the stuff as comment(?).

Also, there's a strange glitch with Custom Assistance. When or why or how it happens, I couldn't figure out yet, but it's rather annoying: every now and then the lines in Custom Assistance for SQL Server, SQLite and MySQL (the three DB types I use the most) are converted into a single line string. Custom Assistance keeps working in spite of that but if I need to change anything, I have to restore it from a saved textfile.

	Quote:
	that's the place where the whole thing fails. Many of those functions bear two or three sets of parenthesis instead of one

I'm afraid in the current version there is nothing you can do about that. It only understands parameters within the standard function(...parameters...) format. anything that goes after the closing ) symbol isn't part of the same function. So called analytical functions are not like classic functions, they rather represent a separate syntax category falling somewhere between general expressions and GROUP BY with sub-queries kind of things (GROUP BY, PRIVOT, UNPIVOT, CTE, etc...).

I was afraid you were going to say that, that's why I asked if they're going to be addressed in v8.

I see that there is an existing task in the queue for enhanced intellisense support for PIVOT, GROUP BY, and for analytical functions planned for 8.x code branch. It has a medium priority and I'm not sure if it will make it into the initial 8 release, I cannot tell it from the current status. We should know that by the end of the summer.