JOIN (declare a "join" operation)

Top  Previous  Next

 

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:

INNER