Simplifying Complex Select Paragraphs

With relational database design, information is often normalized by storing data entities in separate tables. To display the normalized information, you must write a select paragraph that joins these tables. With many database systems, performance suffers when you join more than three or four tables using one select paragraph.

With SQR, you can perform multiple select paragraphs and nest them. In this way, you can break a large join into several simpler selects. For example, you can break a select paragraph that joins the orders and the products tables into two selects. The first select retrieves the orders that you want. For each order that is retrieved, a second select retrieves the products that were ordered. The second select is correlated to the first select by a condition such as:

where order_num = &order_num

This condition specifies that the second select retrieves only products for the current order.

Similarly, if a report is based on products that were ordered, you can make the first select retrieve the products and the second select retrieve the orders for each product.

This method improves performance in many cases, but not all. To achieve the best performance, you may need to experiment with the different alternatives.

You can use master and detail reports to perform multiple select paragraphs and nest them.

See Using Dynamic SQL.