Query Builder

When you invoke the builder to modify an existing query definition, the Q+E Query Builder window is displayed. This screen has two menus at the top, an icon bar across the top, and a status bar across the bottom.

This window displays the definition of the current query, as shown in each of the boxes. The following list defines these boxes:

Tables	Lists the database table(s) from which records will be retrieved

Fields	Lists the field(s) of the database table to be displayed in columns

Sort order	Lists sort orders for the records

Conditions	Lists conditions used to specify which records are to be displayed

Three additional boxes can be displayed--a Table Joins box, a Group by box, and a Having box. A Table Joins box is displayed when you have defined a join among database tables. The other two boxes are displayed when you have defined a Group By clause.

The Query menu contains commands that enable you to define and modify your query statement. These commands correspond to the nine icons on the icon bar.

Tip

You can click any box to move to its associated dialog box. For example, if you click Fields, the Field dialog box is displayed.


Tables Command (Query Builder)
Tables Command
Query Builder

Use this command to select the database tables from which fields will be retrieved.

Shortcuts

Icon:

Dialog Box Options

Source/Directories/Drives

When specifying the file name of a database table, make sure the correct Source, drive, and directory path are specified so that all valid database file names are listed in the File Name list.

File Name

Select the database tables from which fields will be retrieved from this list. If you select more than one database table, the next dialog box displayed is Table Joins where you can join the tables you selected.

-> Button

Click this arrow when you have selected the file name to place the file name in the Selected Tables list.

Expression

Type an expression for the selected table. For example, you can add a table alias for the database table (EMP E), E is the table alias. A table alias is a name used to refer to this table in the rest of the Select statement. You can also pass database options for the From clause of the Select statement. These options are database dependent. Refer to the documentation for your database system.

For example, for dBASE you can pass a statement that determines if deleted records are to be retrieved--EMP (RECORDS=DELETED).


Join Command (Query Builder)
Joins Command (Query Menu)
Query Builder

Use this command to join multiple database files. You can join database files only if the files have at least one column in common and the files are from the same database system. You join the data in one database file (the child) to the data in another file (the parent) and the records are combined and displayed in one window.

For example, if your company has a department database and an employee database, you may want to join employees (the child) to departments (the parent) to display employee records grouped within the department in which they work. In this example, both the database files have a department ID field.

Shortcuts

Icon:

Dialog Box Options

In the left drop-down list, select the parent database file. Then, select the field of that file that has common values in the child database file.

In the right drop-down list, select the child database file. Then, select the field of that file that has common values in the parent database file.

In the center (operator) drop-down list, select the operator for this join. They are

=	Equal to
<>	Not equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
*=	Outer join (parent to child, for dBASE)
=*	Outer join (child to parent, for dBASE)

Note: The operator for outer joins is specific to the database system you are using. Some database systems do not support outer joins.

Insert Button

Click this button to add this join statement to the Table Joins box.

Replace Button

Click this button to replace the current join statement with the one you just defined. To replace an existing join statement, highlight it, define the new statement, and click the Replace button.


Fields Command (Query Builder)
Fields Command (Query Menu)
Query Builder

Use this command to select the fields of the database table that you want retrieved and displayed in a window. You can also define field expressions. A field expression typically contains a database field, an operator, and a constant (for example, SALARY * 1.05).

Shortcuts

Icon:

Tip

You can rearrange the fields in the Select List so that they are displayed in different columns in the window. To do this, click the field you want to move in the Select List, press the CTRL key (the cursor changes shape), and drag the field to its new location in the Select List.

Dialog Box Options

Fields

Select a field of the database table and click the right arrow (->) to place the field in the Select List.

-> Button

Click this arrow to select a highlighted field from the Fields list and place it in the Select List.

->> Button

Click this arrow to select all of the fields from the Fields list and place them in the Select List.

Expression

Type a field expression in this box. This drop-down list contains the database table fields. When you are entering a field expression, you can click the Add Function button to use a function in the expression.

Insert Button

Click this button to add the field expression to the Select List.

Replace Button

Click this button to replace a field or field expression in the Select List with the field expression you just defined. To replace an existing field or field expression, highlight it, define the new expression, and click the Replace button.

Only return distinct records

Select this option to display only records with unique values, not duplicate values.

Delete Button

Click this button to delete a field or field expression from the Select List. You must first highlight the field.


Sort Command (Query Builder)
Sort Command (Query Menu)
Query Builder

