Retrieving Data from Multiple Tables

Data can be retrieved from multiple tables in ESQL by addressing several tables in the SELECT statement of the cursor declaration, in the same way as in interactive SQL. The preprocessor generates a SELECT statement addressing multiple tables, which is optimized by the SQL compiler when the cursor is opened.

When considering the two alternatives, the first one is preferred. The reason for this is:

The SQL optimizer gets the full information about the query that it is supposed to return a result set for. In this way the optimizer can make more use of statistical information and it can thereby optimize the query to execute in a more efficient way.

The application will require less resources in the form of open cursors.

If the application is run in a client/server environment, the second alternative will cause more communication over the network, since it will send data over the net which is only used to determine which data from the second cursor that will be selected and is of no real interest to the application.

The application will be more compact as well as easier to understand and maintain.

The 'Parts explosion' Problem

A special case of data retrieval from multiple tables is the use of stacked cursors to fetch data from logical copies of the same table, in a manner that provides a solution to the so called "Parts explosion" problem.

A cursor can be defined as REOPENABLE and the same cursor may be opened several times in succession in the same application program, each previous instance of the cursor being saved on a stack and restored when the following instance is closed. A FETCH statement refers to the most recently opened instance of a cursor. Each instance of the cursor addresses an independent result set and the position of each cursor in its own result set is saved on the stack.

Note: Result sets addressed by different instances of a cursor may differ according to the conditions prevailing when the cursor instance was opened.

The state of the cursor stack needs to be controlled by the application. A counter can be used to indicate if there are more instances of the cursor remaining on the stack. See the example that follows.

Stacked cursors are typically used in application programs which traverse a tree structure stored in the database.

For example (this is a simplified variant of the "parts explosion" problem), traverse a tree structure and print out the leaf nodes: