Use subqueries to feed data to a subsequent query.
Here is how the previous example could be restructured using subqueries:
INSERT INTO AvgSaleComparison SELECT ORCL.avgsale, MSFT.avgsale FROM (SELECT AVG(volume*price) as avgsale FROM StockTrades KEEP 10 ROWS WHERE symbol = 'ORCL') AS ORCL KEEP LAST, (SELECT AVG(volume*price) as avgsale FROM StockTrades KEEP 10 ROWS WHERE symbol = 'MSFT') AS MSFT KEEP LAST;
Subquery expressions, enclosed in parentheses, take the place of stream names in the FROM clause and are essentially the same as any other query except:
No INSERT INTO clause is used before the SELECT clause.
If any columns in the SELECT list are expressions, an AS clause is needed for each column in order to provide a name which can be used in the outer query.
If the data from the subquery is going to be used in a window, the window definition must be placed outside the subquery as shown above. Notice that subqueries themselves can have windows but the output of a subquery is always a stream unless it is converted to a window by a KEEP clause in the outer query.
The query above is functionally identical to the previous example, except that it is accomplished in a single query with two subqueries instead of using three separate queries and two additional data streams. In actuality, Sybase CEP constructs the same intermediate data streams between the subqueries and the outer queries, but the user isn't required to do anything other than write the subquery.
The use of subqueries is principally designed to minimize the need to define additional intermediate data streams. Any query with subqueries can always be restructured as a query pipeline with explicit intermediate data streams. In some cases, this can be helpful, particularly when debugging a Sybase CEP application, because intermediate data streams can be observed in the Studio and adapters can be temporarily attached to intermediate streams to capture the stream data. This is not currently possible with subqueries.