Documentation Center

  • Trial Software
  • Product Updates

Fine-Tuning Queries Using Advanced Query Options

    Note:   For details about advanced query options, select Help in any of the dialog boxes for the options.

Retrieving All Occurrences vs. Unique Occurrences of Data

To use the dbtoolboxdemo data source to demonstrate how to retrieve all versus distinct occurrences of data:

  1. Set the Data return format preference to cellarray.

  2. Set Read NULL numbers as to NaN.

  3. In Data operation, choose Select.

  4. In Data source, select dbtoolboxdemo.

    Do not specify Catalog or Schema.

  5. In Tables, select SalesVolume.

  6. In Fields, select January.

  7. To retrieve all occurrences of January:

    1. In Advanced query options, select All.

    2. Assign the query results to the MATLAB workspace variable All.

    3. Click Execute to run the query.

  8. To retrieve only unique occurrences of data:

    1. In Advanced query options, select Distinct.

    2. Assign the query results to a MATLAB workspace variable Distinct.

    3. Click Execute to run the query.

  9. In the MATLAB® Command Window, enter All, Distinct to display the query results:

    The value 3000 appears three times in All, but appears only once in Distinct.

Retrieving Data That Meets Specified Criteria

Use basic.qry and the Where field in Advanced query options to retrieve stock numbers greater than 400000 and less than 500000:

  1. Load basic.qry.

  2. Set the Data return format preference to cellarray.

  3. Set Read NULL numbers as to NaN.

  4. In Advanced query options, click Where.

    The WHERE Clauses dialog box appears.

  5. In Fields, select the field whose values you want to restrict, StockNumber.

  6. In Condition, specify that StockNumber must be greater than 400000.

    1. Select Relation.

    2. In the drop-down list to the right of Relation, select >.

    3. In the field to the right of the drop-down list, enter 400000.

      The WHERE Clauses dialog box now looks as follows.

    4. Click Apply.

      The clause that you defined, StockNumber > 400000, appears in the Current clauses area.

  7. Add the condition that StockNumber must also be less than 500000.

    1. In Current clauses, select StockNumber > 400000.

    2. In Current clauses, click Edit or double-click the StockNumber entry.

    3. For Operator, select AND.

    4. Click Apply.

      The Current clauses field now displays:

      StockNumber > 400000 AND

    5. In Fields, select StockNumber.

    6. In Condition, select Relation.

    7. In the drop-down list to the right of Relation, select <.

    8. In the field to the right of the drop-down list, enter 500000.

    9. Click Apply.

      The Current clauses field now displays:

      StockNumber > 400000 AND
      StockNumber < 500000

  8. Click OK.

    The WHERE Clauses dialog box closes. The Where field and SQL statement display the Where Clause you specified.

  9. Assign the query results to the MATLAB workspace variable A.

  10. Click Execute.

  11. To view the results, enter A in the Command Window:

  12. Save this query as basic_where.qry.

Grouping Statements

Use the WHERE Clauses dialog box to group query statements. In this example, modify basic_where.qry to retrieve data where sales in January, February, or March exceed 1500 units, if sales in each month exceed 1000 units.

To modify basic_where.qry:

  1. Click Where in VQB. The WHERE Clauses dialog box appears.

  2. Modify the query to retrieve data if sales in January, February, or March exceed 1500 units.

    1. In Current clauses, select StockNumber < 500000 and click Edit.

    2. For Operator, select OR and click Apply.

    3. In Fields, select January. For Relation, select > and enter 1500 in its field. For Operator, select OR. Click Apply.

    4. Repeat step c twice, specifying February and March in Fields.

      The WHERE Clauses dialog box now looks as follows.

  3. Group the criteria that require sales in each month to exceed 1500 units.

    1. In Current clauses, select the statement January > 1500 OR. Press Shift+click to select February > 1500 OR and March > 1500 also.

    2. Click Group.

      An opening parenthesis is added before January and a closing parenthesis is added after March > 1500, indicating that these statements are evaluated as a group.

  4. Modify the query to retrieve data if sales in each month exceed 1000 units.

    1. Select March > 1500 ) in Current clauses and click Edit.

    2. Select AND for Operator and click Apply.

    3. Select January in Fields. Select > for Relation and enter 1000 in its field. Select AND for Operator. Click Apply.

    4. Repeat step c twice, specifying February and March in Fields.

      The WHERE Clauses dialog box now looks as follows.

    5. Click OK.

    The WHERE Clauses dialog box closes. The SQL statement dialog box displays the modified where clause.

  5. Assign the query results to the MATLAB workspace variable AA.

  6. Click Execute to run the query.

  7. To view the results, enter AA in the MATLAB Command Window.

Removing Grouping of Statements

To use the WHERE Clauses dialog box to remove grouping criteria from the previous example:

  1. In Current clauses, select (January > 1000 AND.

  2. Press Shift+click to select February > 1000 AND and March > 1000) also.

  3. Click Ungroup.

The parentheses are removed from the statements, indicating that their grouping is removed.

Displaying Results in a Specified Order

Use Order by in Advanced query options to specify the order in which query results display.

