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.