Processing a Query that Contains another Query Section

When you process a Query section that contains another Query section, it forms SQL that uses what are called “derived tables”, which are essentially subquery statements in a FROM clause. The SQL generation phase behaves as follows:

Generates the SQL for the Query section being used as a topic in the current Query section being processed, including resolving any variable filters. Any Sort line items in the source Query section are ignored in generating the SQL. This generation only occurs if the contents of the Query as a topic is referenced by the currently processing Query section, following the rules for join path generation specified for the currently processing section.
The resulting SQL is added to the currently processing Query section if required, in the FROM clause, surrounded by parentheses, as with any subquery.
The entire subquery is given a table alias. The alias will follow the pattern for any table involved in a query and will thus be of the form “ALn”. As with subquery filters, any aliases used in the subquery itself will be distinct from the alias names used in the currently processing Query section’s table references. The alias will be used thereafter to refer to columns in the “derived table”, as is normal for SQL generation based on physical tables.
Following the table alias name, “ALn”, a parenthesized list of column aliases is added. This list is identical to the list of names of the topic items in the Query topic. (For Oracle, the column aliases are displayed in the select list of the subquery instead of in a separate parenthesized list, for example, SELECT AL1.STORE AS store….)
Before submitting the resulting SQL to the database, a check is performed to ensure that the query is properly joined. If it is not, the standard behavior, as specified by the Interactive Reporting database connection file preference “Allow Non-Joined Queries”, is taken.
Following submission of the SQL, any references to the column alias names on the Request line of the currently processing Query section are available to build the dependent Results section.
The minimum requirement is that if the “derived table” is referenced by the currently processing query, its SQL is generated as if that section were processed alone, with the exceptions noted above. At initial implementation or in a future release, the SQL would include only those columns actually needed by reference in the currently processing Query. This reduces the number of items in the SELECT list of the subquery, with a corresponding reduction in the number of column alias names.
For example, assume the following Query section is to be used as the source for a “derived table” in another Query section:
In this example, the computed item’s definition is “UCASE (Pcw_Customers.Buyer)”. The filter on ‘Store Type’ is set to ‘Discount’.
The Query section using “Query” as a derived table might appear as follows:

In the previous diagram, the filter on City is set to ‘Los Angeles’. The SQL generated when the above query is processed, per the minimum requirements, would be:

SELECT AL2.STATE, SUM(AL1.UNITS) FROM PCW_SALES AL1,
(SELECT AL3.STORE_ID, AL3.CITY, AL3.STATE, UCASE(AL3.BUYER)
FROM PCW_CUSTOMERS AL3 
WHERE AL3.STORE_TYPE = ‘Discount’) AL2(STORE_ID, CITY, STATE, COMPUTED)
WHERE AL2.STORE_ID = AL1.STORE_ID AND AL2.CITY = ‘Los Angeles’
GROUP BY AL2.STATE