 |
Online Help System
|
 |
Back to Brilliant Database Web-Site |
Write to Support |
Help Index
Queries
Introduction
A query is a user's request for information to be retrieved from a database. A query consists of the following parts:
- Specification of database software records source for analyzing rules
conformity;
- Set of rules, according to which the database software records/queries will be
checked for consistency;
- Specification of how the request results should be displayed.
As an example, let us examine, using databases, what queries contain information about books. Assume that we have the following information about every book: Title, Author, Number of pages, Publication date, and Year of purchase. In this case, a query would be a formal description of such expressions as follows:
Example 1. I want to get from the database a list of books written in 1995, sorted by name. Where:
- "from the database" is a source;
- "written in 1995" is a rule;
- "a list of books, sorted by name" is a specification of how the results should be displayed.
Example 2. I want to get from the My Favorite Books folder a list of the books published in the year of creation, the number of pages of which is greater than 200, sorted by the year of publication, where:
- "from the "My Favorite Books" folder" is a source;
- "published in the year of creation, the number of pages of which is greater than 200"
is a ruleset;
- "a list of the books sorted by the year of publication" is a specification of how the results should be displayed.
Further we will use these examples in the queries editor description.
Query Editor
General
To open the Query Editor window, select the Queries > Add/Modify/Delete Query menu item in the main menu:
The Query Editor window consists of two panes:
- The left pane (1) provides the list of queries available in the database, and the buttons to add and delete a query. The queries are grouped by types of database software records, the current database software record is marked with the blue color;
- In the right pane (2) you can edit the current query.
Creating and Deleting Queries
- To create a new query, click the New Query button in the left upper
part of the window (1) and select the database software record type, with which the new
query will work. By default, a query returning all database software records of the selected
type is created. To modify a query, use the right pane of
the window (2);
- To delete the current query, click the Delete Query button in the
left upper part of the window
(1);
Modifying the Current Query
The right part of the window (2) is used to edit the selected query. It contains 3 tabs: Query, Appearance and Post-Query Actions.
Query Tab
The contents of this tab can be divided into three parts (see the Introduction section):
- Part 1 - the database software records source (i.e., the folder, from which the database software records should be taken);
- Part 2 - set of rules, according to which the database software records and the queries will be checked for consistency;
- Part 3 - in this part you can specify fields the database software records should be sorted by. Using unique field you can specify the field that has to be unique in the query results. For example (for a database of books), to get a list of all genres, indicate genre as distinct. Now the query will return only one book for each genre, and we will get a list of all genres. Otherwise the resulting list will contain all books and, consequently, duplicating genres.
When creating a new query, the set of rules is empty. This means that all database software records from the specified folder (and its subfolders, if the corresponding check box is selected) will be returned.
At the left of the window there are 3 buttons that can be used to create, delete or edit rules. If a ruleset contains more than one rule, you should use the And/Or button to specify whether a database software record to be returned should satisfy all the rules (and), or one of the rules (or). For example:
- Get a list of the books published in 2000, and those the number of pages of which is greater than 100;
- Get a list of the books, which were either published in 2000, or the number of pages of which is greater than 100;
If you want to create complex rules, like (Rule1 AND Rule2) OR (Rule3 AND Rule4), click the dropdown menu of the New Rules... button and select the New RuleSet menu item:
Clicking either Edit Rule or New Rule opens the window for rule editing,
where you can set up the rule.
Appearance Tab
- Configure Table Style - click this button to define how query results are to be output (fields, colors, grouping,
etc.) Learn more...
- Show query results in - this option defines where query results are to be displayed:
- Special Window on the left - query results are to be displayed in a
special window to the left, under the folder list (by default).
- Replace Record list window - query results will be displayed where
the database software record list normally appears; at the same time, the database software record list in the
current folder will be hidden. Selecting this option allows creating the
Toolbar that will appear above the results. For
example, there you can place buttons for printing results or for mass editing
of selected database software records:
When the check box is cleared, the window with query results will not be displayed. - Don't show the query in the "Queries" menu - select this check box to hide the option for execution of the query in the queries menu. To learn more about other ways to execute a query, see the Running Queries section.
- Don't show the "no database software records were found" message - select this check box if you do not want the program to show the message in case no database software records have been found.
Post-Query Actions Tab
This tab contains several check boxes that define operations to be performed after the request is accomplished.
- Restart query... If none of the database software records meets query parameters, the user will be offered to repeat the request with different parameters. This option is performed only if any parameters in the ruleset are specified as "variable" (see above).
- Automatically select the first database software record from query results. After a request is accomplished, the first database software record in the query results will be selected.
- Print query results. After a request is accomplished, you will be offered to
print out its results.
- Export query results using the custom export. After a request is accomplished, you will be offered to export its results
by using Custom Export Templates (generate html
web-page, export to txt, insert data to MS Word document, send result by
e-mail, etc). Learn more about the custom export....
- Execute script. After the query has been executed, the
script set in Script Designer will be executed.
Running Queries
The easiest way to execute a query is to select it from the upper part of the Queries menu:
In case the required query is not displayed in the Queries menu (i.e., for this query in the Additional tab the check box Don't
show the query in the "Queries" menu is selected), it can still be executed by creating a corresponding toolbar
button or a form button using
actions.
Records contained in the report will be moved into the special Query
Results Window (by default):
To view a database software record from the query, double-click it. To print the query, select Print Query Results from the reports menu.
To perform an operation with a query result, copy the query results to a variable using a script and perform the Run script for database software recordset action. To learn more, see the User Functions - Changing Several Records section.
|
