Visual Query Builder

Visual Query Builder is provided for building the queries visually. It allows you to create and edit queries without knowledge of SQL, prepare and execute queries, and view the results of the execution. This module is based on the EMS QueryBuilder Component Suite (see http://www.ems-hitech.com/querybuilder/ for details).

To call the Visual Query Builder select the Tools | Show Query Builder or Tools | New Query Builder menu item.

Building Query

The main area of the Query Builder is the Builder area. Here you can build your query by placing the database tables on the area, selecting the required data and setting links between objects.

Adding tables and fields to the query
To add a table to the query, choose the required object in the table list at the right, then double-click it or drag it to the Builder area. The selected table will appear on the Builder area with the list of its fields. To include a table field in the query, click at the left of the field name in the list or double-click it to set the blue icon next to the field name. To include all the fields, set a flag at the left of the object alias. To remove the fields from the query, uncheck the fields; to remove the object, close it by clicking the button 'X' next to the object alias. To edit the object alias, double-click the alias.

Associating tables by fields
To associate database objects by two fields, just drag one field from the object list to another. This will set a link between these objects by the selected fields. When you drop a field, a line will appear between the linked fields. You can view and edit the properties of object association. To do that, aim the cursor at the link line. A hint, containing the association condition, will appear. To edit the properties, double click the line or right-click and choose Properties item from the popup menu. A dialog window will appear, where you can change the association condition by choosing it from the list (=, >, <, >=, <=, <>). Also you can check or uncheck Include all from <object_name> option for each object, included into the association. Click 'OK' to confirm the changes you made. To drop a link between objects, right-click on the link line and choose Delete Link item from the popup menu. To delete all the links of some object, click button '-' next to the object alias. To insert a point to the link line, right-click on the line and choose Insert Point item from the popup menu. A new point will appear, using which you can move the link line. It doesn't cause any changes to the query but makes the diagram performing more obvious and the visual building handler.

Setting Query Properties

To set other query properties, use tabs at the lower part of the window.

Criterions
On this page you can set the selection conditions. To add a condition, click button at the left and select 'Add condition' in the popup menu. Edit the condition by clicking its parts and setting their values. Clicking the button at the left of the condition string activates the popup menu, which allows you to add a new condition of the same enclosure level, add a new enclosure level, delete the current condition, open or close the condition, if it is composite. A simple condition string contains three fields: an argument, a condition and a second argument (if required for the condition). Clicking each field allows you to set its value. Clicking the argument field allows editing it as a text field. You can set a table name or a definite value in this field. Right-clicking the field in the edit mode activates the popup menu, which contains the Insert Field function (also called by Shift+Enter). This function allows you to choose a field from the list of all the table fields, available in the query. Clicking the condition field activates the popup menu, where you choose the condition you need. The way of proceeding the condition is set in the upper string of the area (All, Any, None, or Not all of the following are met). Click the underlined word to change it.

Selection
This page displays the output fields of the query. It allows you to edit the names of the query output fields, set their displaying order and set the aggregate functions (SUM, MIN, MAX, AVG, and COUNT) for each field. To remove the field from the list, right-click the field row and choose 'Delete current row' from the popup menu. To change the input query field, double click it and then type the field name on the keyboard or choose it from the drop-down list.

To change the output query field name, double click it and type the field name on the keyboard.
To set the aggregate function for the field, double click the field row in the Aggregate column and then type the function name on the keyboard or choose it from the drop-down list.

   Include only unique records
If you check this option the repeated records are not included into the query result.

Grouping Criterions
On this page you can set the conditions for grouping the query records. They are set in the same way as the selection conditions (see above). These conditions will be included into the HAVING statement of the current query.

Sorting
Set the way of sorting the query records on this page. The field list at the left represents all the output query fields; the list at the right contains fields, by which the query records will be sorted. To move the field from one list to another, drag the selected field or use buttons 'Add' and 'Remove'. To change the sorting order, select a field in the right list and move it using buttons 'Up' and 'Down'. To change the sorting direction, select a filed in the right list and switch the direction (Ascending, Descending) using button 'A..Z'/'Z..A'.

When the query is ready, execute the query by clicking button Execute on the toolbar. If there are any errors in building the query text, you will get the appropriate message, describing the error. If everything is correct, the Results page appears.

Query Builder Tabs

Results
This page becomes available after the query was executed. It displays the result data, returned by the query, as a grid, a form or a printable report (see Data Frame for details). The popup menu of this tab and the buttons on the toolbar allow you to Export Data , Export Data as INSERT . In procedures returning BLOB fields you can also call the BLOB Editor to view the content of the BLOB fields.

The status bar displays the number of records, acquired while executing the query.

Edit
On this page the query text is automatically generated while you build query. You can edit this text according to the rules of SQL, and all the changes will be displayed on the other pages of the Query Builder.

Query Builder Toolbar

The buttons on the Query Builder toolbar allow you to perform the following operations over the query.

Button	Action
Databases	Drops down with a list of registered databases, allowing you to select the database for the query.
Load	Loads the query text from an *.sql file.
Save	Saves the query text to an *.sql file.
Delete Current Query	Clears the current query.
Execute	Executes the query.
Start Transaction	Starts a transaction. Transaction starts automatically only on editing query data to prevent unnecessary table locking.
Commit Transaction	Commits the current transaction.
Rollback Transaction	Rollbacks the current transaction.
Close Result	Closes the Results page with the query result data.
Export Data ('Results' tab only)	Activates the Export Data Dialog, which allows you to export the result query data to file.
Export Data as INSERT Statements ('Results' tab only)	Activates the Export Data As Insert Dialog, which allows you to export the result query data to the SQL script as INSERT statements.
View	Allows you to show/hide the table list at the right of the window.
Default Size and Position	Brings the window to its default size and position (restricted by the main window and DB Explorer).

See also:

SQL Editor