Select

Used to generate the SELECT clause of an SQL database query. This tab displays by default when you open the Visual SQL Query Builder dialog box (Settings menu > Database > Query Builder).


Visual SQL Query Builder dialog box, with the Select tab displayed

Tables

Opens the Select Table dialog box, which provides a list of database tables for selection for generating SQL queries.

Functions

Selects a query value.

Except for the first function (Value of, which is the default), these functions derive one value for a group of query results. That is, the bottom five functions are aggregate functions. Only queries containing aggregate functions can have a GROUP BY clause. When an aggregate function is selected, a GROUP BY clause is added to the query being generated in the SQL Select Statement text box.

Dataset Mode

When on, selects Dataset query mode operation. Dataset mode is a special form of querying which allows for random access to query results (as opposed to limiting data viewing to a forward sequential manner only). The random access of query results is achieved by fetching each row of the query result and storing its primary key in a list. If a table has a MSLINK field, it is the default primary key. If a table does not have a MSLINK field, the first numeric field becomes the primary key. Dataset mode only works with tables that have a numeric field of unique values. All primary keys are fetched prior to retrieving any attributes. Since database query fetches primary keys in addition to attributes, a Dataset query takes more time to complete than a normal query, especially if the query results in a large number of rows. The Previous command button is only available in Dataset mode.

When off, Cursor mode is in affect. Cursor mode is supported directly by the database software (Oracle or ODBC. This mode operates by opening a query against the database. Since database software does not provide backwards fetching, Previous is not supported in this mode. Since there is no fetching of all primary key values (as in Dataset mode), Cursor mode is faster then Dataset mode.

Execute Query on Open

When on, the fields displayed in the form dialog are populated by results of the query. When off, the fields appear without values, enabling a user to key in values directly into the form (for example, by keying-in “> 100” in the MSLINK text box) to execute new queries.

No Duplicate Rows

If on, a query will not return duplicate rows.

Append to Dataset

If on, subsequent query results are appended to the current dataset. This is useful when you want to save multiple query results as one dataset to a single ASCII text file.

If off, only the results of the most recently executed query are recognized as the current dataset.