JOIN

Defines the compound data set used for multiple data set retrieval.

Syntax


   J[OIN] data item equivalence [,data item equivalence]...

   [;data set equivalence [,data set equivalence]...] [END]

Parameters

data item equivalence

has the form:


   [data base name:] data set name. data item name

   [(subscript)] [@] TO [@] [data base name:]

   data set name. data item name [(subscript)]

data base name is the name of a data base specified in either the DEFINE, DATA-BASE=, or MULTIDB command.

data set name is the name of a data set that is to be included in the compound data set being specified. If data base name is used, the data set must belong to that data base.

data item name is the name of the data item that links the joined data sets together. The data item must belong to the specified data set.

subscript is a number used to indicate which sub-item you want to join. Subscript must be an integer >= 1 and <= the number of sub-items defined for the compound item. QUERY will default to the first sub-item if no subscript is specified.

@ means "preserve all values of" the data set associated with the @ sign. This parameter is explained in detail later in "Using the @ Parameter".

data set equivalence

has the form:


   dummy data set name = data set name

dummy data set name is a temporary name for the data set named on the right side of the data set equivalence. A dummy data set cannot be a legal data set name in any open data base.

END

may be used to end a JOIN command.

Discussion

The JOIN command allows the retrieval and reporting of data item values from multiple data sets by creating a logical relation between data sets. The relation is established by equating a data item from one set with a data item from a second data set. These data item equivalences, of which QUERY allows up to 52, define a compound data set.

Once you have defined a compound data set with the JOIN command, you must enter a MULTIFIND or a MULTIFIND ALL command to actually create the compound data set and access the desired items. Refer to the MULTIFIND or MULTIFIND ALL commands in this manual for details on their operation. You can only report from those data sets used in the JOIN command.

A JOIN command will remain in effect until another JOIN command, valid or invalid, is entered. Each subsequent JOIN command also clears the previous select file. For example, suppose you want to make a sales report that shows information from four data sets: the name of the product, the quantity sold of that product, the quantity on hand, and the date of sale. To access all this information, define your data base with a DEFINE, DATA BASE=, or MULTIDB command and enter the JOIN command.


      >JOIN PRODUCT.STOCK# TO SALES.STOCK#,&
      >>    SALES.STOCK# TO INVENTORY.STOCK#
      >MULTIFIND PRODUCT.DESCRIPTION = NAIL
      USING SERIAL READ
      2 COMPOUND ENTRIES QUALIFIED

In order for the JOIN command to be valid, all data sets used in the JOIN command must be logically connected to one another so that each data set is traceable to every other data set through the data item equivalences. If logical connections cannot be traced between data sets, the following message is returned and the JOIN does not take place.


      LOGICAL CONNECTIONS ARE
      INCOMPLETE, COMPOUND
      DATA SET CANNOT BE GENERATED

In the example above, the data set SALES is common to both PRODUCT and INVENTORY, and a data equivalence is formed with the data item STOCK#. However, the data items that link the data sets need not have the same name or be of the same data type.

The MULTIFIND PRODUCT.DESCRIPTION=NAIL command created the compound data set from which retrieved entries can be reported. To produce a sales report for the NAIL product, you can enter the REPORT command with the desired header and detail statements from your terminal or you can use a procedure file. The REPORT command is shown on the next page.


      >REPORT SALESPROC,A 

      REPORT
      H1,"Report on Sales",43,SPACE A2
      H2,"Stock",7
      H2,"Product",24
      H2,"Quantity",39
      H2,"Quantity",56
      H2,"Date",70
      H3,"No.",7
      H3,"Description",24
      H3,"Sold",39
      H3,"in Stock",56
      H3,"Sold",70,SPACE A2
      D1,PRODUCT.STOCK#,6
      D1,PRODUCT.DESCRIPTION,24
      D1,SALES.QUANTITY,39
      D1,INVENTORY.ONHANDQTY,56
      D1,SALES.PURCH-DATE,70,E1
      E1,"XX/XX/XX"
      END

                              Report on Sales

    Stock          Product       Quantity         Quantity          Date
      No.      Description           Sold         In Stock          Sold


      50              NAIL            130             1200      85/01/12
      50              NAIL              5             1200      85/01/12

