Page History
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:
...
To modify a WHERE clause:
1. Either right-click on a column listed in the Where Deck and select [Edit...]
...
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 is 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 their Criteria Filter Logic. Three choices appear if you right-click on the existing Filter Logic:
- AND ALL - The default which uses uses all of the criteria with AND between each.
- OR ALL - Uses OR between each criteriacriterion.
- 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 is updated to show it's there. A cog icon next to the Filter Logic indicates a Custom Criteria Filter Logic is in use.
Info |
---|
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. |