Having trouble creating a view by joining 2 objects?

Body

In the Database Configuration application have you ever tried to join two objects to create a view using the 'Join to object' field and received the error

When you use the 'Join to object' field, the application will try to find matching attributes in the 'Extends object' and the 'Join to object' objects and will automatically update the 'View where' field. So why do you receive this error when you know both objects have the attribute assetnum? This is because the rules we use to determine how to join the two tables were not satisfied. I will try to explain these rules to make it easier for you to decide if this functionality will meet your needs. If this functionality does not meet your needs there are other options for creating views which we will discuss in the next series of blogs.

The objects defined in the fields 'Extends object' and 'Join to object' must satisfy one of the following rules in order to know how the two objects should be joined.

Both objects have the same PRIMARYKEYCOLSEQ columns

So what does it mean 'the same'? First, the objects you are joining, must have the same number of PRIMARYKEYCOLSEQ columns. Second, each attribute of the PRIMARYKEYCOLSEQ from the 'Extends object' must have a corresponding attribute from the 'Join to object' that satisfy the following:

The attributes have the same maxtype, length and scale.

Also one of the following rules must be true:

• The attribute name from the 'Extends object' is the exact same name as the attribute name from the 'Join to object'
• The attribute from the 'Extends object' is a sameas of the attribute from the 'Join to object'
• The attribute from the 'Join to object' is a sameas of the attribute from the 'Extends object'
• The attributes from the 'Join to object' and 'Extends object' have the equivalent sameas on to another object

Every PRIMARYKEYCOLSEQ must match; it is not good enough if only some match.

If the PRIMARYKEYCOLSEQ attributes are not a match then we examine the indexes of both objects.

At least one index on each object must have the same columns

Each object may have more than one index but only one index needs to match. However, every attribute of an index must match, it is not good enough to have some of the attributes of an index match. As with the PRIMARYKEYCOLSEQ rule, the index must have the same number of attributes. Each attribute of the index from the 'Extends object' must have a corresponding attribute from the 'Join to object' that satisfy the following:

The attributes have the same maxtype, length and scale.

Also one of the following rules must be true:

• The attribute name from the 'Extends object' is the exact same name as the attribute name from the 'Join to object'
• The attribute from the 'Extends object' is a sameas of the attribute from the 'Join to object'
• The attribute from the 'Join to object' is a sameas of the attribute from the 'Extends object'
• The attributes from the 'Join to object' and 'Extends object' have the equivalent sameas on to another object

If 'Extends object' and the 'Join to object' do not have an equivalent index then we check to see if the UniqueID columns are the same.

The same UniqueID column

This rule will rarely be satisfied. An index on the UniqueID column typically exists for each object. Therefore the rule of matching an index will be satisfied. In the event the UniqueID is not the only column on an index then this rule will be evaluated. As in the two rules above the following must to be true:

The UniqueID columns from each object must have the same maxtype, length and scale.

Also one of the following rules must be true:

• The UniqueID attribute name from the 'Extends object' is the exact same name as the UniqueID attribute name from the 'Join to object'
• The UniqueID attribute from the 'Extends object' is a sameas of the UniquiID attribute from the 'Join to object'
• The UniqueID attribute from the 'Join to object' is a sameas of the UniquiID attribute from the 'Extends object'
• The UniqueID attributes from the 'Join to object' and 'Extends object' have the equivalent sameas on to another object

If one of the three rules is satisfied then the ‘View where’ field will be automatically updated, joining the matching attributes.