My Autocomplete

My AutoComplete - Werysoft.com

One of the great features of QweryBuilder is the intellisense found in the SQL Editor window. It assists you as you type your ad-hoc queries. You can display a list of tables by typing in the database owner (e.g. dbo) followed by a period (.). Or you can simply press Ctrl+Space. To bring up a list of columns for a table, type the name of the table followed by a period, or type the table alias you entered in the FROM clause then enter a period.

E.g.
SELECT A. <-- Autocomplete window will display here
FROM MyTable A

SELECT *
FROM dbo. <-- Autocomplete window will display here

Another feature in QweryBuilder, known as My Autocomplete, allows you to get quick access to data within your database via the intellisense window. The My Autocomplete feature (Tools/My Autocomplete) can display a list of data retrieved from the database or data that has been manually typed in.

So why would a feature like this be handy? In a support role you are constantly querying the database looking for information based on “key” data. For example; a popular query may be to find all employee’s by department. Your database will contain an Employee table and a Department table. The Employee Table will contain a foreign key back to the Department table for the column DeptId.  If you need to find all employees in the HR department you will have to first look up the key for the HR department. Once you have this key you will then use it to query the Employee table. So really what you’ve done is create a SELECT statement, executed it, copied the results from the results window or memorized it, created another query for the Employee’s table then entered your department.

To do this example with My Autocomplete you would; open the My Autocomplete window and enter a new item by clicking the “new” button. Provide a name (e.g. dept); this name will be used in the query window so keep it short. From the type drop down, select Command.  Enter the following syntax:

SELECT DeptId, DeptName
FROM Department

The column order in the above SELECT statement is important. You want the key column first (the column value that will be inserted into the editor) then the descriptor column second. In your SQL Editor window you can then type the following:

SELECT * FROM EMPLOYEE A
WHERE A.DeptId = dept. <-- dept is the name we specified in the My Autocomplete window.

After entering the period you will see that the word dept is removed (it’s not valid for our query) and a list of data is retrieved and displayed (the results of the query entered in the My Autocomplete window). Find your department and select it. Once you have your department selected you can click enter or tab to insert the key value into your editor window.

The Werysoft Team