Use this command to specify the fields by which you want the records sorted.

Shortcuts

Icon:

Dialog Box Options

Fields

Select a sort field and click the right arrow (->) to place the field in the Select List.

-> Button

Click this arrow to select a highlighted field from the Fields list and place it in the Select List.

->> Button

Click this arrow to select all of the fields from the Fields list and place them in the Select List.

Ascending Button

Click this button for the highlighted field in the Select List to be sorted in ascending order (AAA to ZZZ). By default, all fields placed in the Select List are sorted in ascending order unless otherwise specified.

Descending Button

Click this button for the highlighted field in the Select List to be sorted in descending order (ZZZ to AAA).

Expression

Type a field expression in this box. This drop-down list contains the database table fields. When you are entering a field expression, you can click the Add Function button to use a function in the expression.

Insert Button

Click this button to add the field expression to the Select List.

Replace Button

Click this button to replace a field or field expression in the Select List with the field expression you just defined. To replace a field or field expression, highlight it, define the new expression, and click the Replace button.

Delete Button

Click this button to delete the highlighted field or field expression from the Select List.


Conditions Command (Query Builder)
Conditions Command (Query Menu)
Query Builder

Use this command to add  query conditions that records must meet to be retrieved from the database table or file. An example condition is to display only the records that have a value in the SALARY column of greater than $25,000 (SALARY > 25000).

Shortcuts

Icon:

Dialog Box Options

Connector

The connectors are NOT, AND, OR, NOT AND, and NOT OR.

Field Expression

Type a field expression in this box. This drop-down list contains the database table fields. When you are entering a field expression, you can click the Add Function button to use a function in the expression.

Operator

Select an operator.

Value Expression

Type a value expression in this box. This drop-down list contains the database table fields. When you are entering a value expression, you can click the Add Function button to use a function in the expression.

To define a dynamic query, do not enter a value expression; instead, click the Add Parameter button to define query parameter.

Insert Button

Click this button to add the condition to the Conditions box.

Replace Button

Click this button to replace a condition in the Conditions box with the condition you just defined. To replace an existing condition, highlight it, define the new condition, and click the Replace button.

Delete Button

Click this button to delete the highlighted condition from the Conditions box.

Add () Button

Click this button group conditions by surrounding them with parentheses. You must first highlight the conditions. Conditions grouped by parentheses are considered first.

For example, SALARY > 40000 OR HIRE_DATE > {01/30/89} AND DEPT = 'D101' retrieves employees in department D101 hired after Jan 30, 1989, as well as every employee making more than $40,000, no matter what department and hire date.

However, (SALARY > 40000 OR HIRE_DATE > {01/30/89}) AND DEPT = 'D101' retrieves employees in department D101 that either make more than $40,000 or were hired after Jan 30, 1989.

Del () Button

Click this button to delete the parentheses surrounding a group of conditions. You must first highlight the conditions.

Edit Parameters

Click this button to edit a query parameter.


Connectors (Query Builder)

The NOT connector applies to the first condition you define. The other connectors apply only if one condition has already been defined.

Option	Definition

NOT	Negates the condition or conditions if you have them grouped using parentheses.

AND	Records must meet all previous conditions as well as the new condition being added.

OR	Records must meet either all previous conditions or the new condition.

NOT AND	Records must meet all previous conditions and not the new condition being added.

NOT OR	Records must meet either all previous conditions or not the new condition.


Dynamic Query

You can define a query that can generate multiple reports according to values you enter in a dialog box each time you open a query file. For example, you could have a Sales database file that contains sales dollars by month. By defining a dynamic query, you can generate a report for any month that you need at the time simply by entering the desired month--one query, multiple reports.

Dynamic queries let you enter new values each time the query is executed.

To define a dynamic query, you must create query parameters in the Query Builder.


AND and OR Connectors (Query Builder)
AND and OR Connectors

These connectors are applicable only if one conditions has already been defined.

Option	Definition

AND	Records must meet all previous conditions as well as the new condition being added.

OR	Records must meet all previous conditions or the new condition.


Query Builder Operators

The following list defines the comparison operators that can be used in the Conditions dialog box of the Query Builder.

Operator	Definition

= (equal)	Must match specified value.

<> (not equal)	Must not match specified value.

> (greater than)	Must be greater than specified value.

>= (greater or equal)	Must be greater than or equal to specified value.

< (less than)	Must be less than specified value.

<= (less or equal)	Must be less than or equal to specified value.

