Previous Topic

Next Topic

Book Contents

Book Index

Build a Where Clause

This window is used to build an individual Where Clause. You enter this window by clicking on NEW or MOD in the GUI Where tab. Refer to this for general information on the features of the Query Builder.

You may also wish to refer to our summary of Where Clause Syntax.

The layout of this window reflects the normal format of a Where clause, which is:

function(column_name) operator value

Examples:

substr(cust_name,1,2) = ‘FR’

cust_type NOT IN (‘a’,’b’)

purchase_time > CURRENT TIME – 1 DAY

cust_name like '%Smith%'

Component

Use

Notes

Function

specifies an (optional) column function

Specify this in the format function(:) – the column name will be substituted wherever the colon appears.

The listbox will display a list of common functions for the type of your column. You can select one of these or type one in.

If you have a long column function, click on the >>> button to get an expanded window where a long value can be entered.

Column_name

specifies the column the Where clause is based on

First, select the table; when you do so AQT will show you all the columns in the table.

If you select Show > Query Cols, AQT will show you only the columns that are in the query. Select the appropriate column.

Operator

specifies the Where clause operator

The listbox has most of the common Where clauses. For some clauses (In, Between, Like) you can qualifier the operator with a NOT. You do this by checking the NOT checkbox. Note that for most databases not equals is given by <>.

Value

specifies the value the column is compared to

Specify this by

typing the value into the box, or

clicking on >>> to get an extended box for longer values.

Display column values

Two buttons are very useful for examining the contents of the column.

Get Values will show you all the existing values of the column.

Get Constants will show you common system variables (and other constants) for the column.

As you build the Where clause, the full text of the clause will be displayed in the Where Clause text box.

Quotes

This specifies whether the value in the Column Value box is to have quotes placed around it. AQT will (normally) be smart enough to set this appropriately (eg. will be set for character values, not set for numeric values). In some cases you may want to set this yourself.

BETWEEN Clause

When you have a BETWEEN clause you specify two values for the column (eg. BETWEEN 1 AND 10). AQT allows you to specify these two values independently; you will get a box Value is where you can specify whether the value you are specifying is the first or second value in the Between Clause.

IN Clause

When you have an IN clause you can specify multiple values for a column - eg. IN (‘A’,’B’,’C’,’D’). There can be many of these values; some databases allow you to specify several hundred. In future releases of AQT we may build a facility for easily manipulating large IN clauses. In the current release, you can specify multiple IN clause values by selecting multiple items from the value list (using the Ctrl and Shift keys).

STARTS WITH and CONTAINS

For character columns, AQT will show you operators Starts With and Contains. These are not valid SQL operators, but make it easier for you to build the appropriate clauses using the LIKE operator.

Sub-Selects

Sub-selects can be used in a Where clause, normally in conjunction with the IN operator. Example: col1 IN (select distinct col1 from tab2).

AQT allows you to enter a sub-select (eg. you can type it into the Column Value box), however AQT currently gives you no assistance in building it. This feature will be added in a later release of AQT.

Clause is Column Based

The preceding information relates to Where clauses that involve comparing a column to a value. We call these “Column-based Where clauses”. The vast majority of your Where Clauses will be like this.

However it is possible to have Where clauses that are not in this format.

One example of this is a clause such as:

Exists (Select * from Tab1 Where Tab1.ColA = Tab2.ColB)

You can specify clauses such as this by setting Clause is Column Based to No. All the upper windows will be greyed out – you type the Where clause directly into the Where Clause textbox.

HAVING Clause

If you are building a summary query, you have the option of building a Having Clause as well as a Where Clause. In this case this window will have an option box Having Clause (yes/no). Use this to select whether you are building a Where Clause or a Having Clause. For a Having Clause, the Column Functions will show column summary functions. For a Having Clause you must select one of these.