The compound data set was formed by matching entries from the data sets PRODUCT, INVENTORY, and SALES that have equal values for connecting data items named in the data item equivalence. In this example, STOCK# is the only equated data item for all data sets.

If a value for the data item named in the data item equivalence occurs more than once in one of the data sets named in the equivalence, and at least once in the other data set named, this entry will be repeated in the compound data set. Given the following data sets and a data item equivalence using upper-limit and price, the name JOHN SMITH will appear twice in the compound data set.


                CLIENT                                 SELLER
      ------------------------------       -----------------------------
           NAME     UPPER LIMIT $                 NAME         PRICE
      ------------------------------       -----------------------------
        JOHN SMITH     $1000                   MARY SMITH      $1000
                                             GEORGE BROWN      $1000
      ------------------------------       -----------------------------

      >JOIN CLIENT.UPPER-LIMIT TO SELLER.ASKING-PRICE
      >MULTIFIND ALL
      2 COMPOUND ENTRIES QUALIFIED
      >REPORT ALL

      SALES:  CLIENT
      NAME            =JOHN SMITH
      UPPER-LIMIT     =$1000

      SALES:  SELLER
      NAME            =MARY SMITH
      ASKING-PRICE    =$1000

      SALES:  CLIENT
      NAME            =JOHN SMITH
      UPPER-LIMIT     =$1000

      SALES:  SELLER
      NAME            =GEORGE BROWN
      ASKING-PRICE    =$1000

Data Item Types

Equated (joined) data items can be of different types and lengths, with the exception of character types, which may only be equated to other character types. If two items of different lengths are equated, QUERY treats the shorter one as if it were padded with blanks to match the length of the longer data item.

Null Connecting Data Items

The IMAGE subsystem initializes numeric type data items to zero, and character type data items to ASCII null. When data items are equated with a relational operator for retrieval, QUERY compares data item values based on their types. When comparing two numeric type data items, QUERY considers the two items equal when both items have a zero value. If the comparison is between character type data items with the values of ASCII null, QUERY does not consider the relationship equal, and no entries are joined.

Zoned Type Data Items

When joining zoned type data items, the zoned numbers with the sign overpunch are considered equal to the equivalent value without an overpunch. For example, 5A is considered equivalent to 51. When QUERY converts a different data type to zoned, it will pad with leading blanks. Leading nulls are not considered equivalent to leading blanks.

Packed Data Type Items

When QUERY converts a different data type to packed, it uses a COBOL Convention, a positive sign of 1100, a negative sign of 1101, and unsigned 1111. Positive and unsigned numbers are considered equivalent.

Data Set Equivalence

Equating data sets is a way of renaming a data set to enable the information contained in it to be used in more than one way. For example, assume you have the following simple data set EMP-DETAIL containing three data entries, and you want to create a REPORT that prints the employee's name and the employee's manager's name:


                                  EMP-DETAIL
      ------------------------------------------------------------------
          EMP-#                    EMP-NAME                   MGR-#
      ------------------------------------------------------------------
          1792                     G. Smith                   1833
          1833                     H. Jones                   3421
          3421                 J. President                   ****
      ------------------------------------------------------------------

In this data set, the data item MGR# identifies both an employee and an employee's manager. For example, MGR# 1833 identifies G. Smith's manager (H. Jones) and is at the same time H. Jones employee number.

To create the desired report, the data set EMP-DETAIL must be equated to a dummy data set, and these two data sets connected with a JOIN command, to specify a compound data set from which the report can be generated.

