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 Where tab.

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:

column_name operator value

Examples:

cust_name = 'Smith'

purchase_time > CURRENT TIME – 1 DAY

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

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

The column may have a Column Function applied to it.

Column Value

To specify the column value, you can:

• enter the value in the box at the top. Edit Value will show you a larger box in case you need to enter a large value.
• click on Get Values to see the existing values of the column. You can then select one from the list.
• click on Extended Vals to get a fuller dialog for displaying column values.
• click on Get Constants to see the common system variables (and other constants) for the column.
• if your column is a Date, Time or Timestamp type, you will get a Select Date button to help specify a date value.

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 for your column type. However in some cases you may want to set this yourself.

Build Sub-Select

A sub-selects allow you to compare your column to a value in another table.

Clicking Build Sub Select will take you to the Build Sub Select dialog where you can specify the sub-select.

BETWEEN Clause

When you have a BETWEEN clause you need to specify two values for the column (eg. BETWEEN 100 AND 200). You will get a dfferent style Column Values panel where you can specify these two values.

Enter the value in the box, or click Get Values and select from the dropdown list.

IN Clause

When you have an IN clause you can specify multiple values for a column - eg. IN (‘A’,’B’,’C’,’D’).

To make it easier to deal with a list of values, click on Build IN Clause. This will show the Build IN Clause dialog.

If you are entering the values manually, you must include the quote for each value. Use quotes is ignored in this case.

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.

Not comparing a Column

In some rare cases you may not be comparing a column, but are comparing a special register or constant. Examples:

USER = 'test'

CURRENT DATE > '2010-03-04'

23 > 56 (I've no idea why someone might want to do this!)

You can specify a Where Clause like this by entering the first value (USER, CURRENT DATE, 23 etc) in the Column Function box.

This is a HAVING Clause

If you are building a summary query, you will see another checkbox This is a HAVING clause.

When you check this box, you will be building a Having Clause, rather than Where Clause. The Column Functions will show column summary functions. For a Having Clause you must select one of these.

Add and New/Close

• Add and New will add the Where Clause to your query, but keep the window open so you can add another.
• Add and Close will add the Where Clause to your query, then close the window.