Issue with SELECT and the word FROM in DB Objects

[rldawg]

In settings, I have "List all database columns after a SELECT statemet" unchecked.

So in order to get column suggestions, what I willl normally do is type SELECT<enter/newline> and then type FROM <tablename> and then move back up to the select line so that I can get column suggestions.

After the most recent update when i type FROM ... the suggestion box pops up and shows me a list of a couple views i have i.e. vwCompanyNameAddressFromContact that have the word FROM in them and if I hit a space, it will use that view instead of the FROM that I typed.

I guess the new feature is to do matching within the object name vs the start of the object name.

This is more than annoying when your object has the word FROM in it. Can we come up with a better solution?

[Paul Stephenson]

Hi rldawg,

I see your point: the partial matching is now suggesting more items and therefore has a better chance of matching one of your tables or views when you really wanted the word FROM.

The keyword FROM itself is not being shown because it's not valid SQL straight after a SELECT -- if it were, then FROM would be the top choice.

One option would be to use the ssf<tab> snippet to start your queries. This puts in SELECT * FROM and offers you the table list straight away (saving keystrokes!). Then, when you want the columns, you can either go to the * and press Tab to expand them all, or delete the * and press Ctrl+Space to get the column list as normal. You can edit the ssf snippet to include a newline by going to SQL Prompt 5 > Snippet Manager.... Alternatively you could format the SQL afterwards with Ctrl+K Ctrl+Y.

Another option is to create your own snippet (e.g. sf) that contains this:

[rldawg]

"The keyword FROM itself is not being shown because it's not valid SQL straight after a SELECT -- if it were, then FROM would be the top choice"

I will say that my method worked in previous versions...but I can not be sure.

Once i get the FROM typed in SQL prompt doesn't have a problem suggesting table names for me (perhaps knowing that I am trying to do in order to get a proper column list suggested to me). Normally if things are syntactically out of whack, SQL prompt will stop making suggestions.

Howver, I think you gave me some good alternatives that I will try and then report back. Thanks.

[rldawg]

Quick followup: The sf snippet idea is brilliant. As soon as I do that, SQL Prompt immediately suggests objects with no extra key strokes.

Thanks!

[Paul Stephenson]

I'm glad that's working well for you.

Although SQL Prompt often stops suggesting with invalid SQL, it does suggest tables after SELECT FROM, possibly deliberately so that you can enter the table name before the columns as you're trying to do.

Nothing has changed in this area since previous versions except the partial matching. I think the problem is just the combination of using Space as a completion character and having tables (or views) that contain the string "From".
_________________
Paul Stephenson
Project Manager, Red Gate