This example uses the basic_where.qry query you created in Retrieving Data That Meets Specified Criteria. The results of basic_where.qry are sorted so that January is the primary sort field, February the secondary, and March the last. Results for January and February appear in ascending order, and results for March appear in descending order.

To specify the order in which results appear in basic_where.qry:

  1. Load basic_where.qry.

  2. Set the Data return format preference to cellarray.

  3. Set Read NULL numbers to NaN.

  4. In Advanced query options, select Order by.

    The ORDER BY Clauses dialog box appears.

  5. Enter values for the Sort key number and Sort order fields for the appropriate Fields.

    To specify January as the primary sort field and display results in ascending order:

    1. In Fields, select January.

    2. For Sort key number, enter 1.

    3. For Sort order, select Ascending.

    4. Click Apply.

      The Current clauses area now displays:

      January ASC

  6. To specify February as the second sort field and display results in ascending order:

    1. In Fields, select February.

    2. For Sort key number, enter 2.

    3. For Sort order, select Ascending.

    4. Click Apply.

      The Current clauses area now displays:

      January ASC
      February ASC

  7. To specify March as the third sort field and display results in descending order:

    1. In Fields, select March.

    2. For Sort key number, enter 3.

    3. For Sort order, select Descending.

    4. Click Apply.

      The Current clauses area now displays:

      January ASC
      February ASC
      March DESC

  8. Click OK.

    The ORDER BY Clauses dialog box closes. The Order by field and the SQL statement in VQB display the specified Order By clause.

  9. Assign the query results to the MATLAB workspace variable B.

  10. Click Execute to run the query.

  11. To view the results, enter B in the MATLAB Command Window. Enter A to display the unordered query results and compare them to B. Your results look as follows:

    For B, results are first sorted by January sales, in ascending order. The lowest value for January sales, 1200 (for item number 400455), appears first. The highest value, 5000 (for item number for 400345), appears last.

    For items 400999, 400314, and 400876, January sales were 3000. Therefore, the second sort key, February sales, applies. February sales appear in ascending order: 1500, 2400, and 2400 respectively.

    For items 400314 and 400876, February sales were 2400, so the third sort key, March sales, applies. March sales appear in descending order: 1800 and 1500, respectively.

Using Having Clauses to Refine Group by Results

Using the HAVING Clauses Dialog Box

Use the Having function to refine the results of a Group By clause.

After specifying a group-by clause in Advanced query options, click Having. The HAVING Clauses dialog box appears.

  1. From the Fields list box, select the entry whose value to restrict.

  2. Define the Condition for the selected field, as described in Retrieving Data That Meets Specified Criteria.

  3. Select Operator to add another condition.

  4. Click Apply to create the clause.

    The subquery appears in the Current clauses area.

  5. Repeat steps 1 through 4 to add more conditions as needed.

  6. Change the clauses as needed:

    • To edit a clause:

      1. Select the clause from Current clauses and click Edit.

      2. Modify the Fields, Condition, and Operator fields as needed.

      3. Click Apply.

    • To group clauses:

      1. Select the clauses to group from Current clauses. Press Ctrl+click or Shift+click to select multiple clauses.

      2. Click Group. Parentheses are added around the set of clauses.

        To ungroup clauses, select the clauses and then click Ungroup.

    • To delete a clause, Select the clause from Current clauses and click Delete. Use Ctrl+click or Shift+click to select multiple clauses.

  7. Specify a subquery in the HAVING Clauses dialog box, as needed. For details, see Creating Subqueries for Values from Multiple Tables.

  8. Click OK.

    The HAVING Clauses dialog box closes. The SQL statement in the Visual Query Builder dialog box updates to reflect the specified having clause.

Example: Using Having Clauses

This example restricts the results from basic_where.qry to sales greater than 2000 for January and February:

  1. In Advanced query options, click Having. The HAVING Clauses dialog box appears.

  2. For January:

    1. Select > as the Relation Condition.

    2. Enter 2000 as the Relation value.

    3. Select the AND Operator.

    4. Click Apply.

  3. For February:

    1. Select > as the Relation Condition.

    2. Enter 2000 as the Relation value.

    3. Click Apply. The HAVING Clauses dialog box appears as follows.

  4. Click OK.

    The HAVING Clauses dialog box closes. The SQL statement field in the VQB dialog box reflects the specified Having clause.

  5. Assign a MATLAB workspace variable C, and click Execute to run the query.

    C =
       [3000]   [2400]
       [5000]   [3500]

    Compare these results to those in Displaying Results in a Specified Order.

Creating Subqueries for Values from Multiple Tables

Use the Where feature in Advanced query options to create subqueries. Creating subqueries in this way is referred to as nested SQL.

This example uses basic.qry, which you created by selecting Query > Save and saving your query as basic.qry in the File name field.

