Having Deck

A HAVING clause helps you set conditions that filter which group results appear in the final results of your query. When a column is dropped inside the [Having Deck] the [Create Having Criteria] Dialog appears.

This dialog allows:

• Choosing Name
• Choosing Functions - like [AVG, COUNT, COUNT_BIG, MAX, MIN, SUM] or [Custom] criteria.
• Writing a Custom Expression
• Choosing an Operator - You can select =, < >, LIKE, IN, NOT IN, IS NULL, IS NOT NULL or (None).
• Entering a Value - You will see examples of what you could put for the Operator you've selected.
• Creating a Subquery - This creates a new Subquery tab in your workbook for building your Subquery.
• Viewing a Preview of the Clause - This shows the SQL of your clause.

Once [Save] is clicked in the [Having Clause Criteria] Dialog the column appears in the Having 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 Having 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 HAVING Clause.

If more than one column is present in the Having 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 adjusted by selecting [AND ALL], [OR ALL], or by creating a [Custom] filter. See Modifying Criteria Filter Logic below for more.

The Query Builder attempts to automatically set appropriate logic based on the columns dropped in the Having 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. See the Invalid Objects, Filter Logic or Joins page for more.

To modify a HAVING Clause:

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

2. Or left click on the menu triangle to the right of the column listed in the Having Clause Deck and select [Edit...]

Remove a HAVING Clause

The fastest way to remove a HAVING Clause is to click the triangle at the top right of the Having Clause Deck and select [Clear].

It's also possible to remove each column from the HAVING Clause by

1. Right clicking it and selecting [Remove]

or

2. Left clicking its menu triangle and selecting [Remove]

or

3. Left clicking a column to select it, then press [Delete]

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.