Advanced Filter Options

MarketWide's Filter dialog allows you to create filters at any level of complexity. The next few sections discuss some advanced options for refining filters. Click here for more basic filter information.. 

Topics:

Grouping Statements

 A group of statements is a set of consecutive statements connected by the 'AND' operator. In the Filter dialog, groups of statements have a solid title bar above them and a gray box around them. Groups of statements can be inside other groups.

There are two ways to group sets of statements to create filters:

Change a statement's AND operator to OR 

• • If you change a statement's AND operator to OR, the filter automatically puts that statement in a new group separated by OR.

Highlight statements and use the Group Items function.

• • If you highlight consecutive statements and click the Group Items toolbar button or choose Edit > Group from the menu, the filter places the selected statements in their own group separated by OR.

Changing a Statement's AND/OR Operator

When you add new statements to a filter the default relationship between them is AND, however, you can change that relationship to OR with a simple double click. If you change a statement's AND operator to OR, the filter automatically puts that statement into a new group separated from the other statements by OR.

Grouping Sets of Highlighted Statements

In addition to changing the AND operator to OR, you can group selected statements using the Group Items toolbar button or the Edit > Group menu option.

If you highlight consecutive statements and use the Group Items feature, the filter places the selected statements in their own group separated from the other statements by OR, as shown in the following example.

Moving Statements

You can move statements in a filter by simply dragging and dropping the statement to the new position you would like to move it to, like Windows Explorer.

Creating Subqueries

As the word implies, a subquery is a query within a query. For example, suppose you want to view all orders for customers that live in Pennsylvania, but your ORDERS_TABLE does not tell you in which states customers live. In other words, you want to view orders from the ORDERS_TABLE for a list of customer numbers satisfying certain conditions in the ADDRESS_TABLE. In SQL code, the query-subquery relationship looks like this:

Select * from Orders_Table where Customer_Number in (Select Customer_Number from Address_Table where State = 'PA')

The underlined section is the subquery, which pulls a list of customer numbers from the ADDRESS_TABLE to use as the value list for the main query against the ORDERS_TABLE.

In the filter dialog, you can use a subquery to specify values by choosing the Subquery popup menu option when you right-click the value area of a statement. The popup menu with the Subquery option only appears when you use the IN or NOT IN condition in your statement.

When you select the Subquery option, QueryBuilder appears and allows you to build the subquery using its point-and-click interface. A statement with a subquery shows SQL (…).

Custom SQL Statements

If you are familiar with SQL syntax and want to enter custom SQL text in your filters, you can use the SQL Text option in the left pane of the filter under the folder titled Special Objects. The SQL Text option allows you to add a statement to your filter in the right pane with your own SQL commands within it.

When you drag the SQL Text option to the right pane, a new statement appears asking you to Type SQL text here. You can enter any SQL code you like.

The Filter Menu and Toolbar

The Filter toolbar provides quick access to commonly used commands. Click an icon once to carry out the action.