HP 3000 Manuals HP 3000 Manuals
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 three 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
![[]](../../pic3k/M010181/F03013c50.gif)
>JOIN SETA.ITEM1 TO @ SETB.ITEM2,&
>> SETB.ITEM3 TO @ SETC.ITEM3,&
>> SETC.ITEM2 TO @ SETA.ITEM2
![[]](../../pic3k/M010181/F03023c50.gif)
* 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 an 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
![[]](../../pic3k/M010181/F03033c50.gif)
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
![[]](../../pic3k/M010181/F03043c50.gif)
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
![[]](../../pic3k/M010181/F03053c50.gif)
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
![[]](../../pic3k/M010181/F03063c50.gif)
In summary, @ signs may form the following connections:
![[]](../../pic3k/M010181/F03073c50.gif)
Note that @ signs may not take the following form:
![[]](../../pic3k/M010181/F03083c50.gif)
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.