SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 7.5.501 Pro] - Analytic functions

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 7.5.501 Pro] - Analytic functions
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1401

Post [SA 7.5.501 Pro] - Analytic functions Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
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 View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1401

Post Reply with quote
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.
Mon Jun 13, 2016 3:44 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
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...).
Mon Jun 13, 2016 10:01 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1401

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
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 View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.