skip to main content
OpenEdge Development: AppBuilder
Data-Access Objects : Defining and editing queries using Query Builder
 
Defining and editing queries using Query Builder
A central step in developing any database application is creating the query or queries to be used for retrieving records from the database. AppBuilder offers a structured query editor for your use. You can invoke Query Builder in a number of ways:
*Define a new SDO — AppBuilder then starts the SmartDataObject Wizard, and the wizard calls Query Builder on your behalf. SmartObjects are the most powerful way to use queries.
*Place a basic Data Browser (Browse) object in the workspace — AppBuilder starts Query Builder. The browse object has all the internal logic necessary to use the query, though only in a self‑contained way. You manually add any additional logic you require.
*Place a Query object in the workspace — AppBuilder then starts Query Builder. Such a query object is not connected to anything after you define it. You must manually add the code to make use of it.
*Choose the Query button from the property sheet of a frame object — AppBuilder then starts Query Builder. As is true of the query object, you must manually write the code that makes use of this embedded query, after you have finished defining it.
Note: You can construct or modify queries by hand, using AppBuilder’s Section Editor, but such queries cannot later be edited using Query Builder. If you wish to have all queries be maintainable in Query Builder, you would have to replace any such free‑form queries with their Query Builder‑generated logical equivalents.
Query Builder always starts in Table‑selection mode.
Table mode
In this mode, the Query Builder allows you to specify the tables you want in the query. When you modify a query from the property sheet of a frame or browse, the Query Builder also initializes to this mode. Figure 30 shows how the Query Builder appears in Table mode.
Figure 30: Query Builder in Table mode
To select a database table, select the table name in the Available Tables list and click Add; you can also double‑click on the name.
To remove a table from the Selected Tables & Joins list, select the table name and click Remove; you can also double‑click on the name.
After you select the tables you want, the AppBuilder displays the query in the Query panel. For example, if you choose the Customer, Order, and Order‑Line tables, the Query field appears, as shown in Figure 31.
Figure 31: Sample query for OF‑joinable tables
As you select tables, the AppBuilder establishes the relationship among them and the other tables that you have selected. If two tables share a common key with the same name and data type, they are OF joinable. If they are not OF joinable, you must establish how they will be joined. For example, if you choose the Customer and Local-Default tables, the AppBuilder displays the query in the Query panel, as shown in Figure 32.
 
FOR EACH sports.Customer NO-LOCK,
  EACH sports.LocalDefault NO-LOCK WHERE TRUE: /* Customer join incomplete */
Figure 32: Query sample with incomplete join criteria
The comment indicates that you must complete the relationship of the Local‑Default table to the Customer table. Complete the join criteria by choosing the Join button to enter Join mode.
If you select three or more tables and AppBuilder can establish the relationship among them, the AppBuilder still allows you to choose a table and change its join criteria. You do this by selecting the table in the Selected Tables & Joins list and choosing the Switch Join Partners button.
Join mode
When you activate the Join radio‑set button in the Query Builder dialog box, you enter Join mode, and the Query Builder appears as in Figure 33.
Figure 33: Query Builder in Join mode
To enter this mode, you must already have selected at least two database tables. If you select more than two tables, you can use the down arrow button in the Join combo box to choose the combination of tables you want to join.
If two tables are OF joinable, you can change their join criteria by activating the Customize Join toggle box to establish a WHERE clause between the two tables. Your comparison options are equal, not equal, less than, greater than, less than or equal, and greater than or equal. To use one of these options, double‑click in a field from one of the tables, double‑click on one of the comparison buttons, then click in another field from the other table.
As you establish comparisons between fields, the AppBuilder displays the ABL code in the Join Criteria panel. When you click OK, the AppBuilder displays the code in the Query Builder’s Query field (and in the widget’s property sheet, if the query is an embedded one). You can link the comparisons with AND or OR.
If you are familiar with ABL, you can edit the Join Criteria field by clicking in it and typing in the code directly.
Where mode
To specify search criteria for a table, activate the Where radio set button in the Query Builder dialog box. The Query Builder enters Where mode and appears as in Figure 34.
Figure 34: Query Builder in Where mode
First, select a field from the list of fields. The AppBuilder displays the possible comparison operators for that field. These operators are sensitized based on the type of field.
For example, if you select the Cust‑num field, the Contains button is not sensitized because Cust‑num is not a word‑indexed field. Also, Begins and Matches are not sensitized either, because Cust‑num is an integer field.
After you select a field, choose one of the enabled comparison buttons. A dialog box appears that lets you supply a constant for comparison (you cannot specify variables). If you click either List or Range, the dialog boxes that appear allow you to specify whether the list or range is inclusive or exclusive. The AND and OR buttons extend the relationships between logical fields and also let you combine logical phrases with a complex expression.
The AppBuilder displays the ABL text of the clause in the Where Criteria editor. When you click OK, the AppBuilder displays the code in the Query Builder when it is in Table mode, and in the widget’s property sheet if the query is an embedded one.
If you are familiar with ABL, you can type the search criteria directly into the Where Criteria panel.
Sort mode
To specify the sort order you want for your query, activate the Sort radio set button. The Query Builder enters Sort mode and appears as in Figure 35.
Figure 35: Query Builder in Sort mode
Note: If your query uses the {&SORTBY-PHRASE} preprocessor value to specify a sort order for the final result, the AppBuilder prevents you from entering this Sort mode by making the Sort button insensitive. You can set the query to use the {&SORTBY-PHRASE} from the Options mode of the Query Builder. For more information, see the “Options mode” section.
To specify the sort criteria, select fields from the Available Fields list. The Table combo box allows you to move between tables if you chose more than one table while in Table mode.
In the Sort Criteria editor, AppBuilder displays the fields you choose and the sort criteria you specify. When you click OK, the sort criteria is also displayed in Table mode. The Ascending and Descending radio buttons allow you to specify the display order of the sorted records.
Options mode
To specify query‑tuning options and other query parameters, click Options. Query Builder enters the Options mode and appears as in Figure 36.
Figure 36: Query Builder in Options mode
Editable find, join, and field list criteria
To support the QUERY FIELD-LIST feature, Query Builder displays an editable browse with the following fields:
*Table — Specifies the tables in the query. This field is not editable.
*Find — Specifies the find criteria (EACH, FIRST, or LAST). This field is editable.
*Join — Specifies the type of join (INNER or OUTER). This field is editable.
*Returned — Specifies which fields are returned (included in the field list) when records are fetched to satisfy the query (All Fields or Fields Used). This field is editable.
To change the Find field, double‑click in it until the desired value appears or type E, F, or L.
To change the Join field, double‑click in it until the desired value appears or type I for INNER or O for OUTER.
The Returned field is a logical field that can be set in one of two modes: All Fields (the default) and Fields Used. When you select All Fields, the query fetches all fields of the table. When Fields Used is selected, the query returns only those fields in the FIELDS-IN-QUERY preprocessor list. For browses, this is controlled in the Column Editor; for frame queries, this is controlled by what fields you place in the frame.
To select All Fields, either double‑click in the field until All Fields appears or type A. To select Fields Used, double‑click in the field or type F.
Editable query tuning options
You specify query‑tuning parameters by entering your choice of parameters in the Query Tuning Options panel. Depending on your choices, the AppBuilder inserts code into the OPEN-QUERY statement. For example:
 
LOOKAHEAD
NO-DEBUG
INDEX-HINT
Query‑tuning parameters correspond to the QUERY-TUNING keyword. This keyword is available with the FIND, FOR EACH/LAST/FIRST, and OPEN-QUERY statements. For more information, see OpenEdge Development: ABL Reference.