Concepts → Join Filter

About a join filter

A join filter is a filter expression for a Parent entity object in a join. The filter expression functions to filter rows in the Parent entity object.

As such, a join filter often functions to define the uniqueness of Parent entity rows. This is especially helpful when the Parent entity does not have a Key column or the Key column is not specified in the join condition.

In this regard, a join filter is synonymous with the ON clause in a SQL Join statement. Although both the join filter and join condition are synonymous with the ON clause in a SQL statement, the join condition compares the values of the two columns in the join using the condition operator while the join filter compares the value of a column from the Parent entity object with a specified value (except for the First Version and Last Version operators as these behave differently).

Note

A join filter does not affect a self-join, that is, a join where the same entity object is both the child and the parent.

The Loader Service evaluates the join filter expression and creates a direct data map when saving the join details or when loading the related physical schema(s) or entity object(s). The Analytics Service reads the join direct data map for queries that reference the join entities.

The join filter context

You use the Join Editor to manage filters for a given join. You have to be in a group with the Schema Manager or the SuperRole role to access the Schema Manager. You also need to either own the physical schema of the Child object or have Edit access rights to it in order that you can access the Join Editor and manage the physical schema joins.

In the case of a cross-schema join, you need to own both physical schemas or, at least, have Edit access rights to the physical schema of the Child object and View access rights to the physical schema of the Parent object.

Note

A join that you create between entity objects in different physical schemas shows in the Schema Designer for both physical schemas; however, you can manage these joins only by using the Join Editor for the physical schema that has the Child entity object in the join.

The join filter syntax

A join filter expression consists of:

  • a filter column
  • a filter operator
  • a filter value

About the join filter column

For a join filter, you can reference only columns from the Parent entity object. The following are the types of columns that you can reference in a join filter.

  • Physcial_Schema.Table.Column
  • Physcial_Schema.Table.Formula_Column
  • Physcial_Schema.Incorta_Analyzer_Table.Column
  • Physcial_Schema.Incorta_Analyzer_Table.Formula_Column
  • Physcial_Schema.Materialized_View.Column
  • Physcial_Schema.Materialized_View.Formula_Column
  • Physcial_Schema.Alias.Column
  • Physcial_Schema.Alias.Formula_Column
  • Physcial_Schema.Incorta_SQL_Table.Column (including computed columns)

To learn more, review Concepts → Join Filter Column.

Important

It is not recommended to reference a formula column in an entity object in another schema for the join filter column.

About the join filter operator

The following are the available operators to select from for a join filter operator:

  • <: 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 value column for each group in the filter column
  • Last Version: the greatest value in the join filter value column for each group in the filter column
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 following applies:

  • The Loader Service groups data in the Parent entity object by the filter column and sorts data in each group by the join filter value column.
  • In the case of the First Version, the Loader Service retrieves only the row with the lowest (first) value in the join filter value column for each group that has a matching record in the Child entity object.
  • In the case of the Last Version, the Loader Service retrieves only the row with the greatest (last) value in the join filter value column for each group that has a matching record in the Child entity object.

About the join filter value

For a join filter value, you can use one of the following depending upon the operator that you select:

  • a scalar value for comparison operators (<, ≤, =, ≠, >, ≥)
  • an array of scalar values for the IN operator
  • a column in the Parent entity object only in the case of First Version and Last Version operators
Note

In addition to numeric and date scalar values, you can use comparison operators with string or text values. In such a case, the join filter column is sorted in an alphabetical order, and then compared to the entered value. For example, when you enter the following filter expression: State < Colorado, all cities that precede Colorado in the alphabetically ordered list will be retrieved; that is, Alabama, Alaska, Arizona, Arkansas, and California.

Important

A date needs to be in the same date format as the filterable column. In the Table Editor, you can preview the column details and view the sample data in order to discern the date format.

Important

You can reference a date system variable for the filter value. However, the technique serves only to capture a snapshot of the system variable value.The Loader Service evaluates the value when saving changes to a join in the Join Editor, during the load job for the entity object, or during the load job for the related physical schema.

Examples of join filters

The following table shows the available join filter operators, the supported values, and the effect of this filter on the rows retrieved from the Parent entity object after applying the join condition(s).

Column Operator Value Result
Age < 50 All rows from the Parent table that have a matching for the join column(s) and where the Age column is less than 50 are retrieved.
Position ≤ SW Engineer All rows from the Parent table that have a matching for the join column(s) and where the Position column is less than or equal to SW Engineer are retrieved.
Department = IT All rows from the Parent table that have a matching for the join column(s) and where the Department column is equal to IT are retrieved.
Country ≠ USA All rows from the Parent table that have a matching for the join column(s) and where the Country column is not equal to USA are retrieved.
State > California All rows from the Parent table that have a matching for the join column(s) and where the State column is greater than California are retrieved.
Salary ≥ 1000 All rows from the Parent table that have a matching for the join column(s) and where the Salary column is greater than or equal to 1000 are retrieved.
City IN Cairo,London,Boston All rows from the Parent table that have a matching for the join column(s) and where the City column is Cairo, or London, or Boston are retrieved.
Position First Version Salary From the Parent table, only the row with the lowest Salary per Position that has a matching record in the Child table is retrieved.
Department Last Version Hire_Date From the Parent table, only the row with the latest (greatest) Hire_Date per Department that has a matching record in the Child table is retrieved.

Example of the Equivalent SQL Statement

The following is an example of the SQL statement equivalent to a join with one condition and a filter. Consider the sch_HR physical schema with the tbl_employees and tbl_departments tables. A join created with the following condition and filter details:

Join Element Details
Condition sch_HR.tbl_employees.DepartmentID = sch_HR.tbl_departments.DepartmentD
Filter sch_HR.tbl_departments.DepartmentName IN ("HR","Sales")
SQL Statement SELECT sch_HR.tbl_employees.Name, sch_HR.tbl_departments.DepartmentName FROM sch_HR.tbl_employees LEFT OUTER JOIN sch_HR.tbl_departments ON sch_HR.tbl_departments.DepartmentName IN (“HR”,”Sales”) AND sch_HR.tbl_employees.DepartmentID = sch_HR.tbl_departments.DepartmentID
Query Result A list with all employees from the Child table (tbl_employees). For each row in the Child table where the department is “HR” or “Sales”, the department name is retrieved from the Parent table (tbl_departments); otherwise, the DepartmentName column will be Null.

Additional Considerations

Adding a join filter to a join is different from a SQL statement that has a filter expression in a WHERE clause or an INNER JOIN.

In a physical schema, a join between Child and Parent entity objects is a Left Outer Join. In a SQL statement, the WHERE clause defines a filter expression that removes rows after the join operation. Because a physical schema join is a Left Outer Join, it is not possible to push the predicate into the join itself as it would be possible with an Inner Join.

Related Links
© Incorta, Inc. All Rights Reserved.