In	Must be a member of a set of specified values. Multiple values separated by a comma are entered in the Value box of the Add Condition dialog box. Therefore, you must not use a comma within a value. For example, The Query Builder interprets 300, 20,000 as the set 300, 20, and 000.

Not In	Must not be a member of a set of specified values.

Multiple values separated by a comma are entered in the Value box of the Add Condition dialog box.

Between	Must be within the range (lower and upper bound) specified. Two values are entered--the lower bound and the upper bound--separated by the word "and" (for example, 200 and 500).

Not Between	Must not be within the range (lower and upper bound) specified.

Two values are entered--the lower bound and the upper bound. The values are entered with the word "and" separating them (for example, 'Adams' and 'Young').

Like	See pattern matching

Not Like	See pattern matching.

Is	The field expression is compared against a null value expression (field expression is null).

Is Not	The field expression is compared against a null value expression (field expression is not null).


Grouping Command (Query Builder)
Group By Dialog Box
Query Builder

Use this dialog box to define a report that displays subtotals and group information but does not display the individual records used to compute the subtotals. You can display this dialog box using the Query Grouping command in the Query Builder.

Shortcuts

Icon:  from Query Builder

Dialog Box Options

Fields

Select the field in the database table by which you want records grouped. Then click the right arrow (->) button to place the field in the Groupings list. Or define an expression.

Expression

If you did not select a database field by which the records are to be grouped, define a field expression in this box by which the records will be grouped. This drop-down list contains the database table fields. When you are entering a field expression, you can click the Add Function button to use a function in the expression.

For example, you could define an expression that would group the records by employees that make more than $20,000 a year and those that don't (SALARY > $20,000).

-> Button

Click this button to add a highlighted field from the Fields list to the Groupings list box.

->> Button

Click this button add all of the fields from the Fields list to the Groupings list box.

Insert Button

Click this button to add the field or field expression to the Groupings list box.

Replace Button

Click this button to replace a field or field expression in the Groupings box with the field or field expression you just defined. To replace an existing field, you must first highlight it, define the new one, and then click the Replace button.

Delete Button

Click this button to delete a highlighted field or field expression from the Groupings box.


Having Command (Query Builder)
Having Command (Query Menu)
Query Builder

Use this command to specify additional conditions for groups of records (a Having clause). For example, retrieve only the departments that have an average salary of more than $20,000. You can specify a Having clause only if you have already defined a Group By clause.

Shortcuts

Icon:

Dialog Box Options

Connector

Applicable only if a Having condition is defined. The connectors are AND and OR.

Field Expression

Type a field expression in this box. This drop-down list contains the database table fields. When you are entering a field expression, you can click the Add Function button to use a function in the expression.

Operator

Select an operator.

Value Expression

Type a value expression in this box. This drop-down list contains the database table fields. When you are entering a value expression, you can click the Add Function button to use a function in the expression.

To define a dynamic query, do not enter a value expression; instead, click the Add Parameter button to define a query parameter.

Insert Button

Click this button to add the condition to the Conditions box.

Replace Button

Click this button to replace a condition in the Conditions box with the condition you just defined. To replace a condition, highlight it, define the new condition, and click the Replace button.

Delete Button

Click this button to delete a highlighted condition from the Conditions box.

Add () Button

Click this button group conditions by surrounding them with parentheses. You must first highlight the conditions. Conditions grouped by parentheses are considered first.

For example, SALARY > 40000 OR HIRE_DATE > {01/30/89} AND DEPT = 'D101' retrieves employees in department D101 hired after Jan 30, 1989, as well as every employee making more than $40,000, no matter what department and hire date.

However, (SALARY > 40000 OR HIRE_DATE > {01/30/89}) AND DEPT = 'D101' retrieves employees in department D101 that either make more than $40,000 or were hired after Jan 30, 1989.

Del () Button

Click this button to delete the parentheses surrounding a group of conditions. You must first highlight the conditions.

Edit Parameters

Click this button to edit a query parameter.


Edit Query Text Command(Query Builder)
Edit Query Text Command (Query Menu)
Query Builder

Use this command to display the SQL Select statement that corresponds to the current query definition. You can edit the statement from this dialog box. In this dialog box, you can use the Cut, Copy, Paste, Find, Find Next, and Replace commands.

Shortcuts

Icon:


Validity Check Command (Query Builder)
Validity Check Command (Query Menu)
Query Builder

