Creating a Join as a Simple Query

Add a join to the sample project. A join combines events from two or more inputs to create a single stream or window. It is similar to a join in SQL.

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. Using the Connector tool, 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 this example, connect the VWAP aggregate object and the Positions input window to the IndividualPositions join object, in that order.

   Tip: To add multiple connections, Shift+click and hold the Connector tool and add connections. To return to normal selection, press Esc or click the Select tool in the Palette to release it.
4. Click Copy Columns () in the join shape toolbar and select columns to copy.
   Tip: If you get an error, or do not see all columns from both inputs listed, try reconnecting the new Join element to the Positions or VWAP shapes as needed.
   For this example, choose Select All, then clear the check box on VWAP.Symbol so that you don't get the symbol field twice.
5. Click Add Column Expressions ().
   For this example add two columns: CurrentPosition and AveragePosition.
6. To modify column expressions, either:
   • Double-click on the expression to open the inline editor, and either type directly or press Ctrl+Space for syntax completion assistance, to pick from column names and functions, or,
   • 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, or,
   • Modify the expression in the Properties view.
   For this example, create these Column Expressions:

   • CurrentPosition ( VWAP.LastPrice * Positions.SharesHeld )
   • AveragePosition ( VWAP.VWAP * Positions.SharesHeld )
7. In the Join Conditions compartment of the join shape, set up the join conditions.
   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. 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. Choose a join type.
      For this example, use RIGHT, which is a right outer join. You want RIGHT because VWAP is the first, or left input, and Positions is the second, or right input. You only want your positions in the output; you do not need prices for symbols that are not held in the portfolio.
   3. Select the columns to join on.
      You cannot edit join constraints manually in the Visual editor.
      For this example:.

      • As Source 1, ensure that VWAP is in the dropdown, and select Symbol:string as the column.
      • As Source 2, ensure that Positions is in the dropdown, and select Symbol:string as the column.

      
      
      
   4. Click Add.
      The columns chosen appear in Join Constraints, where you should now see:

      ON VWAP.Symbol=Positions.Symbol

      The dialog shows:
      
      
   5. Click OK.
8. In the join shape, click (Toggle Type to OUTPUT).

The IndividualPositions join shape now shows the completed join, as shown in the figure.