Retrieves compound entries from a compound data set specified
by the most recent JOIN command.
Syntax |
 |
MU[LTIFIND] [#LIMIT=i;] {relation
item identifier M[ATCHING] "pattern"}
[{AND
OR} {relation
item identifier M[ATCHING] "pattern"}]...[END]
|
Parameters |
|
- i
is an integer specifying the maximum number of qualifying entries you
want to retrieve. i must be >= 0. If you specify a negative
number, QUERY ignores your input. When the #LIMIT = parameter is
specified, only the first i
qualifying entries are placed in the select
file. If fewer than i qualifying entries exist, then all
entries that qualify are put in the select file.
- item identifier
takes the form:
[data base name:] [data set name
dummy 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 named in the most
recent JOIN command.
dummy data set name is a temporary data set name established
in the most recent JOIN command.
data item name is the name of a data item belonging to one
of the data sets named in the most recent JOIN command. For
matching, the data item must be type X or U.
subscript is a number to indicate which sub-item you want to
locate. Subscript is entered with parentheses, and 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.
- MATCHING
allows you to retrieve data based on the comparison of data items
with a specified "pattern".
- "pattern"
must be enclosed in quotation
marks. Refer to the FIND command for further
specifications.
- relation
takes the form:
[data base name:] [data set name
dummy data set name] data item name
[(subscript)] relop {"value"
$MISSING} [,{"value"
$MISSING}]...
|
data item name can be any data type. Refer to previous definition.
relop is a relational operator as shown in Table 3-1 “FIND Command Relational Operators”.
value
is the data item value. It must be the same type and within the
same value range as the data item named in the relation. Value
need not be enclosed in quotation marks unless the value contains
special characters. A value not contained in quotation marks is
upshifted. For example, California is converted to CALIFORNIA
before it is compared to data item values in the data base.
Value must be an exact match for character type data items
(type U or X).
You can use null values. Refer to
"Using Null Values" under the FIND command.
- $MISSING
is used to retrieve a missing data item value from a compound data
set. $MISSING is discussed in detail later under the MULTIFIND command.
- END
must be included in a procedure.
Discussion |
|
The MULTIFIND command retrieves data entries from compound data sets
according to the selection criteria you give. This command
can only be used to retrieve data entries that belong to a compound
data set resulting from a JOIN command.
When a MULTIFIND command is entered, whether valid or invalid, the
internal select file is cleared.
The MULTIFIND command does not lock the data sets named in the JOIN
command for the duration of the formation of the compound data set,
but only locks while reading data set entries. If you want locking
to span the formation of the compound data set, use the SETLOCKS and
RELEASE commands.
Note that entering
a REPORT ALL command will show you the order in which the
data sets were joined. This may not be the same order that you
entered them in the JOIN command, as QUERY optimizes the actual
joining of the data sets.
Up to 50 logical connectors (AND, OR) can
be used in a MULTIFIND command.
Refer to "Logical Connectors" under the FIND command.
Example |
|
Suppose you have three data sets, SALES-DETAIL,
STOCK-DETAIL, and MANUF-DETAIL, and you want to produce a report that
shows the quantity of sales of a product, the quantity on hand, and
manufacturing information about this product. To access information
from all three data sets,
a compound data set must be defined with the JOIN command.
>JOIN SALES-DETAIL.STOCK# TO STOCK-DETAIL.STOCK#,&
>> STOCK-DETAIL.STOCK# TO MANUF-DETAIL.STOCK#
|
The JOIN above combines the three data sets into one compound data
set using the common data item STOCK#. Until a MULTIFIND or MULTIFIND
ALL command is performed, the compound data set is only defined,
but not created. If you could look at the compound data set that
would result
from the above JOIN command, it would
appear as follows:
<---(SALES-DETAIL)---> <---(STOCK-DETAIL)---> <---MANUF-DETAIL)--->
-------------------------------------------------------------------------
ACCT# STOCK# QUAN STOCK# DESCR ON-HAND STOCK# LABOR MACHINE
-------------------------------------------------------------------------
111 50 100 50 NAIL 1000 50 5 1
111 60 20 60 BOLT 1200 60 10 5
222 50 5 50 NAIL 1000 50 5 1
222 60 25 60 BOLT 1200 60 10 5
222 70 95 70 WASHER 325 70 15 6
333 50 45 50 NAIL 1000 50 5 1
-------------------------------------------------------------------------
|
Performing the following MULTIFIND creates the compound data set from which
the entry below is retrieved.
>MULTIFIND QUAN > 80 AND ON-HAND > 500
-------------------------------------------------------------------------
ACCT# STOCK# QUAN STOCK# DESCR ON-HAND STOCK# LABOR MACHINE
-------------------------------------------------------------------------
111 50 100 50 NAIL 1000 50 5 1
-------------------------------------------------------------------------
|
Using the $MISSING Parameter |
|
The MULTIFIND command can contain the optional $MISSING parameter,
which allows you to retrieve a missing (non-compound) entry from
a compound data set.
Missing entries result when the JOIN command contains the optional
@ parameter. For more information on missing entries, refer to
the JOIN command.
A missing entry is retrieved from a compound
data set by comparing a data item with the value
$MISSING in a relation.
This parameter allows the
relation to be evaluated as true if, for a particular compound
entry, the non-compound entry that the data item belongs to
is missing. Note that a relation with
$MISSING will be evaluated as false if, for a particular
compound entry, the non-compound entry for the data set to
which the data item belongs is not missing, but the value of
the data item is null.
Example |
|
The following JOIN command produces the compound data set below.
>JOIN SALES-DETAIL.STOCK# @ TO STOCK-DETAIL.STOCK#
<--------(SALES-DETAIL)--------> <--------(STOCK-DETAIL)--------->
------------------------------------------------------------------
ACCT# STOCK# QUAN STOCK# DESCR ON-HAND
------------------------------------------------------------------
111 50 100 50 NAIL 1000
111 60 20 60 BOLT 1200
222 50 5 50 NAIL 1000
222 60 25 60 BOLT 1200
222 70 95 70 WASHER 325
333 50 45 50 NAIL 1000
444 80 92 ** **** ****
------------------------------------------------------------------
|
The following MULTIFIND command does not retrieve any entries from the join.
>MULTIFIND ACCT# = 444 AND ON-HAND >0
|
The MULTIFIND command
with the $MISSING parameter retrieves the following entry.
>MULTIFIND ACCT# = 444 AND ON-HAND > 0 OR&
>> ACCT# = 444 AND ON-HAND = $MISSING
------------------------------------------------------------------
ACCT# STOCK# QUAN STOCK# DESCR ON-HAND
------------------------------------------------------------------
444 80 92 ** **** ****
------------------------------------------------------------------
|
Methods of Retrieval |
|
QUERY retrieves data from compound data sets with either a Keyed,
Serial, or Sort/Merge method,
or a combination of these. QUERY notifies you
with an appropriate message on your terminal screen. A Keyed
retrieval occurs when master or detail data set search items
can be used to locate entries. A Serial
retrieval occurs when the formation and searching of the compound
data set requires serial scanning without benefit of
search items. A Sort/Merge retrieval occurs if the compound data
set requires sorting and merging of two or more data sets.
Because the Sort/Merge method of searching and retrieval may take
considerable time, it is generally advisable to avoid it when
accessing multiple data sets. You can abort a search by entering
a CONTROL Y. QUERY will print the number of qualifying entries
and ask if you want to continue searching. A "NO" reply will store
the retrieved entries in an internal select file and discontinue
the search. Search time can be limited with the #LIMIT =
parameter.
Sort/Merge retrieval will not be used if all data items named in the
data item equivalences are search items, and no @ signs are used. It
is advisable to maximize the number of data item equivalences with
the above properties. Refer to the JOIN command for information on
using the @ sign in joining data sets.