Use this command to check the syntax of a SQL Select statement that you have modified using the Edit Query Text command. The Query Builder reports any errors.

Shortcuts

Icon:


Use Database to Validate Command (Query Builder)
Use Database to Validate Command (Preferences Menu)
Query Builder

Set this command option to use the database system to validate the query conditions as you build them. If not set, the Query Builder does not use the database system to check for errors, so you may construct conditions that have errors when you execute the query. A check mark indicates that this command option is set.


Large Icons Command (Query Builder)
Large Icons Command (Preferences Menu)
Query Builder

Set this command option to display large icons on the icon bar. If not set, small icons are displayed on the Query Builder icon bar. A check mark indicates that this command option is set.


Sample Values from Database Command (Query Builder)
Sample Values from Database Command (Preferences Menu) Query Builder

Set this command option to display database values in the Value Expression drop-down list of the Conditions dialog box when you are defining field conditions. A check mark indicates that this command option is set.


Query Parameters (Query Builder)
Query Parameters
Query Builder

This dialog box lets you define a query parameter to create a dynamic query.

Dialog Box Options

Name

Name of the query parameter. Valid values are alphanumeric character strings of up to 78 characters.

Data Type

Select the data type of the value the user will type for records to be retrieved.

Label

Type a label that will appear in the dialog box when the user is prompted for a value.

Default Value

Type a value on which the query is performed if the user does not enter a value when prompted.

Format String

The format string you define must be applicable to the data type of the parameter, as selected in the Data Type list. The value entered by the user is formatted according to this format string.


Cut Command (Query Builder
Cut Command (Edit Menu)
Query Builder

Use this command to delete the highlighted text and place it on the clipboard.

Shortcuts

Icon:

Keys: CTRL+X

See Also

Copy, Paste


Copy Command (Query Builder)
Copy Command (Edit Menu)
Query Builder

Use this command to copy highlighted text to the clipboard.

Shortcuts

Icon:

Keys: CTRL+C

See Also

Cut, Paste


Paste Command (Query Builder)
Paste Command (Edit Menu)
Query Builder

Use this command to copy the text from the clipboard in front of the cursor or to replace any highlighted text.

Shortcuts

Icon:

Keys: CTRL+V

See Also

Copy, Cut


Find Command (Query Builder)
Find Command (Edit Menu)
Query Builder

Use this command to search and move the cursor to a value.

Dialog Box Options

Find What

Type the value for which you want to search.

Match whole word only

Select this option to match only the whole word. For example, if the search string is cut and this option is set, the Query Builder finds only instances of the word cut, not words with the cut string (as in execute).

Use Pattern Matching

Select this option to use pattern matching

See Also

Find Next


Find Next Command (Query Builder)
Find Next Command (Edit Menu)
Query Builder

Use this command to find the next occurrence of the search string you specified using the Find command.


Replace Command (Query Builder)
Replace Command (Edit Menu)
Query Builder

Use this command to search for a specified value and replace it with another value that you specify.

Dialog Box Options

Find What

Type the value you want to search for.

Replace

Type the value you want to replace with.

Match whole word only

Select this option to match only the whole word. For example, if the search string is cut and this option is set, the Query Builder finds only instances of the word cut, not words with the cut string (as in execute).

Use Pattern Matching

Select this option to use pattern matching for the search.

Verify Button

Click this button to begin the search and make the replacement. If the Query Builder finds the search string, it asks you to verify that you want to replace it.

Replace All Button

Click this button to replace all instances of the search string with the replacement string, without verification.


Functions

The functions that you can select for a field expression are database-specific. Refer to the documentation for your database system for information on the functions you can use.

Each database supports the Average, Count, Maximum, Minimum, and Sum aggregate functions.


Pattern Matching

The Like and Not Like comparison operators let you use either the standard MS-DOS or SQL pattern-matching characters to select records. In the following examples, the MS-DOS characters are assumed. They are

*	To match zero or more characters

?	To match any single character

To change from MS-DOS to SQL pattern-matching characters, use the Data Fetch Options command from the Preferences menu. In the Data Fetch Options dialog box, click SQL (%,__) in the Like box.

Examples

If you enter	Query Builder matches

B*	names that start with the letter B

*N	names that end with the letter N

B*N*T	names that start with B, contain an N, and end with a T

?????	any five-character name

*A?	names whose next-to-last character is an A

Not Like is the opposite of Like. It is used to select all records that do not match the specified pattern.