CCL Subqueries in the FROM Clause

Syntax

( select_clause from_clause [matching_clause] [on_clause] [where_clause] [group_by_clause] [having_clause] [order_by_clause] [limit_clause] [output_clause] ) [AS] alias [keep_clause] [keep_clause]

Components
select_clause	The select list specifying what to publish. See SELECT Clause for more information.
from_clause	The data sources. See FROM Clause for more information.
matching_clause	A pattern-matching specification. See MATCHING Clause for more information.
on_clause	A join condition. See ON Clause for more information.
where_clause	A selection condition. See WHERE Clause for more information.
group_by_clause	A partitioning specification. See GROUP BY Clause for more information.
having_clause	A filter definition. See HAVING Clause for more information.
order_by_clause	A sequencing definition. See ORDER BY Clause for more information.
limit_clause	A limit on the number of rows to publish. See LIMIT Clause for more information.
output_clause	A synchronization specification. See OUTPUT Clause for more information.
alias	An alias for the subquery.
keep_clause	A window policy. See KEEP Clause for more information.

Usage

The list of data sources in the FROM Clause: Comma-Separated Syntax and FROM Clause: Join Syntax variations of the FROM clause can include CCL subqueries. The main body of the subquery is enclosed in parentheses and follows the same syntax as the Query Statement, but without an INSERT clause and without the final semicolon (;). Subqueries in the FROM clause produce aggregate output and can be nested one inside another (subqueries can contain other subqueries).

The body of the subquery must be aliased with an AS clause. This clause serves two purposes: it creates an implicit destination, into which the results of the subquery are published, and it is used by clauses in the outer query to refer to the subquery output. An implicit schema for the destination defined by the alias is created based on the column names of the data sources specified within the subquery. By default, the destination created by the alias behaves as a data stream and does not keep state. However, one or two KEEP clauses can be added to the alias to create a window. In this case, the subquery destination behaves as a window and keeps state.

Restrictions

A subquery cannot directly reference a data stream or window in the parent or outer query and vice versa.
A subquery produces an implicit schema based on the column names of the data sources specified within the subquery. To prevent this implicit schema from having duplicate or ambiguous names, the select list of the subquery's SELECT clause must observe one of the following restrictions:
- The select list must be limited to column references (with or without data source names and/or aliases) and all the column names must be unique.
  
  OR
- The expression of every select list item must be assigned a unique alias.

Example

The following example contains a subquery that creates a full outer join between two windows based on StreamA and StreamB. The results of the subquery are then joined with a third unnamed window, based on StreamC.

INSERT INTO OutStream
SELECT Sq.Subcol1, Sq.Subcol2, Sq.Subcol3, C.Column1, C.Column2
FROM 
     (SELECT A.Column1 AS Subcol1, A.Column2 AS Subcol2, 
             B.Column2 AS Subcol3
      FROM 
         StreamA AS A  KEEP 100 ROWS 
         FULL OUTER JOIN 
         StreamB AS B KEEP 100 ROWS
      ON A.Column1 = B.Column2) AS Sq KEEP 100 ROWS
   FULL OUTER JOIN StreamC AS C KEEP 100 ROWS
   ON Sq.Subcol3 = C.Column2;