The salesVolume table has sales volumes and stock number fields, but no product description field. The productTable has product description and stock number fields, but no sales volumes. This example retrieves the stock number for the product whose description is Building Blocks from the productTable table. It then gets the sales volume values for that stock number from the salesVolume table.

  1. Load basic.qry.

  2. Set the Data return format Preference to cellarray and Read NULL numbers as to NaN.

  3. Click Where in Advanced query options.

    The WHERE Clauses dialog box appears.

  4. Click Subquery.

    The Subquery dialog box appears.

  5. In Tables, select productTable, which includes the association between the stock number and the product description. The fields in that table appear.

  6. In Fields, select stockNumber, the field that is common to this table and the table from which you are retrieving results.

    The statement SELECT stockNumber FROM productTable is created in the SQL subquery statement.

  7. Limit the query to product descriptions that are Building Blocks.

    1. In Fields in Subquery WHERE clauses, select productDescription.

    2. For Condition, select Relation.

    3. In the drop-down list to the right of Relation, select =.

    4. In the field to the right of the drop-down list, enter 'Building Blocks'.

    5. Click Apply.

      The clause appears in the Current subquery WHERE clauses field and is added to the SQL subquery statement.

  8. Click OK to close the Subquery dialog box.

  9. In the WHERE Clauses dialog box, click Apply.

    This updates the Current clauses area using the subquery criteria specified in steps 3 through 8.

  10. In the WHERE Clauses dialog box, click OK.

    The WHERE Clauses dialog box closes. The SQL statement in the VQB dialog box updates.

  11. Assign the query results to the MATLAB workspace variable C.

  12. Click Execute.

  13. Type C at the prompt in the MATLAB Command Window to see the results.

  14. The results are for item 400345, which has the product description Building Blocks, although that is not evident from the results. Create and run a query to verify that the product description is Building Blocks:

    1. For Data source, select dbtoolboxdemo.

    2. In Tables, select productTable.

    3. In Fields, select stockNumber and productDescription.

    4. Assign the query results to the MATLAB workspace variable P.

    5. Click Execute.

    6. Type P at the prompt in the MATLAB Command Window to view the results.

      The results show that item 400345 has the product description Building Blocks. In the next section, you create a query that includes product description in the results.

    Note:   You can include only one subquery in a query using VQB; you can include multiple subqueries using Database Toolbox™ functions.

Creating Queries That Include Results from Multiple Tables

A query whose results include values from multiple tables is said to perform a join operation in SQL.

This example retrieves sales volumes by product description. It is like the one in Creating Subqueries for Values from Multiple Tables, but this example creates a query that returns product description rather than stock number.

The salesVolume table has sales volume and stock number fields, but no product description field. The productTable table has product description and stock number fields, but no sales volume field. To create a query that retrieves data from both tables and equates the stock number from productTable with the stock number from salesVolume:

  1. Set the Data return format preference to cellarray and the Read NULL numbers as preference to NaN.

  2. For Data operation, click Select.

  3. For Data source, select dbtoolboxdemo.

    The Catalog, Schema, and Tables for dbtoolboxdemo appear.

    Do not specify Catalog or Schema.

  4. In Tables, select the tables from which you want to retrieve data. For this example, press Ctrl+click and select both productTable and salesVolume.

    The fields (columns) in those tables appear in Fields. Field names appear in the format tableName.fieldName. Therefore, productTable.stockNumber indicates the stock number in the product table and salesVolume.StockNumber indicates the stock number in the sales volume table.

  5. In Fields, press Ctrl+click to select the following fields:

    • productTable.productDescription

    • salesVolume.January

    • salesVolume.February

    • salesVolume.March

  6. In this example, the Where clause equates the productTable.stockNumber with the salesVolume.StockNumber, so that product description is associated with sales volumes in the query results.

    In Advanced query options, click Where to associate fields from different tables. The WHERE Clauses dialog box appears.

  7. In the WHERE clauses dialog box:

    1. In Fields, select productTable.stockNumber.

    2. For Condition, select Relation.

    3. In the drop-down list to the right of Relation, select =.

    4. In the field to the right of the drop-down list, enter salesVolume.StockNumber.

    5. Click Apply.

      The clause appears in the Current clauses field.

    6. Click OK to close the WHERE Clauses dialog box. The Where field and SQL statement in VQB display the Where clause.

  8. Assign the query results to the MATLAB workspace variable P1.

  9. Click Execute to run the query.

  10. Type P1 in the MATLAB Command Window.

     P1 = 
    
        'Victorian Doll'     [1400]    [1100]    [ 981]
        'Train Set'          [2400]    [1721]    [1414]
        'Engine Kit'         [1800]    [1200]    [ 890]
        'Painting Set'       [3000]    [2400]    [1800]
        'Space Cruiser'      [4300]    [ NaN]    [2600]
        'Building Blocks'    [5000]    [3500]    [2800]
        'Tin Soldier'        [1200]    [ 900]    [ 800]
        'Sail Boat'          [3000]    [2400]    [1500]
        'Slinky'             [3000]    [1500]    [1000]
        'Teddy Bear'         [ NaN]    [ 900]    [ 821]

Additional Advanced Query Options

For details about advanced query options, choose an option and click Help in its dialog box. For example, click Group by in Advanced query options, and then click Help in the Group by Clauses dialog box.

Was this topic helpful?