Where Deck

A WHERE clause is used to extract only those records that meet a specified requirement. To add a WHERE Clause, drag in a column from a Table Entity in the Diagram Pane or Table Pane to the [Where] Deck.

When a column is dropped inside the Where Deck the [Create Where Criteria] Dialog appears. 

This dialog allows:

• Choosing Column Name or writing an Expression.
• Choosing a column Operator - You can select =, < >, LIKE, BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL or (None).
• Entering a Custom Operator - You can type your own custom operator in the [Operator] dropdown
• Entering a Value - You will see examples of what you could put for the Operator you've selected as text hints.
• Creating a Subquery - You can write a Subquery for the Join, which allows you to build this Subquery on a new Subquery tab.
• Viewing a Preview of the Clause - This shows the SQL of your clause.
• Enclosing Values in Quotes

Once [Save] is clicked in the [Where Clause Criteria] Dialog the column appears in the Where Deck and is numbered. The SQL Pane then displays what appeared in the Preview field of the dialog.

As more columns are dragged into the Where Deck they are numbered in sequence and the bottom of the deck indicates the Filter Logic by referring to the numbers instead of the full column names to conserve space. The SQL Pane will use the full column names in its WHERE clause.

If more than one column is present in the Where Clause Deck a Filter Logic subsection will appear at the bottom of the deck for multiple criteria. Dragging columns into new orders will renumber them. Criteria Filter Logic can be modified by selecting [AND ALL], [OR ALL], or by creating a Custom filter. See the Modifying Criteria Filter Logic for more.

The Query Builder attempts to automatically set appropriate logic based on the columns dropped in the Where Clause Deck and will prompt you if the Filter Logic you manually generate is not valid. If a column used in the Filter Logic has been dropped from the database, that column will highlight in red to notify you that action should be taken.

To modify a WHERE clause:

1. Either right click on a column listed in the Where Deck and select [Edit...]

or

2. Left click on the menu triangle to the right of the column listed in the Where Deck and select [Edit...]

The [Edit Where Clause Criteria] Dialog will appear for you to make modifications.

The Where Deck also includes an Index Manager discussed below.

 

Index Manager

The Index Manager displays Index information related to the fields in the Where Criteria in a separate deck in the Query Builder Workspace. This is useful to analyze the indexes which may be used in the query and helps you create efficient queries. When the Index Manager is enabled it will show all of the indexes which a column in the Where Clause Deck are a part of.

To launch the Index Manager, select [Worksheet > Show Decks > Index Manager Deck]. 

When it's made visible, the Index Manager appears by default on the right side of the Query Builder Workspace.

With the Index Manager enabled, drag columns from any deck or the Data Pane into the Where Clause Deck. The Index Manager will display the Indexes for that column.

Mouse hovering an Index Name provides Index details like Table Alias, Table Name, Schema and Database.

Clicking an Index Name in the Index Manager shows the columns participating and mouse hovering displays additional details on the indexed column including Data Type, Foreign Key participation, Index Sequence Number and Index Order.

 

Multiple Criteria and Criteria Filter Logic

When multiple criteria appear in the Where and Having Decks it's possible to modify their Criteria Filter Logic. Three choices appear if you right click on existing Filter Logic:

• AND ALL - The default which uses all of the criteria with AND between each.
• OR ALL - Uses OR between each criteria.
• Custom - For creating your own Criteria Filter Logic.

To use Custom Filter Logic:

1. Right click on the Filter Logic and choose [Custom].

2. Within the [Edit Filter Logic] Dialog enter your Custom Criteria Filter Logic.

3. Click [Save] in the [Custom Filter Logic] Dialog and the SQL Pane updates to show it's there. A cog icon next to the Filter Logic indicates a Custom Criteria Filter Logic is in use.

Criteria Filter Logic will highlight with an orange background when you haven't used all of the existing criteria. Hovering over an orange Filter Logic produces a tooltip with suggestions for what could be altered.