HP 3000 Manuals HP 3000 Manuals
MULTIFIND
Retrieves compound entries from a compound data set specified by the most
recent JOIN command.
Syntax
{relation }
MU[LTIFIND] [#LIMIT=i;] {item identifier M[ATCHING] "pattern"}
[{AND} {relation }]
[{OR } {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 set name. ]
[data base 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 set name. ]
[data base name:] [dummy data set name]
data item name [(subscript)] relop
{"value" } [ {"value" }]
{$MISSING} [,{$MISSING}]...
data item name can be any data type. Refer to
previous definition.
relop is a relational operator as shown in Table
3-1.
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.