The dummy data set MGR-DETAIL is a temporary name to which the existing data set is equated. Note that this equation must be specified after the data item equivalences.


      >JOIN EMP-DETAIL.MGR-# TO MGR-DETAIL.EMP-#;&
      >>   MGR-DETAIL = EMP-DETAIL
      >MULTIFIND ALL
      USING SERIAL READ
      2  COMPOUND ENTRIES QUALIFIED

The data set equivalence above is not actually executed until a MULTIFIND or MULTIFIND ALL command is entered to retrieve some or all of the compound entries you have formed.

Two compound entries have been retrieved from which the desired report can be created.


      >REPORT ALL

      EMP:EMP-DETAIL
      EMP-#             =1792
      EMP-NAME          =G. SMITH
      MGR-#             =1833

      EMP:MGR-DETAIL
      EMP-#             =1833
      EMP-NAME          =H. JONES
      MGR-#             =3421

      EMP:EMP-DETAIL
      EMP-#             =1833
      EMP-NAME          =H. JONES
      MGR-#             =3421

      EMP:MGR-DETAIL
      EMP-#             =3421
      EMP-NAME          =J. PRESIDENT
      MGR-#             =****

Below is another report showing employee and manager names:


      >REPORT
      >>H1,"Employee Name",30
      >>H1,"Manager Name",55,SPACE A2
      >>D1,EMP-DETAIL.EMP-NAME,27
      >>D1,MGR-DETAIL.EMP-NAME,55
      >>END



                   Employee Name             Manager Name


                     G. SMITH                    H. JONES
                     H. JONES                J. PRESIDENT

Using the @ Parameter

The optional @ parameter allows you to preserve all of the data item values in one of the data sets of a data item equivalence. The values in the data set which is next to the @ (on the same side of the TO as the @) will be preserved when data sets are joined even if a corresponding data item value for the equated data items does not exist in the second data set. For example, given the following data sets:


               SALES-DETAIL                        STOCK-DETAIL
      -----------------------------      -------------------------------
        ACCT-#    STOCK-#    QUAN          STOCK-#    DESCR    ON-HAND
      -----------------------------      -------------------------------
        666         90       350            110         NUT      970
        222         60        25             60        BOLT     1200
      -----------------------------      -------------------------------

The next JOIN command produces the following compound data set:


        >JOIN SALES-DETAIL.STOCK# TO STOCK-DETAIL.STOCK#

   <--------(SALES DETAIL)--------> <---------(STOCK-DETAIL)--------->
   -----------------------------------------------------------------------
     ACCT-#    STOCK-#     QUAN           STOCK-#     DESCR      ON-HAND
   -----------------------------------------------------------------------
      222        60         25              60         BOLT        1200
   -----------------------------------------------------------------------

If you want to keep all of the entries in the SALES-DETAIL data set, use the @ parameter. Placing the @ sign on the SALES-DETAIL side of the TO ensures that all entries for the SALES-DETAIL data set will be included in the resulting compound data set even if there is no corresponding value for STOCK# in the STOCK-DETAIL data set. For example, the JOIN below produces the following compound data set.


     >JOIN SALES-DETAIL.STOCK# @ TO STOCK-DETAIL.STOCK#

      <-------(SALES DETAIL)-------><---------(STOCK-DETAIL)-------->
      ------------------------------------------------------------------
        ACCT-#    STOCK-#    QUAN          STOCK-#    DESCR    ON-HAND
      ------------------------------------------------------------------
         666        90       350              *        ****        *
         222        60        25             60        BOLT     1200
      ------------------------------------------------------------------

The compound entry STOCK# = 90 is included, but there is no STOCK# = 90 in the STOCK-DETAIL set. The corresponding missing entry from STOCK-DETAIL is represented with asterisks. Missing entries can be retrieved using the MULTIFIND or SUBSET command with the $MISSING parameter.

