Tools → Join Editor

About the Join Editor

The Join Editor allows you to create and manage Left-Outer Join relationships between two physical schema objects: a Child object and a Parent object. Physical schema objects include physical schema tables, aliases, Incorta Analyzer tables, Incorta SQL tables, and materialized views.

The join can be between objects 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 objects allows you to reference columns from multiple objects in one query and get relevant data sets. For an object in a physical schema, you can add multiple joins as Parent or as Child.

The Join Editor allows you to create joins between 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.

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.
• In the Joins tab, select + (Add New Join).
• In the Joins tab, select a join.
• For a given join in the Joins tab, select More Options (⋮ vertical ellipsis), and then select Join Details.

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:

• In the Joins tab, in the Parent Joins section, select + (Add New Join).
• In the Joins tab, in the Child Joins section, select + (Add New 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 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 object in the same physical schema only.

Join Editor anatomy

The Join Editor consists of the following:

• Join Conditions section
• Join Filters section
• Action buttons

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.

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)
• Remove Condition (trash can icon): select to delete the respective condition. This option Is not available in the first 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:

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

Parent

The Parent object in the join can be one of the following:

• The same as the Child object (self-join relationship)
• Another object in the same physical schema
• An object in another physical schema (cross-schema join)

For the Parent side of a join condition, specify the following:

Remove Condition

This option (trash can icon) 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
• Remove Filter (trash can icon): select to delete 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

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 to
• IN: a value from an array of scalar values
• First Version: the lowest value in the join filter column for each group in the filter column
• Last 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 IN operator
• a column in the Parent object only in the case of First Version and Last Version operators.

Remove Filter

This option (trash can icon) is available for each filter in the join. Select it to remove the respective join filter.

Action buttons

The Join Editor has the following action buttons.

• Done: 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.

Join Editor actions for join condition

With the Join Editor, you can perform the following actions for join conditions:

• Add a join condition
• Edit a join condition
• Remove a join condition

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:

• 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.
  • In the case of the first join condition only, select the Child object from the current physical schema.
  • Select the join column in the Child object.
• Select the join operator.
• In the Parent section (the right side of the join),
  • In the case of the first join condition only, select the physical schema where the Parent object exists. This can be the current physical schema or another physical schema.
  • In the case of the first join condition only, select the Parent object from the physical schema you selected.
  • Select the join column in the Parent object.
• Select Done.

Edit a join condition

• For the condition that you want to edit, make the necessary updates as applicable.
• In the Action bar, select Done.

Remove a join condition

• For the condition that you want to remove, in the upper-right corner, select Remove Condition (trash can icon).
• In the dialog, select Delete.
• Select Done.

Join Editor actions for join filters

With the Join Editor, you can perform the following actions for join filters:

• Add a join filter
• Edit a join filter
• Remove a join filter

Add a join filter

• In the Join Editor, 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.
• Select Done.

Edit a join filter

• For the filter that you want to edit, make the necessary updates.
• Select Done.

Remove a join filter

• For the filter that you want to remove, select Remove Filter (trash can icon).
• In the dialog, select Delete.
• Select Done.