Query Builder

<< New SQL Editor | IBExpert | Data Analysis >>

(1) Criteria
(2) Selection
(3) Grouping criteria
(4) Sorting

For those not yet competent in SQL, the Visual Query Builder is there to make life easier! It allows you to create and edit queries with multiple tables without previous knowledge of SQL, as well as prepare and execute queries, and view the results. This feature is unfortunately not included in the free IBExpert Personal Edition.

If you are new to Firebird/InterBase^Ž SQL, then please also refer to Firebird Development using IBExpert for a comprehensive introduction to SQL. The SQL Language Reference, the Firebird 2 Language Reference Update, Firebird 2.1 Language Reference Update and the Firebird 2.5 Language Reference Update
provide comprehensive references to all Firebird/InterBase^Ž SQL keywords, syntax and parameters. A full list of Firebird error codes can be found in the Firebird 2.1 error codes documentation.

The IBExpert Query Builder is started using the menu item Tools / Query Builder. It can also be started directly from the SQL Editor using [Ctrl + Shift + Alt + B] or the

icon.

A query can be built by simply moving the database objects (e.g. by dragging the desired table) from the right panel over to the left editing area. Objects may also be dragged and dropped from the DB Explorer and SQL Assistant into the code editor window. Or you can alternatively use the Select database object form.

When an object node(s) is dragged from the DB Explorer or SQL Assistant, IBExpert will offer various versions of text to be inserted into the code editor. It is also possible to customize the highlighting of variables. Use the IBExpert Options menu item, Editor Options / Colors to choose color and font style for variables.

The required fields can be selected using the mouse. By clicking on the box to the left of the table name, all fields are automatically highlighted. Tables can be linked, e.g. by key relationships, joins etc., using the mouse (click on the desired field in the first table and drag it across to the desired field in the second table). This creates a JOIN.

By double-clicking on the lines connecting two tables the option Link Properties appears, and the developer can specify from which table all of the information should be fetched (see JOIN for more information about joins).

Alternatively, a small context-sensitive menu appears when right-clicking on a line, offering not only the above mentioned option, but also the option to insert or delete point or to delete the link.

Check every field which is important for the result set and use [F9] or the respective icon to execute and view the results. For information regarding the Results page, please refer to SQL Editor / Results.

Conditions can be specified in the lower part of the Query Builder dialog using the options listed on the following pages:

(1) Criteria

A simple condition string contains three fields: an argument, a condition and a second argument - if required for the condition. By clicking on the word ALL of All of following are met, it is possible to change this condition to ALL, ANY, NONE, or NOT ALL. By clicking on the ring to the left of All of following are met, it is possible to add a condition. Using [Shift + Enter] or right-clicking and selecting Insert Field, fields can be selected from the specified tables:

Alternatively a value can be manually entered. By clicking on the '=' sign a list of available conditions appears:

If you wish to view the SQL statement at any time, simply switch to the Edit page.

(2) Selection

By double-clicking on a field in the builder area, the field source is automatically inserted. An output field name (i.e. the field name that appears above the column on the result list) may be specified by double-clicking (or using the [Enter] key) on the field name listed in the Name of output field column.

On the Selection page an aggregate (SUM, MIN, MAX, AVG and COUNT) can be specified for individual fields if wished. For example, if a minimum or maximum order value needs to be determined; or the number of unpaid invoices. The Aggregate pull-down list can be viewed by double-clicking or using the [Enter] key and downward arrow key, and selecting an option.

The Include only unique records checkbox eliminates duplicate records when checked.

(3) Grouping criteria

Again ALL, ANY, NONE, or NOT ALL of the specified conditions can be met. Here combined criteria can be determined, i.e. aggregate and comparative selection criteria.

(4) Sorting

Here the results can be sorted in ascending or descending order by one or more fields in order of priority. Simply move the field(s) to be used as the sorting criteria from the list on the left to the right-hand window, by selecting and clicking the Add button or using drag 'n' drop. Use the A.Z -Z.A button to specify ascending or descending order, and use the Up and Down buttons (when sorting by multiple fields) to specify sorting priority (i.e. which field should be sorted first).

When the query preparation is complete, it can be prepared [Ctrl + F9] and analyzed, and/or executed [F9] before finally committing.

In addition to the main Builder window, there is also an Edit page, displaying the query resulting from the drag 'n' drop and condition specification in the main builder window, as SQL text. This is, in effect, the same as the SQL Editor's main Edit window. It can be edited directly, if wished, and all changes are displayed on the other Query Builder pages.

A Results page appears following query execution, displaying the returned data resulting from the query. A Filter panel can also be blended into the dialog to aid data navigation and allow further filtering. For more information, please refer to the SQL Editor's Results page and Filter Panel.

The Plan Analyzer is displayed following query execution and shows how Firebird/InterBase^Ž approaches a query, e.g. with SORTS, JOINS etc, which tables and indices are used. The information is shown in the lower panel in a tree structure with statistics. Please refer to Plan Analyzer for further information.

The Performance Analysis displays information showing much effort was required by Firebird/InterBase^Ž to carry out an executed query or procedure. For more information please refer to the SQL Editor's Performance Analysis.

The Visual Query Builder is ideal for the beginner, although somewhat limited for more advanced work; complex queries should be performed in the SQL Editor or Script Executive.