Placing the @ sign on the other side of the TO preserves all entries in the data set STOCK-DETAIL, and marks missing entries from the SALES-DETAIL with asterisks. The next JOIN command produces the following compound data set.


     >JOIN SALES-DETAIL.STOCK# TO @ STOCK-DETAIL.STOCK#

      <---------(SALES-DETAIL)-------> <--------(STOCK-DETAIL)------>
      ------------------------------------------------------------------
        ACCT-#    STOCK-#    QUAN          STOCK-#    DESCR    ON-HAND
      ------------------------------------------------------------------
         *           *         *            110         NUT       970
        222         60        25             60        BOLT      1200
      ------------------------------------------------------------------

Note: When forming compound entries for three or more data sets, once an entry from a data set is considered missing, no further joins can be made with the missing entry, and missing entries will be propagated.

When joining data sets with the @ parameter, the following restrictions apply:

The @ parameter is not allowed on both sides of the TO.
The @ parameter is not allowed on a cycle of data sets. A cycle occurs when the data item equivalences, in effect, form a circle. The following are examples of illegal data cycles:


      >JOIN SETA.ITEM1 @ TO SETB.ITEM1, &
      >>     SETB.ITEM2 @ TO SETA.ITEM3


      >JOIN SETA.ITEM1 TO @ SETB.ITEM2,&
      >>     SETB.ITEM3 TO @ SETC.ITEM3,&
      >>     SETC.ITEM2 TO @ SETA.ITEM2

When joining three or more data sets, the @ parameter must be propagated. Any data item equivalence naming a data set on the opposite side of the TO from the @ parameter must also have an @ sign associated with it, and the @ must be on the same side of the TO as that data set.

Propagating @ Signs

Example 1

In this example, an @ sign is associated with SETA in the equivalence between SETA and SETB. Therefore, the data set on the opposite side of the TO from the @ sign (SETB) must also have an @ sign associated with it. The @ sign associated with SETB requires that @ sign be associated with SETC. The @ sign associated with SETC requires that an @ sign be associated with SETD if SETD was joined to another data set. Since SETD is not connected to another data set, no @ sign is necessary.


      >JOIN SETA.ITEM1 @ TO SETB.ITEM2,&
      >>     SETB.ITEM3 @ TO SETC.ITEM4,&
      >>     SETC.ITEM5 @ TO SETD.ITEM6

Example 2

In the next example, the @ sign associated with SETB requires that an @ be associated with SETC. The @ associated with SETC requires that an @ be associated with SETD if SETD was joined to another data set. Since SETD is not connected to another data set, no @ sign is associated with it.


      >JOIN SETA.ITEM1 TO SETB.ITEM2,&
      >>     SETB.ITEM2 @ TO SETC.ITEM3,&
      >>     SETC.ITEM4 @ TO SETD.ITEM5

Example 3

In this example, the @ signs associated with SETB require @ signs to be associated with both SETA and SETC when these data sets are connected with other data sets. Only SETC has an additional connection and requires an @ sign.


      >JOIN SETA.ITEM1 TO @ SETB.ITEM2,&
      >>     SETB.ITEM1 @ TO SETC.ITEM2,&
      >>     SETC.ITEM1 @ TO SETD.ITEM4

Example 4

In the equivalence between SETA and SETB, SETA has the @ sign associated with it. Therefore, every time SETB is connected to another data set, an @ must be associated with it. This accounts for the two @ signs associated with SETB. SETD must also have an @ sign because of its connection with SETB. SETC and SETE are not required to have associated @ signs because they have no further connections.


      >JOIN SETA.ITEM1 @ TO SETB.ITEM2,&
      >>     SETB.ITEM3 @ TO SETC.ITEM2,&
      >>     SETB.ITEM4 @ TO SETD.ITEM1,&
      >>     SETD.ITEM2 @ TO SETE.ITEM3

In summary, @ signs may form the following connections:

Note that @ signs may not take the following form:




	NOTE: It may be helpful to diagram the connections established by the JOIN command as shown in this section. When the @ parameter is used, a diagram can allow you to easily see where an @ must be propagated. A diagram will also reveal any illegal connections made with the @ parameter.