Author |
Message |
gemisigo
Joined: 11 Mar 2010 Posts: 2167
|
|
[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?
|
|
Thu Jun 09, 2016 8:20 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
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.
|
|
Fri Jun 10, 2016 10:22 am |
|
 |
gemisigo
Joined: 11 Mar 2010 Posts: 2167
|
|
|
|
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
 |
 |
ROW_NUMBER() OVER ([partition_by_expression] group_by_expression)
|
(2 sets)
or PERCENTILE_CONT
 |
 |
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.
|
|
Mon Jun 13, 2016 3:44 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
 |
 |
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...).
|
|
Mon Jun 13, 2016 10:01 am |
|
 |
gemisigo
Joined: 11 Mar 2010 Posts: 2167
|
|
|
|
I was afraid you were going to say that, that's why I asked if they're going to be addressed in v8.
|
|
Tue Jun 14, 2016 9:39 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
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.
|
|
Tue Jun 14, 2016 10:07 am |
|
 |
|