|
|
|
|
|
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 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
|
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.
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.
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.
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).
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 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.
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.
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.