Bookshelf v7.5.2: How a Join Is Constructed

Siebel Tools Reference > Business Objects Layer > Joins >

How a Join Is Constructed

The object definition relationships in a join are illustrated in Figure 95.

Figure 95. Join Relationships

The roles of the object definitions in the diagram are summarized as follows:

Business Component object type. The business component is the parent object definition of the join. Because of the join, fields in the business component (called joined fields) can represent columns from the joined table.

Joined field. A joined field in the business component represents a column from a table other than the business component's base table. Therefore, a joined field must obtain its values through a join. A joined field has the name of the join in its Join property. Together the Join property and Column property identify the column and how to access it. When creating a joined field in a business component, you can change the Type property from the default DTYPE_TEXT to a more appropriate type. For example, if you are joining a table column that contains phone numbers, you can change the Type field to DTYPE_PHONE.

Join object type. Join is a child object type of the Business Component object type. The Join object definition uniquely identifies a join relationship for the parent business component and provides the name of the destination (joined) table. The join object definition identifies the joined table in the Table property. The name of the base table is already known to the business component. Typically, a join object definition is given the same name as the joined table.

Join Specification object type. The join specification object definition is a child of the join object definition. It identifies the foreign key field in the business component and the primary key column in the joined table (that the foreign key points to).

The Source Field property identifies the foreign key field in the business component. If left blank, the Source Field is the Id field, indicating a one-to-one relationship between the business component and the joined table. Occasionally, a system field such as Created By or Updated By may be specified as the foreign key field in the Source Field property.

The Destination Column property identifies the primary key column in the joined table. A non-blank Destination Column property value is required if the join occurs on a column other than ROW_ID. A blank value in the Destination Column property means that the destination column is ROW_ID, which is typically the primary key in tables in Siebel applications.

NOTE: In rare circumstances, there can be multiple join specifications in a single join. For example, the Sub Campaign business component has a join to the S_LANG table with two join specifications. In such cases the source fields in the join specifications should be based upon the same table.

Join Constraints. A join constraint is a constant-valued search spec applied to a column during a join. It is for use with outer joins.

NOTE: Set the Outer Join Flag to TRUE if you want to retrieve all the records in the business component even when the joined fields are empty.

Foreign key (source) field and foreign key column. The foreign key field is identified in the Source Field property of the join specification. It represents a foreign key column in the base table, pointing to rows in a particular table used in joins. For example, in the Contact business component, the foreign key field to the join on accounts data is the Account Id field, which represents the PR_DEPT_OU_ID column in the base table.

Joined table. The joined table is the master table in the master-detail relationship. It provides columns to the business component through the join. The joined table is identified in the Table property of the Join object definition.

NOTE: When configuring a recursive or self join, the Alias name of the joins must be different than the Table Name. Using the same name will result in the following error message: "Table `T1' requires a unique correlation name." This error is often due to a faulty recursive or self join definition.

Primary key (destination) column. The join specification identifies the primary key column in the joined table (in the Destination Column property). Every standard table in standard Siebel applications has a ROW_ID column that uniquely identifies rows in the table. ROW_ID is the destination in most joins.

Mapped column. Columns in the joined table are available for use in fields in the business component.