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. For the example, basic_where.qry, where StockNumber is greater than 400000 and less than 50000, modify the query to group constraints. The new query will retrieve results where sales in any of the 3 months is greater than 1500 units, as long as sales for each of the 3 months is greater than 1000 units.

Click Where in the Visual Query Builder. 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 to retrieve data where sales in any of the 3 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 to retrieve data where sales in each of the 3 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.

1. 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 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.

1. 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
  March > 1000)
  

Then click Ungroup. The three statements are no longer grouped.

Retrieving Information That Meets Specified Criteria

Presenting Results in Specified Order

© 1994-2005 The MathWorks, Inc.