Selecting Fields and Defining Criteria
The Columns pane consists of the field columns grid and the SQL function toolbar sections (see Figure 1.8 for details).
Figure 1.8. Showing the Columns pane with several selected fields
The field columns grid consists of the following columns:
Column |
This grid-column is a combo-box control (with edit-box style), which initially contains a combined list of all field names derived from all the objects in the Relations pane. |
Alias |
This grid-column is an edit-box control, and is used for optional alias name entry for the content of the Column grid-column (by default it is empty). |
Object |
This grid-column is a combo-box control (with edit-box style), which initially contains a list of the names of all objects in the Relations pane. |
Object Alias |
This grid-column is an edit-box control, and is used for optional object alias name entry for the content of the Object grid-column (by default it is empty). |
Output |
This grid-column is a check-box control, and is used to indicate whether the field is to be outputted or not. |
Sort Type |
This grid-column is a combo-box control containing the choices Ascending, Descending and No-Sort. |
Sort Order |
This grid-column is a combo-box control containing the possible order choices depending on the currently selected sort fields. For example, if we have 3 sort fields selected, then the choices will be 1, 2, 3, and No-Sort. |
Group By |
This grid-column is a check-box control, and is used to indicate whether the field is used as aggregate. |
Criteria |
This grid-control is an edit-box control, and is used to enter criteria for the query. |
Or... |
This grid-control is an edit-box control, and is used to enter criteria for the query. |
Note that for a new field-row in the Columns pane, initially the Column combo-box lists a combined list of all field names derived from all the objects in the Relations pane, but if the Object grid-column is specified this list will be restricted to only the field names of the selected object. Similarly, the Object combo-box initially lists the object names of all objects in the Relations pane, but when the Column grid-column is not empty, only object names that have this Column value as a field name will be listed.
The Columns pane is mainly used to define criteria for the currently open query. In the most standard situation the user may define query criteria by selecting a field from the Column grid-column, and then entering some value under the Criteria grid-column. This value may be preceded with one of the following operators =, <, >, <=, >=, !<, !>, !=, <>, IS, IS NOT, NOT, IN, LIKE, and BETWEEN. Note that the default operator is =, so that if the user does not specify an operator the equality is assumed.
Observe that when the user specifies criteria via direct entry into the WHERE-clause of the SQL statement in the SQL pane, some of the conditions may be considered expressions and may entirely be put under the Column grid-column. This is specially the case when a criteria condition cannot be represented in the following standard form:
<field_name> <operator> <value>
Also, note that any thing following the LIKE operator will be automatically put inside quotes (if it is not already inside quotes). The syntax of the LIKE-expression, which may be any regular expression, depends on the back-end database engine. For example, in the case of the MS-SQL Server the syntax is outlined below:
Wildcard character |
Description |
Example |
% |
Any string of zero or more characters. |
WHERE lastname LIKE Har% will include all last names which start with Har, for example Harry, Harold, etc. |
_ (underscore) |
Any single character. |
WHERE lastname LIKE H_r_ld will include all last names which start with H and end with ld, with three middle characters separated with an r, for example Herald, Harold, etc. |
[] |
Any single character within the specified range ([a-f]) or set ([abcdef]). |
WHERE lastname LIKE H[ae]rald will include all last names which start with H and end with rald, with one character in the set {a, e}, for example Herald, Harald, etc. |
[^] |
Any single character not within the specified range ([^a-f]) or set ([^abcdef]). |
WHERE lastname LIKE H[^a]rald will include all last names which start with H and end with rald, with one character between them that is not an a, for example Herald, Hyrald, but not Harald. |
We illustrate next the usage of the grid in the Columns pane. Essentially, the vertical direction represents conjunction (i.e. AND), while the horizontal direction represents disjunction (i.e. OR). For example, if we want to query all the contact names of the customers whose last names start with John and first name is Ben, or whose last name is Benjamin and the first name starts with Frank, then we may enter the following entries in the cells of the Columns grid:
Column |
Criteria |
Or |
Or |
last_name |
LIKE John% |
= Benjamin |
|
first_name |
= Ben |
LIKE Frank% |
|
|
|
|
|
which results into the following WHERE-clause in the SQL pane:
WHERE (last_name LIKE John% AND first_name = Ben)
OR (last_name = Benjamin AND first_name LIKE Frank%)
Observe that we may include several disjunctions under one Criteria/Or grid-cell. For example, the following WHERE-clause
WHERE ((last_name LIKE John% OR last_name LIKE Jon%)
AND first_name = Ben)
OR (last_name = Benjamin AND first_name LIKE Frank%)
may be represented in the following way:
Column |
Criteria |
Or |
Or |
last_name |
LIKE (John% OR Jon%) |
= Benjamin |
|
first_name |
= Ben |
LIKE Frank% |
|
|
|
|
|
Note that more Or columns may be added as needed and that any criteria condition for a query may be represented into this grid of the Columns pane, given that complex expressions involving SQL functions may be entirely included in the Column grid-column not using the Criteria/Or grid-columns.
Finally, other than specifying query criteria, sorting and grouping, the Columns pane may also be used as a collection of fields on which the SQL functions toolbar may be applied. We will consider these SQL functions in the next section.