| Navigation: Language Reference > 4 - Entity Declarations > View Structures >====== JOIN (declare a “join” operation) ====== |    |
JOIN( | secondary key ,linking fields| ) [, INNER ]
| secondary file ,expression |
[PROJECT( )]
[JOIN( )
[PROJECT( )]
END]
END
| JOIN | Declares a secondary file for a relational “Join” operation. |
| secondary key | The label of a KEY which defines the secondary FILE and its access key. |
| linking fields | A comma-delimited list of fields in the related file that contain the values the secondary key uses to get records. |
| secondary file | The label of the secondary FILE. |
| expression | A string constant containing a single logical expression for joining the files (PROP:JoinExpression or PROP:SQLJoinExpression). This expression may include any of the logical and Boolean operators. |
| INNER | Specifies an “inner join” instead of the default “left outer join”–the only records retrieved from the VIEW's primary file parent are those with at least one related record in the JOIN's secondary file. |
| PROJECT | Specifies the fields from the secondary related file specified by a JOIN structure that the VIEW will retrieve. If omitted, all fields from the file are retrieved. |
The JOIN structure declares a secondary file for a relational “Join” operation. A relational “Join” retrieves data from multiple files, based upon the relationships defined between the files. There may be multiple JOIN structures within a VIEW, and they may be nested within each other to perform multiple-level “Join” operations.
The secondary key defines the access key for the secondary file. The linking fields name the fields in the file to which the secondary file is related, that contain the values used to retrieve the related records. For a JOIN directly within the VIEW, these fields come from the VIEW's primary file. For a JOIN nested within another JOIN, these fields come from the secondary file of the JOIN in which it is nested. Non-linking fields in the secondary key are allowed as long as they appear in the list of the key's component fields after all the linking fields.
When data is retrieved, if there are no matching secondary file records for a primary file record, blank or zero values are supplied in the fields specified in the PROJECT. This type of relational “Join” operation is known as a “left outer join.”
The expression parameter allows you to join files which contain related fields but no keys defined for the relationship. PROP:JoinExpression and PROP:SQLJoinExpression are array properties whose the array element number references the ordinal position of the JOIN in the VIEW to affect. PROP:SQLJoinExpression is an SQL-only version of PROP:JoinExpression. If the first character of the expression assigned to PROP:JoinExpression or PROP:SQLJoinExpression is a plus sign (+) the new expression is concatenated to the existing join expression.
Example:
Customer FILE,DRIVER('Clarion'),PRE(Cus) !Declare customer file layout
AcctKey KEY(Cus:AcctNumber)
Record RECORD
AcctNumber LONG
OrderNumber LONG
Name STRING(20)
END
END
Header FILE,DRIVER('Clarion'),PRE(Hea) !Declare header file layout
AcctKey KEY(Hea:AcctNumber)
OrderKey KEY(Hea:AcctNumber,Hea:OrderNumber)
Record RECORD
AcctNumber LONG
OrderNumber LONG
Total DECIMAL(11,2) !Total cash paid
Discount DECIMAL(11,2) !Discount amount given
OrderDate LONG
END
END
Detail FILE,DRIVER('Clarion'),PRE(Dtl) !Declare detail file layout
OrderKey KEY(Dtl:AcctNumber,Dtl:OrderNumber)
Record RECORD
AcctNumber LONG
OrderNumber LONG
Item LONG
Quantity SHORT
END
END
Product FILE,DRIVER('Clarion'),PRE(Pro) !Declare product file layout
ItemKey KEY(Pro:Item)
Record RECORD
Item LONG
Description STRING(20)
Price DECIMAL(9,2)
END
END
ViewOrder1 VIEW(Header) !Declare VIEW structure
PROJECT(Hea:AcctNumber,Hea:OrderNumber)
JOIN(Dtl:OrderKey,Hea:AcctNumber,Hea:OrderNumber) !Join Detail file
PROJECT(Dtl:ItemDtl:Quantity)
JOIN(Pro:ItemKey,Dtl:Item) !Join Product file
PROJECT(Pro:Description,Pro:Price)
END
END
ViewOrder2 VIEW(Customer) !Declare VIEW structure
JOIN(Header,'Cus:AcctNumber = Hea:AcctNumber AND ' & |
' (Hea:Discount + Hea:Total) * .1 > Hea:Discount')
PROJECT(Hea:AcctNumber,Hea:OrderNumber)
JOIN(Dtl:OrderKey,Hea:AcctNumber,Hea:OrderNumber) !Join Detail file
PROJECT(Dtl:ItemDtl:Quantity)
END
END
See Also: