Creating an Uncorrelated Subquery

This section will enhance your understanding of when and why to uncorrelate your subquery. Look at the sample file sales_demo.srm. This report selects all customers and displays all their orders. Assume that you only want a list of customers whose orders exceed 100 units per item. Instead of sorting through the entire customer report, you can uncorrelate the query as shown below.

*  To find only those orders that exceed 100 units per item:

  1. From the Where Clause Builder, click Add Clause.

  2. Choose cust_num.

  3. Choose IN.

  4. Choose subquery:

  5. Choose Orders, Ordlines

  6. Choose Orders.cust_num

  7. Add a condition: 

    Ordlines.quantity >= 100

  8. Finish the subquery.

  9. Finish the master query.

If you ran the subquery on its own, you would get a list of customer who ordered in bulk. Taking that list, you could write a condition:

cust_num IN (100001, 100002, 100004, 100006, 100007, 100008, 100009, 100012, 100014, 100015, 100016, 100017)

Using a subquery saves you this extra step.

To decide whether to correlate your subquery, think about whether you could run the subquery on its own. Would you have to run it only once for the whole report, returning a list, as above? If your answer is yes, then you need to uncorrelate your subquery. Look at the example, "uncorrelate_subquery.srm" given in the Samples folder for a better understanding of uncorrelated subqueries.

Note:

The following is the default path for the Samples folder and example: 

..\Hyperion\products\biplus\bin\SQR\Studio\samples\uncorrelate_subquery.srm

If you placed SQR Production Reporting Studio in a destination other than the default destination, then look for the Samples folder there.

Note:

If you have to run your subquery once for each row of the master query, it is called a correlated subquery.

Related Topics

Creating a Correlated Subquery