Creating a Join as a Simple Query

Add to the sample diagram a join simple query that combines multiple datasources, similar to the join expression options of the CCL FROM clause.

1. In the Visual editor Palette, in Streams and Windows, select Join.
   If necessary, close the compartments below Streams and Windows, or use the arrow below the compartment, so that Join is visible.
2. Click in the diagram to create the object.
   For this example, edit the join object name to be IndividualPositions.
3. Connect the join object to the appropriate stream or window.
   Attach join objects to any stream, window, or Flex operator. Join objects have multiple inputs, but only one output.

   Note: Streams, windows and delta streams can participate in a join. However, a delta stream may participate in a join only if it has a KEEP clause specified. Only one stream can participate in a join. For more about supported joins, see the Studio Users Guide or the CCL Programmers Guide.
   For this example, connect the VWAP aggregate object and the Positions input window to the IndividualPositions join object.
4. Click Copy Columns () in the join shape toolbar and select columns to copy.
   For this example, choose Select All, then clear the check box on the second Symbol field.
5. Click Add Column Expressions ().
   For this example add two columns: Current Position and Average Position.
6. To modify Column Expressions, either:
   • Press F2 to open the inline editor. A drop-down menu displays which is populated with input columns and built-in functions. Select the input column or built-in function you want to use to define the expression.
   • Press Ctrl+F2 to open the expression editor. Press Ctrl+Space to display the available input columns and built-in functions, or enter the desired expression manually.
   • Modify the expression in the Properties view.
   For this example, create these Column Expressions:

   • BookId Positions.BookId
   • Symbol Positions.Symbol
   • CurrentPosition ( VWAP.LastPrice * CAST ( float , Positions.SharesHeld ) )
   • AveragePosition ( VWAP.VWAP * CAST ( float , Positions.SharesHeld ) )
7. In the Join Conditions compartment of the join shape, set up the join conditions.
   In this example, if you connected the join to the VWAP and Positions inputs, in that order, there are now two elements in the Join Conditions compartment. The first defines the leftmost element for the join. If you connected to VWAP first, the first element (left side of the join) is VWAP. You can double click it to change it. For this example, you must configure the second join element.
   1. Double-click the second join element to open the Edit Join Expression dialog.
   2. Select the columns from Source 1 and Source 2 to join on.
      For this example, select Symbol for both Positions and VWAP.
   3. Click Add.
      The columns chosen appear in Join Constraints, where you should now see Positions.Symbol=VWAP.Symbol. You cannot edit join constraints manually in the Visual editor.
   4. (Optional) Choose a join type.
      For this example, use the default, LEFT.
8. Click OK.