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.

Note

This document captures the new user interface (UI) of the Join Editor introduced in the 2022.3.0 release.

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.

Warning

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.

Important

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 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.

Important

Starting the 2021.4.1 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:

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 + NewJoin.
  • 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.
Note

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:

  • 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 + NewJoin (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 + NewJoin (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.

Note

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)
  • 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:

ElementDescription
Physical schemaDefaulted to the current physical schema and you cannot change it.
Child ObjectSelect an 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
ColumnTypically, 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.
Important

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.

Important

It is not recommended to reference a formula column in an 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
Important

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 object in the same physical schema
  • An object in another physical schema (cross-schema join)
Important

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:

ElementDescription
Physical schemaDefaulted to the current physical schema and you can select another physical schema for the first condition only.
Parent ObjectSelect an 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
ColumnTypically, the Primary Key in the Parent object; however, it can be any column. In both cases, it must be of the same (or similar, though this is not recommended) data type as the column in the Child object. It is not recommended to reference a formula column in an object in another physical schema for the join condition column.
To learn more about join condition columns, review Concepts → Join Condition Column.

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
Important

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.

Important

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 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.
Note

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 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:

  • 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.
Warning

Do not create a self-join for an Incorta Analyzer table or Incorta SQL table.

Edit a join condition

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

For join conditions besides the first condition, you cannot change the selected physical schema, Child object, or Parent object.

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.
Note

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, 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.