Database Toolbox

Grouping Criteria

In the Where Clauses dialog box, you can group together constraints so that the group of constraints is evaluated as a whole in the query. Continuing with the example, basic_where.qry, where StockNumber is greater than 400000 and less than 50000, modify the query to retrieve results where sales in any of the three months is greater than 1500 units, as long as sales for each of the three months is greater than 1000 units. The Where Clauses dialog box appears as follows to retrieve data where the StockNumber is greater than 400000 and less than 50000.

1. Add the criteria that retrieves data where sales in any of the three months is greater than 1500 units.
1. In Current clauses, select StockNumber < 500000, and then click Edit.
2. For Operator, select OR, and then click Apply.
3. In Fields, select January. For Relation, select > and type 1500 in the field for it. For Operator, select OR, and then click Apply.
4. In Fields, select February. For Relation, select > and type 1500 in the field for it. For Operator, select OR, and then click Apply.
5. In Fields, select March. For Relation, select > and type 1500 in the field for it. Then click Apply.

The Where clauses dialog box appears as follows.



2. Group the criteria requiring any of the months to be greater than 1500 units.
1. In Current clauses, select the statement January >1500 OR.
2. Shift-click to also select February > 1500 OR.
3. Shift-click to also select March > 1500.
4. Click Group.

An opening parenthesis, (, is added before January, and a closing parenthesis, ), is added after March > 1500, signifying that these statements are evaluated as a whole.



3. Add the criteria that retrieves data where sales in each of the three months is greater than 1000 units.
1. In Current clauses, select the statement March >1500) and then click Edit.
2. For Operator, select AND, and then click Apply.
3. In Fields, select January. For Relation, select > and type 1000 in the field for it. For Operator, select AND, and then click Apply.
4. In Fields, select February. For Relation, select > and type 1000 in the field for it. For Operator, select AND, and then click Apply.
5. In Fields, select March. For Relation, select > and type 1000 in the field for it. Then click Apply.

The Where clauses dialog box appears as follows.



6. Click OK.

The Where Clauses dialog box closes. The SQL statement in the Visual Query Builder dialog box reflects the modified where clause. Because the clause is so long, you have to use the right arrow key in the field to see all of the contents.

4. Assign a MATLAB workspace variable, for example, AA.
5. Click Execute.

The results are a 7-by-4 matrix.

6. To view the results, type AA in the Command Window.
   
   

Removing Grouping.   To remove grouping criteria in the Where Clauses dialog box, in Current clauses, select all of the statements in the group and then click Ungroup. The parentheses are removed from the statements.

For the above example, to remove the grouping, select (January > 1000 AND, and then Shift-click to also select February > 1000 AND, and March > 1000). Then click Ungroup. The three statements are no longer grouped.

Retrieving Information That Meets Specified Criteria

Presenting Results in Specified Order