Red Gate forums :: View topic - SQL Prompt 6.3 beta (Now closed)
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 6
SQL Prompt 6 forum

SQL Prompt 6.3 beta (Now closed)

Search in SQL Prompt 6 forum
Post new topic   Reply to topic
Jump to:  
Go to page Previous  1, 2
Author Message
PDinCA



Joined: 25 Jul 2005
Posts: 511
Location: Costa Mesa, CA, USA

PostPosted: Thu Mar 06, 2014 7:30 am    Post subject: Reply with quote

Attributes used to come first for WHERE clauses - now they are last and that's highly counterproductive. I get other table then function names before columns. Is it just me? Using the 188 build, win8.1 x64, SQL2012EE
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 207
Location: Red Gate Software

PostPosted: Thu Mar 06, 2014 12:11 pm    Post subject: Reply with quote

Hi PDinCA,
I'm having difficulty recreating your issue - I always get columns suggested first on a where clause. Would it be possible to get a script or a screenshot of where this is happening for you?

Mr.DiGi, Unfortunately I don't think there's enough logging in the current build to help figure out where qualify object names is going wrong. Does it hang every time for you? Even on older, non-beta builds?
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 511
Location: Costa Mesa, CA, USA

PostPosted: Thu Mar 06, 2014 11:17 pm    Post subject: Reply with quote

Yesterday's script:
Code:
INSERT INTO dbo.t_company_custom_def
     ( custom_id
     , custom_company_id
     , custom_xml
     , update_date
     , update_by
     )
SELECT @NewID
     , 30
     ,[custom_xml]
     , '20140305 15:45'
     , 'me'
  FROM [xcp_ref].[dbo].[t_company_custom_def] ccd
 WHERE co

First off, having completed the WHERE and a space, it gave me ccd, the alias, as expected, and correct. I typed c and the ccd alias reamined at the top, followed by columns, so I typed an o, to get to "WHERE co" as in the code above. That's when the whole list changed to a list of f(x) functions, headed by COALESCE, then CONTAINS, then a bunch of tables (I have them listed by schema), then a slew of sys.functions, and I don't see the "custom_COmpany_id" (in the enlarged pop-up) that I expected my CO typing to bring to the top of the list. SQL Prompt at this point becomes useless for rapid code completion.

I would really like to send you a SnagIt or Snip, but Windows8.1 has completely destroyed my ability to use them. I hope you can create a simple table like the above, and reproduce the problem...

Failing that, a remote diagnostic session would be fine by me.
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 207
Location: Red Gate Software

PostPosted: Fri Mar 07, 2014 12:25 pm    Post subject: Reply with quote

Hi PDinCA,
I just gave your script a try and I think this behavior might be by design. Prompt will display any objects matched by the prefix first (COALESCE, CONTAINS etc all start with CO) followed by any partial matches.

I get the column listed much further down as it's not considered that "good" a match yet:


To get a better match against the columns you should be able to type the first letter or two of each word (eg. Typing "cu_co", "cco" or even "_co" will give a better match against custom_company_id)

Edit: as of 6.3.0.240 the columns should be at the top of the partial matches list, objects matched by a prefix are still first however.
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 511
Location: Costa Mesa, CA, USA

PostPosted: Tue Mar 11, 2014 6:20 pm    Post subject: Reply with quote

I must admit to being surprised by the volume of what I will call "noise" listed above potential columns that are partially matched.

Thinking about the context of the SQL Prompt suggestions, when one is writing a SQL WHERE clause, how frequently is a column in a FROM clause the actual subject of the WHERE versus some obscure system function, another table name (huh?), or a function? I'd suggest that in excess of 95% of the time the COLUMN is what is wanted... It is acknowledged BAD PRACTICE to use functions in WHERE clauses anyway, or so I believe.

I'm out of votes on SQL Prompt (two logins' worth), so may I here suggest:

Provide an option to "always list columns in suggestion lists, even partial matches, above any functions of any kind".

In over 25 years of coding SQL, I can safely say the number of times I want a built-in or sys. function as the FIRST artifact after my WHERE is 0.05% of the time, or less!

Doesn't "columns first" simply just make sense? Or maybe I really am missing something...
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 207
Location: Red Gate Software

PostPosted: Tue Mar 18, 2014 3:18 pm    Post subject: Reply with quote

Hi PDinCA,
Could you give 6.3.0.252 a try and see if that's closer to what you'd prefer? (We normally sort by prefix/partial match and then the object type, build 252 should reverse this so columns and aliases are kept at the top for the WHERE clause)
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 511
Location: Costa Mesa, CA, USA

PostPosted: Wed Mar 19, 2014 12:13 am    Post subject: Reply with quote

Thanks Aaron, installed and makes a lot more sense. Tried several partial matches on wide tables and everything looks good. Appreciate the swift turnaround.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page Previous  1, 2
Page 2 of 2

 
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