Tools → Join Editor
About the Join Editor
The Join Editor allows you to create and manage Left-Outer Join relationships between two physical schema entity objects: a Child object and a Parent object. Physical schema entity objects include physical schema tables, aliases, Incorta Analyzer tables, Incorta SQL tables, and materialized views.
The join between entity objects can be from the same physical schema or different physical schemas. In addition, you can use the Join Editor to create a join between an object and itself (self-join) to create a hierarchy using the object data. Creating joins between physical schema entity objects allows you to reference columns from multiple objects in one query and get relevant data sets. For an entity object in a physical schema, you can add multiple joins as Parent or as Child.
Incorta Analyzer tables and Incorta SQL tables do not support self joins. When you try to load a table of these with a self join, it will throw errors.
It is not recommended to create multiple joins between the same objects because this will corrupt the query result set. Instead, you can add multiple conditions to the join, or you can use aliases for additional joins between the two objects.
The Join Editor allows you to create joins between entity objects in different physical schemas. The joins will show under both physical schemas: Child and Parent schemas. However, you can access or edit such joins only by using the Join Editor for the Child schema. The Loader Service updates joins with every physical schema refresh. If a join uses two objects in different physical schemas, the Loader Service will update or recalculate the join every time either physical schema is refreshed.
To learn more about joins in Incorta, review Concepts → Join.
Starting with the 5.1.2 release, changes that you make using the Join Editor, such as adding, updating, and removing join conditions or filters, are saved to a draft version and will not be published till you promote the changes to a saved version, that is, select Save Changes in the Schema Designer. For more details, refer to the Schema Designer modes.
Join Editor permissions and access rights
A user that belongs to a group with the Schema Manager or the SuperRole role can access the Join Editor for a physical schema that this user owns or has at least View access rights to.
If the Enable Super User Mode option is enabled in the Cluster Management Console (CMC) → Tenant Configurations → Security, the Super User that is a Tenant Administrator and any user with the SuperRole can access the Join Editor for a given object and edit it regardless of the access rights.
A user with only the Schema Manager role must own or have Edit access rights to the physical schema to be able to use the Join Editor to edit its join relationships. You can access the Join Editor for a physical schema using:
- the Schema Designer
- the Table Editor
- the Schema Diagram Viewer
Access the Join Editor using the Schema Designer
Using the Schema Designer, there are four different ways to access the Join Editor for a physical schema that you own or have access rights to:
- In the Action bar, select + New → Join.
- If there are no existing joins for the physical schema, in the Joins section, select Create New Join.
- In the Joins section, select a join.
- For a given join in the Joins section, in the Action menu (v arrow), select Join Details.
For new joins, the Join Editor opens with the current physical schema by default selected for the Child object (left side of the join). You can select objects for the Child side only from the current physical schema.
Access the Join Editor using the Schema Diagram Viewer
To access the Join Editor using the Schema Diagram Viewer, you need to use the Schema Diagram Viewer for the physical schema where the Child object in the join relationship exists.
Here are the options you have to access the Join Editor using the Schema Diagram Viewer for a given physical schema:
- Select an arrow that represents a join relationship. In the Join drawer, select the square with arrow icon at the top of the drawer or beside a join condition.
- Select an object in the diagram that is the Child in a join relationship (the base of a join relationship arrow). In the object details drawer, in the Child Joins section, select the square with arrow icon beside a join condition.
- Select an object in the diagram that is the Parent in a join relationship (the target of a join relationship arrow). In the object details drawer, in the Parent Joins section, select the square with arrow icon beside a join condition.
Access the Join Editor using the Table Editor
Here are the options that you have to access the Join Editor using the Table Editor for a given physical schema object:
- If there are no existing Parent joins for the given physical schema object, in the Parent Joins, select + Add Parent Join.
- If there are no existing Child joins for the given physical schema object, in the Child Joins section, select + Add Child Join.
- In the Table Editor Action bar, select + New → Join (as parent). The current object is selected for the Parent side; however you can select another entity object in the same physical schema or another physical schema.
- In the Table Editor Action bar, select + New → Join (as child). The current object is selected for the Child side; however you can select another entity object in the same physical schema only.
Join Editor anatomy
The Join Editor consists of the following:
- Action bar
- Join Conditions section
- Join Filters section
Action bar
The Action bar contains the following:
- +New: select to open the Add New menu. The Add New menu options are:
- Condition: select to add a new condition for the join
- Filter: select to add a new filter on the join
- Done: available when you add, edit, or remove a join condition or filter. Select to save the changes you made and exit the Join Editor.
- Cancel: Select to cancel the changes you made, if any, and exit the Join Editor.
When you exit the Join Editor, you revert to the Schema Designer or the Table Editor depending upon the tool you used to access the Join Editor.
Join Conditions section
The Join Conditions section shows the existing join conditions, if any. For a new join, you must specify one or more join conditions in the Join Editor. Additional conditions that you create for the same join relationship share the same physical schemas and objects in the first condition for both the Child and Parent sides.
Multiple join conditions cannot specify the same fully qualified name for a column or formula column.
To learn more about join conditions, review Concepts → Join Condition.
The Conditions section shows the following:
- + (addition icon): select to create a new condition to the join
- Join conditions for the given join relationship, if any. Each condition consists of three parts:
- Child (the left side of the join)
- Join operator
- Parent (the right side of the join)
- x (cross icon in a circle): select to delete the respective condition
+ (Add join condition)
Select to add a new condition to the join relationship. You can add multiple conditions to the same join relationship.
Child
The Child object in the join condition must be from the physical schema you edit.
For the Child side of a join condition, specify the following:
| Element | Description |
|---|---|
| Physical schema | Defaulted to the current physical schema and you cannot changed it. |
| Child Object | Select an entity object from the physical schema. This can be one of the following: ● a physical schema table ● an alias ● an Incorta Analyzer table ● an Incorta SQL table ● a materialized view |
| Column | Typically, a Foreign Key in the Child object referencing a Primary Key in the Parent object; however, it can be any column with a supported data type, that is, ● boolean (in the case of using formula columns that return a boolean value for the join columns) ● date ● integer ● long ● string ● text ● timestamp To learn more about join condition columns, review Concepts → Join Condition Column. |
It is not recommended to use a text or string column for the join filter column as string comparisons can be slow for large data volumes. Also, using a timestamp column may result in inaccurate join results unless it is intended to match the exact timestamp value.
It is not recommended to reference a formula column in an entity object in another physical schema for the join condition column.
Join operator
The join operator is a comparison operator that determines how to evaluate the join relationship and decide on matching rows in the Parent object. Available operators are:
<: less than≤: less than or equal to=: equal to≠,!=, or<>: not equal to>: greater than≥: greater than or equal to
At least one of the join conditions has to use the Equal (=) operator.
Parent
The Parent object in the join can be one of the following:
- The same as the Child object (self-join relationship)
- Another entity object in the same physical schema
- An entity object in another physical schema (cross-schema join)
In the case of a cross-schema join, you have to either own or at least have View access rights to the physical schema where the Parent object in the join exists.
For the Parent side of a join condition, specify the following:
| Element | Description |
|---|---|
| Physical schema | Defaulted to the current physical schema and you can select another physical schema for the first condition only. |
| Parent Object | Select an entity object from the physical schema. This can be one of the following: ● a physical schema table ● an alias ● an Incorta Analyzer table ● an Incorta SQL table ● a materialized view |
| Column | Typically, the Primary Key in the Parent object; however, it can be any column. In both cases, it must be of the same data type as the column in the Child object. It is not recommended to reference a formula column in an entity object in another physical schema for the join condition column. To learn more about join condition columns, review Concepts → Join Condition Column. |
x (Remove Condition)
The x option (cross icon in a circle) is available for each condition other than the first one. Select it to delete the respective join condition. You cannot delete the first condition.
Join Filters section
The Filters section shows the existing filters for the join relationship, if any. You can apply none, one, or more filters to a join. A join filter specifies an expression for a column in the Parent object. The expression affects the Parent object referenced in the join and functions to filter out the rows of the Parent object.
To learn more about join filters, review Concepts → Join Filter.
The Filters section shows the following:
- + (addition icon): select to add a new filter to the join
- Join filters in the given join relationship, if any. The expression of a join filter consists of:
- a column
- a filter operator
- a value
- x (cross icon): select to remove the respective filter
+ (Add Filter)
You can add multiple filters to the same join relationship. To return in the join query result set, a row in the Parent object must meet all the conditions and filters, if any, that you define for the join.
Column
For join filters, you can reference only columns from the Parent object in the join as the filter column. The following are the supported data types for a filter column:
- boolean (in the case of using a formula column that returns a boolean value for the filter column)
- date
- integer
- long
- string
- text
- timestamp
When using the First Version or the Last Version as the join filter operator, You need to select columns from the Parent object for both the filter column and the join filter value. In this case, the filter column is used to group the Parent object data by, and the least value (First Version) or the greatest value (Last Version) in the value column for each group determines the rows to retrieve from the Parent object.
It is not recommended to use a text or string column for the join filter column as string comparisons can be slow for large data volumes. Also, using a timestamp column may result in inaccurate join results unless it is intended to match the exact timestamp value.
To learn more about join filter columns, review Concepts → Join Filter Column.
Filter operator
The filter operator, along with the filter column and value, constitutes a filter expression that further restricts which rows to retrieve from the Parent object. Available operators are:
<: less than≤: less than or equal to=: equal to≠,!=, or<>: not equal to>: greater than≥: greater than or equal toIN: a value from an array of scalar valuesFirst Version: the lowest value in the join filter column for each group in the filter columnLast Version: the greatest value in the join filter column for each group in the filter column
Value
For a join filter value, you can use one of the following depending upon the operator you select:
- a scalar value, including a date system variable, for comparison operators (
<,≤,=,≠,>,≥) - an array of scalar values for the
INoperator - a column in the Parent object only in the case of
First VersionandLast Versionoperators.
x (Remove Filter)
The x option (cross icon) is available for each filter in the join. Select it to remove the respective join filter.
Join Editor actions for join condition
With the Join Editor, you can perform the following actions for join conditions:
Add a join condition
You can add one or more conditions. All conditions in a join relationship share the same Child object and Parent object; however, they cannot share the same join columns.
Here are the steps to add a condition to a join:
- If required, in the Join Editor, do one of the following:
- Select +New → Condition.
- In the Conditions section, in the upper-right corner, select +.
- Specify the condition details.
- In the Child section (the left side of the join):
- The current physical schema is selected by default and you cannot select another.
- For the first join condition only, select the Child object from the current physical schema.
- Select the join column in the Child object.
- In Join, select the join operator.
- In the Parent section (the right side of the join),
- For the first condition only, select the physical schema where the Parent object exists. This can be the current physical schema or another physical schema.
- For the first condition only, select the Parent object from the physical schema you selected.
- Select the join column in the Parent object.
- In the Action bar, select Done.
Do not create a self join for an Incorta Analyzer table or Incorta SQL table.
Edit a join condition
- Access the Join Editor for a given relationship.
- For the condition you want to edit, make the necessary updates as applicable.
- In the Action bar, select Done.
For join conditions besides the first condition, you cannot change the selected physical schema, Child object, or Parent object.
Remove a join condition
- Access the Join Editor for a given join relationship.
- For the condition you want to remove, in the upper-right corner, select x.
- In the dialog, select Delete.
You cannot remove the first condition in a join.
Join Editor actions for join filters
With the Join Editor, you can perform the following actions for join filters:
Add a join filter
- In the Join Editor, do one of the following:
- Select +New → Filter.
- In the Filters section, in the upper-right corner, select +.
- Specify the filter details:
- In Column, select a column from the Parent object in the join to filter its data depending upon the operator and value you enter.
- In the case you select First Version or Last Version for the Operator, select the column you want to group the Parent object data by.
- In Operator, select the operator for the filter expression.
- In Value, enter the value to compare the column data to.
- In the case you select First Version or Last Version for the Operator, select the column you want to retrieve the row with the lowest or highest value for each group, respectively.
- In the case you select IN for the Operator, you can enter multiple values separated by commas without additional spaces.
- In Column, select a column from the Parent object in the join to filter its data depending upon the operator and value you enter.
- In the Action bar, select Done.
Edit a join filter
- Access the Join Editor for a given relationship.
- For the filter that you want to edit, make the necessary updates.
- In the Action bar, select Done.
Remove a join filter
- Access the Join Editor for a given join relationship.
- For the filter that you want to remove, select x.
- In the dialog, select Delete.