NOTE: These examples are particular to Oracle 10g, but with some modifications this can be applied to most other modern databases as well, including MS SQL, PostgreSQL and DB2.

Background
Recently I had the requirement to reuse some TABLE request results in several SQL queries. To complicate matters, the TABLE request was performing a query that wasn't very efficient, so even though the request was on the same database as the other queries I didn't want to repeat the query for each following statement, as that would significantly increase the time it would take to create the report.

The initial table request in my case was the result of a hierarchical query, with result columns for the level in the hierarchy where the result was found and the actual result. For example:

 LEVEL | CATEGORY | PART
-------+----------+-------------
     1 | TREE     | Pear tree
     2 | BRANCH   | Pear branch
     3 | LEAF     | Pear leaf
     3 | FRUIT    | Pear
     4 | PULP     | Pear mash
     4 | SEED     | Pear seed

The queries that were to use the results of the above only applied to one level of the hierarchy each. The first query would only apply to TREE's, the 2nd query to BRANCHes, the 3rd to LEAVEs and FRUIT and the 4th to PULP and SEEDs.

Initially, I wrote the above results to an ALPHA file and used a -REPEAT loop to -READ it line by line and concatenate parts of WHERE clauses for each query. Messy!

Common Table Expressions
These days it's possible to define a "virtual" table in an SQL query that you can easily reuse. It's fairly similar to creating a temporary table, except that it doesn't actually get written to the database and has a scope of a single query instead of the scope of a database transaction or more.

For example:

WITH tree AS (
    SELECT LEVEL, CATEGORY, PART
      FROM TREES
     START WITH PART = 'Pear tree'
     CONNECT BY PRIOR PART_OF = PART
)
SELECT region
FROM tree
INNER JOIN tree_region ON (tree.PART = tree_region.tree AND tree.LEVEL = 1)
GROUP BY region, tree;

The above uses the INNER JOIN to restrict the tree_region's listed with the trees that we selected in our CTE.

However, recursive queries are generally not fast. Especially not if the actual query is more complicated than the above and when there's a lot of data to filter the desired hierarchy out of.

That's why I didn't want to repeat the same query part in my case, remember?

Putting things together
So, writing the results to a text file got a bit too messy to my tastes and the recursive CTE's add an unacceptable amount of query time to each query. What to do?

Of course, we use the best of both.

The initial query gets written to a text file, but in such a way that it's usable as a data set in a CTE.

Like this:

SQL SQLORA PREPARE TREE FOR
    SELECT LEVEL, CATEGORY, PART
      FROM TREES
     START WITH PART = 'Pear tree'
     CONNECT BY PRIOR PART_OF = PART
;
END

DEFINE FILE TREE
	LVL/I2 = LEVEL;
END
TABLE FILE TREE
PRINT
	COMPUTE ROWNUM/I1 = LAST ROWNUM +1; NOPRINT
	COMPUTE UNION/A10 = IF ROWNUM GT 1 THEN 'UNION ALL ' ELSE ''; NOPRINT
	COMPUTE SELECT/A90 = UNION | 'SELECT ' | EDIT(LVL) | ' AS LVL, ''' || CATEGORY ||
		''' AS CATEGORY, ''' | PART | ''' AS PART FROM dual';

ON TABLE HOLD AS SPECS FORMAT ALPHA
END
-RUN

-SET &TREE_QRY = IF &LINES GT 0 THEN '-INCLUDE TREE' ELSE 'SELECT 0 AS LVL, '''' AS CATEGORY, '''' AS PART FROM dual';

This creates an alpha file which' contents are a valid SQL query:

          SELECT 1 AS LVL, 'TREE' AS CATEGORY,     'Pear tree' AS PART FROM dual
UNION ALL SELECT 2 AS LVL, 'BRANCH' AS CATEGORY,   'Pear branch' AS PART FROM dual
UNION ALL SELECT 3 AS LVL, 'LEAF' AS CATEGORY,     'Pear leaf' AS PART FROM dual
UNION ALL SELECT 3 AS LVL, 'FRUIT' AS CATEGORY,    'Pear' AS PART FROM dual
UNION ALL SELECT 4 AS LVL, 'PULP' AS CATEGORY,     'Pear mash' AS PART FROM dual
UNION ALL SELECT 4 AS LVL, 'SEED' AS CATEGORY,     'Pear seed' AS PART FROM dual

The variable &TREE_QUERY either allows us to include the above query (if we had any results) or substitute it with something that's valid but won't match anything else in our database.

The above we can now use in other queries by joining them to our dataset:

SQL SQLORA PREPARE QUERY1 FOR
WITH tree AS (
&TREE_QRY.EVAL
)
SELECT *
  FROM tree
 INNER JOIN tree_region ON (
        tree.PART = tree_region.PART
    AND tree.LVL = 1
)
;
END

TABLE FILE QUERY1
PRINT *
END

SQL SQLORA PREPARE QUERY2 FOR
WITH tree AS (
&TREE_QRY.EVAL
)
SELECT *
  FROM tree
 INNER JOIN tree_region ON (
        tree.PART = tree_region.PART
    AND tree.LVL = 2
)
;
END

TABLE FILE QUERY2
PRINT *
END

Etcetera.

I kept the code relatively simple and in that process lost the reason why those table requests in different parts of the hierarchy needed to be different. In my case they do, but it doesn't really matter for the example. It's the concept that's important here, after all!