Skip to main content
insightsoftware Documentation insightsoftware Documentation
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

Manage Joins

In the Noetix Views Workbench (NoetixViews Workbench), you can add, edit, or suppress Noetix-defined and custom joins that are based on standard or rowid columns. You can delete custom joins but not Noetix-defined joins. The join feature is not available for list of values (LoV) and base views.

You can manage joins by using the Modify View » <view label> » Add, modify keys and joins page of the NoetixViews Workbench. The following information about existing joins is available under the Joins to other views areaof the page:

  • Join Name: Indicates the unique name provided for the join.
  • Type: Indicates the type of the column based on which the join is defined; the possible values are "Standard" and "Rowid".
  • Master View.Key Name:Displays the names of the master view and master key used in the join.
  • Join: Indicates the join condition; the possible values are "Outer on Master", "Equi-join", and "Outer on Detail"
  • View Modifier: Indicates the name appended to the master view name in the presentation layer of your business intelligence (BI) tool.
  • Suppressed: Indicates the suppression status of the join; the possible values are "Yes" and "No".
  • Updated By: Indicates the name of the user who last updated the join.

NOTE: If you want to sort the information on the basis of a column, click the column label. You can also use the boxes under each column label to filter the information based on the specific value of the column.

The following topics describe the procedures related to managing standard or rowid column-based joins:

About Standard Column-Based Joins

Such joins make use of standard column-based master keys. Multiple standard column-based joins are possible between two views. A standard column-based join can have a cardinality of one-to-one or one-to-many.

For example, consider the join between the XLA SLA Hub Journal Lines and XLA AP Invoice Dist views. The XLA SLA Hub Journal Lines view has a composite master key that consists of the Accounting Event Header ID, Accounting Event Line Number, and Subledger Application ID columns. The XLA AP Invoice Dist view has a composite foreign key that consists of the Accounting Event Header ID, Accounting Event Line Number, and Acct Event Application ID columns. In this example, the join conditions can be defined as follows:

  • XLA SLA Hub Journal Lines.Accounting Event Header ID = XLA AP Invoice Dist.Accounting Event Header ID

  • XLA SLA Hub Journal Lines.Accounting Event Line Number = XLA AP Invoice Dist.Accounting Event Line Number

  • XLA SLA Hub Journal Lines.Subledger Application ID = XLA AP Invoice Dist.Acct Event Application ID

About Rowid Column-Based Joins

Such joins make use of rowid column-based master keys. For such joins, every active query of the detail view must have the table that is referenced by the rowid column-based master key in the master view. The table can also come from a base view for the detail view. The detail view has a foreign key that will use the Z$<master view label> format. Only one rowid column-based join is possible between two views. A rowid column-based join will always have a cardinality of one-to-one. When a rowid column-based join is established, a foreign key named Z$<master view label> is added to the detail view.

For example, consider the AP Invoices and AP Invoice Payments views. The AP Invoices view has a rowid column-based master key called Z$AP Invoices that is based on the rowid column of the AP INVOICES ALL table. The AP Invoice Payments view has a foreign key column called Z$AP Invoices that is also based on the rowid column of the AP INVOICES ALL table. In this example, the join condition can be defined as follows: AP Invoices.Z$AP Invoices= AP Invoice Payments.Z$AP Invoices.

Published:

Manage Joins

In the Noetix Views Workbench (NoetixViews Workbench), you can add, edit, or suppress Noetix-defined and custom joins that are based on standard or rowid columns. You can delete custom joins but not Noetix-defined joins. The join feature is not available for list of values (LoV) and base views.

You can manage joins by using the Modify View » <view label> » Add, modify keys and joins page of the NoetixViews Workbench. The following information about existing joins is available under the Joins to other views areaof the page:

  • Join Name: Indicates the unique name provided for the join.
  • Type: Indicates the type of the column based on which the join is defined; the possible values are "Standard" and "Rowid".
  • Master View.Key Name:Displays the names of the master view and master key used in the join.
  • Join: Indicates the join condition; the possible values are "Outer on Master", "Equi-join", and "Outer on Detail"
  • View Modifier: Indicates the name appended to the master view name in the presentation layer of your business intelligence (BI) tool.
  • Suppressed: Indicates the suppression status of the join; the possible values are "Yes" and "No".
  • Updated By: Indicates the name of the user who last updated the join.

NOTE: If you want to sort the information on the basis of a column, click the column label. You can also use the boxes under each column label to filter the information based on the specific value of the column.

The following topics describe the procedures related to managing standard or rowid column-based joins:

About Standard Column-Based Joins

Such joins make use of standard column-based master keys. Multiple standard column-based joins are possible between two views. A standard column-based join can have a cardinality of one-to-one or one-to-many.

For example, consider the join between the XLA SLA Hub Journal Lines and XLA AP Invoice Dist views. The XLA SLA Hub Journal Lines view has a composite master key that consists of the Accounting Event Header ID, Accounting Event Line Number, and Subledger Application ID columns. The XLA AP Invoice Dist view has a composite foreign key that consists of the Accounting Event Header ID, Accounting Event Line Number, and Acct Event Application ID columns. In this example, the join conditions can be defined as follows:

  • XLA SLA Hub Journal Lines.Accounting Event Header ID = XLA AP Invoice Dist.Accounting Event Header ID

  • XLA SLA Hub Journal Lines.Accounting Event Line Number = XLA AP Invoice Dist.Accounting Event Line Number

  • XLA SLA Hub Journal Lines.Subledger Application ID = XLA AP Invoice Dist.Acct Event Application ID

About Rowid Column-Based Joins

Such joins make use of rowid column-based master keys. For such joins, every active query of the detail view must have the table that is referenced by the rowid column-based master key in the master view. The table can also come from a base view for the detail view. The detail view has a foreign key that will use the Z$<master view label> format. Only one rowid column-based join is possible between two views. A rowid column-based join will always have a cardinality of one-to-one. When a rowid column-based join is established, a foreign key named Z$<master view label> is added to the detail view.

For example, consider the AP Invoices and AP Invoice Payments views. The AP Invoices view has a rowid column-based master key called Z$AP Invoices that is based on the rowid column of the AP INVOICES ALL table. The AP Invoice Payments view has a foreign key column called Z$AP Invoices that is also based on the rowid column of the AP INVOICES ALL table. In this example, the join condition can be defined as follows: AP Invoices.Z$AP Invoices= AP Invoice Payments.Z$AP Invoices.

For an optimal Community experience, Please view on Desktop
Powered by Zendesk