This topic will enhance your understanding of when and why to correlate your subquery. Look at the sample file Salesdemo.srm. This report selects all customers and displays all their orders. Assume that you only want the orders that are larger than a customer's average order, Instead of sorting through the entire customer report you can correlate the query as shown below.
To find only those orders that exceed a customer’s average order:
From the Where Clause Builder, click Add Clause.
Choose Sale_Amount.
Choose Greater than.
Choose subquery:
Choose Orders, Ordlines, Products.
Add an expression by giving it a name, for example AverageSale.
Pick the coordinates for the expression as shown below:
avg(ORDLINES.QUANTITY * PRODUCTS.PRICE)
In the Correlate page, link the Master query cust_num to the Subquery cust_num.
Finish the subquery.
Finish the master query.
The subquery is linked to the master query by the customer number. Unlike an uncorrelated query, you cannot run the subquery on its own. To run the subquery separately, you would need to provide a customer number and run the query once for every customer number returned by the master query. Using a correlated subquery saves you from having to do all this work manually.
To decide whether to correlate your subquery, think about whether you can run the subquery on its own. Would you have to run it once for each row of the master query, as above? If your answer is yes, then you need to correlate your subquery. Look at the example, "correlate_subquery.srm" given in the Samples folder for a better understanding of correlated subqueries.
..\Hyperion\products\biplus\bin\SQR\Studio\samples\correlate_subquery.srm
If you placed SQR Production Reporting Studio in a destination other than the default destination, then look for the Samples folder there.
If you have to run your subquery only once for the whole report, it is called an uncorrelated subquery.