Create a Join in a Graphical View

Drag a source from the Source Browser and drop it onto a source or other node in the diagram.

Procedure

If the Source Browser panel is not visible on the left of the screen, click Source Browser in the toolbar to show it.
Browse or search for the object you want to add on either of the tabs.
- The Repository tab lists all the tables, views, and intelligent lookups that are available in the space (including objects shared to the space)..
  
  Note
  
  If you use a local table with delta capture on as a source for your view, the column Change Date and Change Type won't be displayed.
  
  For more information, see Add Objects from the Repository.
- The Sources tab lists all the connections and other data sources that have been integrated to the space from which you can import tables. However it shows only limited records. If you can't see the sources you are looking for, use Import from Connection to perform search. You can:
  
  Expand the data sources to browse through their objects (see Import an Object from a Connection or Other Source).
  
  Open the Import Objects from Connection dialog on a particular connection to select multiple objects for import (see Import Multiple Objects from a Connection).
Select your object and drag it over a source or other node in the diagram. When the target node is highlighted green, drop the new source to create a join.
The source is added to the diagram and it is joined to the target node. The join symbol is selected and its properties are displayed in the side panel. A projection node is added after the join node to remove any duplicate columns.

Note

If you choose a table or view from the Sources tab, it is automatically imported into the repository and deployed, and will be available on the Repository tab for future use by you or others.
Optional. Rename the node in its side panel to clearly identify it. This name can be changed at any time and can contain only alphanumeric characters and underscores.

Set the following properties in the General section:

Property	Description
Join Type	Specifies the type of SQL join to create. You can choose from: Cross - Return all of the rows from the left table joined to all of the rows from the right table. Full - Return all of the rows from both tables, joining records from the left table where possible. Inner - [default] - Return all of the rows in the left table that have a matching row in the right table. Left - Return all of the rows from the left table (whether or not they have a match in the right table), and any matching rows from the right table. Right - Return all of the rows from the right table (whether or not they have a match in the left table), and any matching rows from the left table.
Distinct Values	Return only unique values.
Cardinality	Specifies the number of rows matching another table in a join and may improve the view's performance. You can choose from: `MANY TO ONE` `MANY TO EXACT ONE` `MANY TO MANY` `ONE TO ONE` `ONE TO EXACT ONE` `ONE TO MANY` `EXACT ONE TO ONE` `EXACT ONE TO EXACT ONE` `EXACT ONE TO MANY` The cardinality is integrated in the code and is visible in the SQL preview.

Property

Description

Join Type

Specifies the type of SQL join to create. You can choose from:

Cross - Return all of the rows from the left table joined to all of the rows from the right table.
Full - Return all of the rows from both tables, joining records from the left table where possible.
Inner - [default] - Return all of the rows in the left table that have a matching row in the right table.
Left - Return all of the rows from the left table (whether or not they have a match in the right table), and any matching rows from the right table.
Right - Return all of the rows from the right table (whether or not they have a match in the left table), and any matching rows from the left table.

Distinct Values

Return only unique values.

Cardinality

Specifies the number of rows matching another table in a join and may improve the view's performance. You can choose from:

MANY TO ONE
MANY TO EXACT ONE
MANY TO MANY
ONE TO ONE
ONE TO EXACT ONE
ONE TO MANY
EXACT ONE TO ONE
EXACT ONE TO EXACT ONE
EXACT ONE TO MANY

The cardinality is integrated in the code and is visible in the SQL preview.

Specify the mapping of join columns in the Mappings section:
- A mapping is automatically created by matching column names if possible.
- To manually map columns, drag a column from the left list and drop it onto a column in the right list.
- To delete a mapping, click the link and then click the Delete tool.
- You can filter the Mappings section to show only mapped or unmapped pairs of columns.
- You can filter or sort the left or right column lists independently
Click  (Preview Data) to open the Data Preview panel and review the data output by this node. For more information, see Viewing Object Data.