Joining a Stream to a Window

In this activity you will be combining a stream that represents a request for information with a window containing trade information in order to provide the price and time of the most recent trade for the specified stock symbol.

You will be modifying the StockFilter project you worked with in the State Persistence tutorial.

Follow these steps to open the StockFilter project and to remove the stream that you no longer need for this tutorial (if you are continuing immediately after completing the previous tutorial, you can skip the first three steps):

Start Sybase CEP Studio.
On the File menu, click Load Project.
Navigate to the directory where you stored the StockFilter project for the State Persistence tutorial and then open StockFilter.ccp. You can also find a copy of the completed project in SybaseC8Repository/version/examples/GettingStarted/CompletedProjects/AfterMaintainingState.
Click FilteredTrades in the Explorer view.
On the Project menu, click Delete Stream and then click Yes in the dialog box to confirm the deletion. Note that the CCL statement creating the stream is removed from the Queries tab.

Creating a Join Query

Follow these steps to create a query that uses data from two sources:

Replace the Query statement beginning with "-- Create an unnamed window" in the Queries tab with the following text:
```
-- Create a named window

CREATE WINDOW TradesWindow 
SCHEMA (Symbol STRING, Price FLOAT, Volume INTEGER)
KEEP LAST ROW PER Symbol;

-- Create an input stream

CREATE INPUT STREAM TradeInquiries
SCHEMA (Symbol STRING);

-- Create an output stream

CREATE OUTPUT STREAM TradeResults
SCHEMA (Symbol STRING, Price FLOAT, TimeOfTrade TIMESTAMP);

-- Populate TradesWindow with rows from 
-- the StockTrades stream

INSERT INTO TradesWindow
SELECT *
FROM StockTrades;
```
Note: You can also copy the text from the file CombiningSourcesCCL.txt in the directory SybaseC8Repository/version/examples/GettingStarted/CCLFiles (where version is the version number of Sybase CEP Engine), between the lines "-- Combining Sources Code Block 1" and "-- Combining Sources Code Block 2."

The first three statements create a window and two additional streams. Note the policy of the window: KEEP LAST ROW maintains the most recent row in the window, just like KEEP 1 ROW. The last statement populates TradesWindow with rows arriving in the StrockTrades stream.
Copy the following text and then append it to the end of the contents in the Queries tab (or copy the text from CombiningSourcesCCL.txt between the lines "-- Combining Sources Code Block 2" and "-- Combining Sources Code Block 3"):
```
-- Create the join

INSERT INTO TradeResults
SELECT TradeInquiries.Symbol, 
       TradesWindow.Price,
       GETTIMESTAMP(TradesWindow)
FROM TradeInquiries JOIN TradesWindow
ON TradeInquiries.Symbol = TradesWindow.Symbol;
```
A query that combines data from more than one data source is called a join. This statement joins a data stream carrying values that represent stock ticker symbols to a window that keeps the latest price and volume information for each symbol it receives:
- The SELECT clause refers to columns in both data sources. It publishes the symbol from TradeInquiries, the price from TradesWindow, and the timestamp of the TradesWindow row (notice the GETTIMESTAMP function). Because both TradeInquiries and TradesWindow have a Symbol column, you must qualify the column reference with the name of the data source.
- The FROM clause lists the query's two data sources, TradeInquiries and TradesWindow, linked by the word JOIN.
- The ON clause establishes the join condition. In this case, the condition is that the value of the Symbol column in the TradesWindow row must match the value of the Symbol column in the incoming TradeInquiries row. Sybase CEP Engine combines the incoming TradeInquiries row with each TradesWindow row that meets the join condition and then publishes a separate row containing the columns specified in the SELECT clause for each combination. Sybase CEP Engine ignores rows in TradesWindow that do not meet the join condition.

Next

In terms of the example, TradesWindow maintains information about the most recent trade for each stock. A row arriving on TradeInquiries is a request for information about the most recent trade of a specific stock. Rows published to TradeResults contain the symbol of the stock, the price of that stock for the most recent trade, and a timestamp of when the row representing that trade arrived in the window.