Join in siebel

Today I would like to share with everyone my understanding on what a join is. There are two types of join 

• 1) Implicit Join
• 2) Explicit Join

Implicit Joins- These are joins that exist in siebel and is pre-configured. These are not custom joins and not user created. Eg. Joins with the party table and its extension tables are implicit joins(S_PARTY and S_ORG_EXT). These joins will not have a join specification.

Explicit Joins - These are custom joins and user created. The concept of explicit joins can be better explained with the help of an example. Suppose you have an Account in Siebel and if all account has SSN which is stored in SSN table. For creating a join you store the primary key of the SSN table in the account table and you pull SSN for each account using the join.

Eg

Account(S_ORG_EXT)                                            SSN(S_SSN_TABLE)

Field - SSN(Stores row id of SSN record)           

The field SSN in Account BC stores the row id of the SSN record.

Now we can create a Join in the Account BC. Firstly we create a field SSN UI which displays the SSN numbers. Secondly we create a JOIN to S_SSN_TABLE. In the join specify the table to which we need to join in our case its S_SSN_TABLE. You can give any name as Alias name. Lastly we need to create join specification and it is here we specify the condition of  the join. We have the source field where we specify the field that stores the Primary Key of the table that needs to be joined. For our scenario its SSN and the destination Column is ROW_ID. Now in the UI each contact will have an SSN if the join is valid and the SSN will be fetched using the join

Join Constraint In Siebel

A join may be used to fetch values from a table other than the base table of a BC. Now if the join returns more than a single record then the data will not be displayed in the UI. Join constraint enables us to filter the records returned from a join.

For eg we have a BC  whose base table  is S_PARTY and the field is joined to S_PARTY_PER (Explicit join) and the join return more than one records. We can create a join constrain in which we can specify the Destination Column which will be a column in the joined table and in the Value field we can right an expression that would be compared against the destination column and the join will now return records that matches the constraint.