Typical concepts of Joins

View previous topic View next topic Go down

Typical concepts of Joins

Post  munav on Thu Feb 25, 2010 4:03 am

Can we create more than one Join Specification in JOIN condition ? If yes, what scenario.
What is the importance of Join Constraint ? Give a scenario.
Is Join field Read-Only? How can you edit a Join field ?
Can you create join to the same table the BC is based on ? Give a scenario.
Is it possible to have Static picklist on a join field ?
What is a implicit join ? Give examples ?
What happens if Outer Join Flag is not checked in a Join ?

munav
Guest


Back to top Go down

Re: Typical concepts of Joins

Post  Ashok KL on Thu Feb 25, 2010 12:41 pm

Yes we can create more than one join specification in one join.

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

Ashok KL
Guest


Back to top Go down

Can we create more than one Join Specification in JOIN condition ? If yes, what scenario ?

Post  Admin on Thu Feb 25, 2010 5:27 pm

Can we create more than one Join Specification in JOIN condition ? If yes, what scenario ?

Yes, it is possible to have more than one join specification in a join provided that the Source fields in join specifications must come from the same table.

The join specifications can be based on foreign key columns from the source table (the BC table) or joined fields in the source BusComp (different table), provided that all join specifications have source fields coming from one single table.

Scenario: We can create a separate join definition to S_ORG_EXT in Opportunity BusComp which has multiple join specifications using 'Joined Account Id' and "Account Location' as source fields. These source fields are actually joined fields brought over from the default join definition to S_ORG_EXT. If we instead use 'Account Id' and 'Account Location' then 'Account Id' being the Source Field based on base column in Opportunity BusComp, the join will not work and similar error will be displayed stating the join has source fields coming from tables S_OPTY and S_ORG_EXT
avatar
Admin
Admin

Posts : 21
Join date : 2010-02-25

View user profile http://siebelwithmunav.dodiscussion.com

Back to top Go down

Re: Typical concepts of Joins

Post  Admin on Fri Feb 26, 2010 3:07 am

What is the importance of Join Constraint ? Give a scenario.

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

Below properties needs to be populated for Join Constraint:
Destination Column: Column of the destination table
Name: Any Name
Value: A constant value or constant function, such as LoginId(), PostionId(). GetProfileAttr can also be used, but this property must contain a value before the join fires. Otherwise null is returned and the join will not work.

Join Constraint is applied over Join Specification to restrict the Joined field value. Lets take an example where user should be able to see the Service Request Information in Service Request related fields coming from a Join in Action BC (Activity List Screen) if that Service Request is owned by the user. We will create a Join Constraint as follow:

Destination Column: OWNER_EMP_ID
Value: LoginId()

If user is owner of Service Request attached to Activity, all the fields coming from this join will show as populated. If user is not owner of Service Request attached to the Activity, all the fields coming from this join will show as blank.

Will the Join work if Join Specification is not defined but only Join Constraint is defined ?

No. Join will not work until the Join Specification is defined.

Why do we need to check Outer Join Flag checked when using Join Constraints ?

If we do not check the Outer Join Flag checked, the rows in parent BC which does not satisfy the Join Constraint condition will also be filtered which is not desirable. Just take the same example as mentioned above. In this case, Activities which user has access to but have Service Request in which user is not the owner then those Activities will not show in the UI which should not happen.
avatar
Admin
Admin

Posts : 21
Join date : 2010-02-25

View user profile http://siebelwithmunav.dodiscussion.com

Back to top Go down

Re: Typical concepts of Joins

Post  Admin on Mon May 10, 2010 6:18 pm

Is Join field Read-Only? How can you edit a Join field ?

Yes, the field based on a join becomes read-only. To update a field based on a join, the field record in the BC should be attached to the PickList. Interestingly, any picklist (even the generic) attached to the field makes it editable but the Join field value is populated only when the source field of the Join is populated correctly. Hence, picklist and pickmap both needs to be properly setup.

Take example of Account in Service Request BC. Create a join if not already exist, S_ORG_EXT with source field Account Id. Create a field Account_X with join = S_ORG_EXT and column as NAME. Expose Account_X in Applet and compile the changes and check in the UI. Account_X field is read-only even when read only property at BC field level and Applet control or list column level is set to false. Now, select a picklist in BC > Fields and setup a pickmap to populate Account (the main field) and Account Id (the source field in the join). Compile changes and check in UI. Field is editable but only value which is available in the picklist can be set.
avatar
Admin
Admin

Posts : 21
Join date : 2010-02-25

View user profile http://siebelwithmunav.dodiscussion.com

Back to top Go down

Re: Typical concepts of Joins

Post  Admin on Mon May 10, 2010 6:36 pm

Can you create join to the same table the BC is based on ? Give a scenario.

Yes and that is called self join in the SQL language. The best example is Action BC. Action and Activity Plan are based on same table but they have child parent relationship like Employee Manager relationship in Employees table. In Action BC, there are many joins to S_EVT_ACT table on which the BC is also based on.
avatar
Admin
Admin

Posts : 21
Join date : 2010-02-25

View user profile http://siebelwithmunav.dodiscussion.com

Back to top Go down

Re: Typical concepts of Joins

Post  Admin on Mon May 10, 2010 7:01 pm

Is it possible to have Static picklist on a join field ?

Yes it is possible to have Static picklist on a join field. Siebel does not validate if the picklist is static or dyanamic. The value is populated based on the pickmap setup for the field.

But it does not make sense to have a static picklist attached to field based on a join. For a join to work, Source field of the join needs to be populated which in turn pulls the necessary value from the joined table. Source field (generally the Id) can be populated by dynamic picklist based on the business component which stored the Source field (Id) and destination column value.
avatar
Admin
Admin

Posts : 21
Join date : 2010-02-25

View user profile http://siebelwithmunav.dodiscussion.com

Back to top Go down

Re: Typical concepts of Joins

Post  Admin on Wed May 12, 2010 11:25 am

What is a implicit join ? How it is different from joins ?

Implicit join is a inbuilt 1:1 relationship between base table and its extension table and it is not required to be defined explicitely in BC > Joins.

Join only allows data to be displayed from joined table (Using picklist only source field of join is updated which is part of base table itself). On the other hand, implicit join allows update of data.

Implicit join is not required to be defined explicitely. The join name appears as the name of extension table in BC fields.

For eg, S_OPTY_X is the extension table for S_OPTY and both have a implicit join.
avatar
Admin
Admin

Posts : 21
Join date : 2010-02-25

View user profile http://siebelwithmunav.dodiscussion.com

Back to top Go down

Re: Typical concepts of Joins

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum