Seeing the Join in Action: Exercise

Now follow these steps to see this join you created in action

  1. Locate the project from to Debug, if it is in Release mode by selecting the Debug option from the drop-down list, as described in .
  2. Select your project and click the plus sign.
  3. Click on TradeInquiries and click the viewer button, etc.
  4. Similarly select and open viewers for TradesWindow and TradeResults and position all the viewers so they are clearly visible.
  5. Start project
Parent topic: Joins

Join Example

In the real world trade inquiries for a specific stock would probably arrive from some external source. In this example, you will use a debugging feature of Sybase CEP Studio to feed streams in manually one at a time. This will allow you to see the join results more clearly.
  1. Open insert row box.
  2. Select TradeInquiries stream in box. The dialog box now shows all the columns for the stream you selected and allows you to manually enter values into these columns (in this case only one column -- Symbol) and send them as a row into the stream. The dialog box also shows a Row Timestamp column. Since the TradeInquiries stream assigns Sybase CEP Server timestamps based on the current Sybase CEP Server time, this field displays the current Sybase CEP Server time and cannot be set manually.
  3. Enter the EBAY in Symbol and click Send to send a row with the value EBAY in the Symbol column into the TradeInquiries stream.
  4. Now look at your stream and window viewers and click on the pause button to stop the scrolling of rows and see query results better.

    TradeInquiries viewer shows the row you manually sent to it.

    The TradesWindow viewer shows the match in the window -- the most recent EBAY trade -- which is joined to the incoming row in TrdeInquries.

    The TradeResults stream shows the output -- the latest price for EBAY and the time at which the trade was concluded.

  5. Use the step forward and back buttons to look at the relationships between the incoming rows.
  6. Look at the output again. Notice the join doesn't produce any output when nothing comes in in the stream. A join can have only one stream and executes only when a row arrives in the stream.
  7. When a row does arrive in the stream, it is matched up separately with all rows in the window that meet the ON clause join condition -- in this case all rows that have the same value in the Symbol column as the incoming row in TradeInquiries. Each of these row combinations is then processed as a separate row by the SELECT clause and each generates its own row in the TradeResults stream. (When there is no ON condition, all combinations of the row arriving in the stream and each of the rows contained in the window are passed to the SELECT clause.)
  8. Unpause the stream and window viewers.

Now let's see what happens if we send a row into TradesInquiries that has no corresponding trade record in TradesWindow.
  1. Restart project.
  2. Go back to the insert row box and enter the value YHOO in the box and click send.
  3. Look at the viewers again. Notice that TradeResults does not have any output for this. The type of join you created is called an inner join. An inner join publishes only row combinations that meet the join condition in the ON clause (if there is an ON clause). In this case, that means that if a row arrives in the TradeInquiries stream and causes the query to execute, rows are only made available to the SELECT clause if the value in the Symbol column of the TradeInquiries stream has a corresponding value in the TradesWindow Symbol column. Since the ON clause didn't find a match in the window for the incoming YHOO symbol, nothing was published.
  4. Stop the project.