QUERY/iX Reference Manual
> Chapter 4 QUERY/iX COMMANDS (cont)MULTIFIND |
||||||||||||||||||||
|
SyntaxMU[LTIFIND] [#LIMIT=i;] {relation | item identifier [NO] M[ATCHING] "pattern"} [ {AND | OR} {relation | item identifier [NO] M[ATCHING] "pattern"} ] ... [END] Parameters
DiscussionThe 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. ExampleSuppose 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 ParameterThe 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. ExampleThe 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 >0The 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 RetrievalQUERY 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. Refer to the Find command "Methods of Retrieval " for wildcard usage and for B-tree access. 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.
|