Using SQL functions

The SQL functions toolbar features include all the SQL functions that are needed for the creation of complex queries. It is destined to help novice SQL users to get started without referencing various reference books for the syntax of the SQL functions supported in the particular backend database engine in use. These functions are categorized into 10 drop-down combo-box buttons plus two buttons as is shown in Figure 1.16. In this section we will outline their general usage.


Figure 1.16.
Showing the SQL functions toolbar with categorization

We illustrate now the general usage of the SQL functions toolbar. Essentially, there are two ways to use these SQL functions:

      ·       Select fields in the Columns pane and then apply the SQL function,
·       Apply the SQL function without any selection in the Columns pane,

The first method will substitute some arguments of the selected function by the selected field names and then insert the syntax string into the SQL pane at the current mouse-cursor location. While the other method will insert the syntax string into the SQL pane, with all arguments of the SQL function being dummy variables. Note that these dummy variables must be replaced with proper values after the insertion. In general, all the dummy arguments of an inserted function must be replaced by either a field name or some constant value.


Figure 1.17. Showing the SQL function application

For example to apply the Left SQL function to the ContactName field, we first position the mouse-cursor in a desired location in the SQL pane, then select the ContactName field-row in the Columns pane (see Figure 1.17 for more details). Then we drop-down the String functions toolbar button and select the Left function option from the drop-down list. As a result of these operations the following string will be inserted into the current mouse-cursor location in the SQL pane:

            LEFT(Customers.ContactName, nLen)

where nLen is a dummy variable and therefore must be replaced by some number. Note that when the focus of the active pane becomes the Relations pane, the Columns pane is updated so that a new field-row is appended as a result of these operations.