Autocompletion in Queries

When editing queries, Manifold will offer to autocomplete SQL statements, clauses and operators as well as table names and columns, popping up lists of key words and other useful items, depending on the context, to help guide us in writing the query. Autocompletion provides several key benefits:

 

§      Increased editing speed.

§      Fewer typographical errors.

§      Consistent capitalization style

§      Quick reference to component, table and column names in the project.

§      Quick reference to SQL keywords, statements, clauses, functions and operators.

 

The default behavior is to pop up a list of possible completions when a candidate word is started or when a left bracket, [ character, is entered to signify the possible desire to use a table or column name. A list of completion candidates will pop up if the Automatically list completion suggestions when editing queries box in Tools - Options - Miscellaneous has been checked.

 

Pressing the keyboard down arrow moves the focus to the list. Pressing Enter, tab, or typing a bracket character inserts the selected suggestion into the query.

 

In addition, pressing CTRL-space after beginning a candidate word will have Manifold automatically complete the word if an unambiguous match is found in SQL key words.

 

These functions may be manually invoked by use the SQL autocompletion commands in the Edit - Advanced menu of commands available when a query is edited:

 

Complete Word

Appears for queries. Complete the word automatically if an unambiguous choice is possible. If no unambiguous choice is possible, pop up a list of choices (the same as Complete Word with List). Usually invoked with the standard Microsoft CTRL-space shortcut.

Complete Word with List

Appears for queries. Pop up a list of possible completions. Not usually used, since checking (the default) the Automatically list completion suggestions when editing queries box in Tools - Options - Miscellaneous causes the list to pop up automatically.

 

We can invoke the above manually with a CTRL-space and CTRL-J keyboard shortcuts.

 

To choose an autocompletion item presented to us we can double-click with the mouse or press the tab key if a choice is unambiguous. Manifold provides different pathways to autocompletion so that we can select whichever method is the fastest and most convenient at the time.

 

Example

 

Let's write a simple query using the Mexico sample drawing and table. This example assumes the Automatically list completion suggestions when editing queries box in Tools - Options - Miscellaneous is checked (the default setting). The query we will write is a single line of SQL:

 

SELECT [Place_name] FROM [Mexico Table] WHERE [POBL_1990] > 1000000;

 

Autocompletion will help us write the above query without the tedium of having to manually write out all the column and table names manually. Autocompletion also spares us much trouble caused by typographic errors that often creep into manually-entered text.

 

We begin by creating a new query and then double-clicking the new query open in a query window.

 

images\eg_autoquery_01.gif

 

We enter the character S (beginning the first character in the Select statement) and immediately the system pops open a menu of choices for autocompletion, scrolled down to the items that begin with S.

 

images\eg_autoquery_02.gif

 

One way to autocomplete the Select statement is to double click the SELECT entry with the mouse.

 

images\eg_autoquery_03.gif

 

This will immediately add the remaining characters in the SELECT statement to the query. Let us continue the query to specify what field we want to select.

 

images\eg_autoquery_04.gif

 

We do this by entering the [ left bracket character. The system immediately pops open a list of table names and column names available in the project. We double click the Place_name table to have it autocompleted.

 

images\eg_autoquery_05.gif

 

The system automatically adds characters to complete the name of the column. About now the novice SQL programmer begins to realize that autocompletion is one of those wonderful things in life that once tasted cannot be done without.

 

There is a potentially faster way of invoking autocompletion than double-clicking with the mouse, as will be seen as we continue the query by building a FROM clause.

 

images\eg_autoquery_06a.gif

 

As we enter the F character to begin the FROM clause, the system pops up a menu of options that begin with F. Note that none of them is yet highlighted.

 

images\eg_autoquery_06.gif

 

As we continue and enter the R character the system highlights FROM since that is the only match that begins with FR. At this point we can hit the tab character (a typical Windows shortcut for accepting an autocompletion choice)...

 

images\eg_autoquery_07.gif

 

...and the system will complete the FROM statement for us.

 

It is true that entering a sequence of an F character and an R character followed by the tab character is not much of a time saver for an accomplished touch-typist who could just as rapidly bang out FROM using all four characters; however, in the case of more complex constructions, such as long field names, using autocompletion can be a great time saver as well as a way of guarding against typographic errors.

 

We can see the above effect by continuing the example.

 

images\eg_autoquery_08.gif

 

If we begin entering a column name by entering [M the system will jump to those columns that begin with M. We could double-click onto a column to choose it for autocompletion, or we could continue entering characters to get a highlighted match to an unambiguous autocompletion choice.

 

images\eg_autoquery_09.gif

 

When we enter [Mexico T we get a highlighted match to the Mexico Table column, at which point we can press the tab key to autocomplete it.

 

images\eg_autoquery_10.gif

 

Again, it's true that an accomplished touch-typist could enter the above text almost as rapidly as autocompleting it; however, by using autocompletion we guarantee the field name does not contain a typographic error because if it did a highlighted match for autocompletion would not have occurred. Saving just a single typographic error in a query can save us as much time in debugging as entering hundreds of characters.

 

Another advantage to autocompletion is consistent use of case. Because autocompletion uses the same capitalization style regardless of whether initial characters were uppercase or lowercase, autocompletion is also a handy way of adhering to a consistent capitalization scheme without having to pay too much attention to using the SHIFT key.

 

images\eg_autoquery_11.gif

 

For example, suppose we continue the query by entering Wher and then press the tab key for an autocompletion.

 

images\eg_autoquery_12.gif

 

The system will enter a WHERE clause using all uppercase letters.

 

images\eg_autoquery_13.gif

 

We can finish the query by manually entering the remaining text.

 

Options

 

As mentioned in the beginning of the example, the above example assumes the Automatically list completion suggestions when editing queries box in Tools - Options - Miscellaneous is checked (the default setting). This option causes a list of choices to automatically appear whenever a SQL key word is begun or a [ left bracket indicates the beginning of a table name or column name.

 

It is not to everyone's taste to have a list of autocompletion choices appear automatically. If we don't like the pop up lists, we can disable them by unchecking the option in Tools - Options. With the option turned off at any time we can cause a list to appear (if one is possible) by pressing CTRL-J.

 

For example, if the option is turned off and we enter...

 

SELECT [Place_name]

 

...and we would like to be reminded of what might come next we can always press CTRL-J and a list of SQL statements, clauses and operators will appear.

 

Note that regardless of whether the above option is turned on or off at any time when beginning an SQL key word we can have it completed for us with a CTRL-space if we have entered enough characters to unambiguously select a possible completion choice.

 

See Also

 

Edit - Advanced

Queries

Simple Queries