QUERY/iX Reference Manual > Chapter 3 QUERY/iX COMMANDS

FIND

	MPE documents
	Complete PDF
	Table of Contents

	E0300 Edition 7 ♥
	E1098 Edition 6

Locates entries in a single data set.

Syntax


  F[IND] [#LIMIT=i;] {relation |
                      item identifier [NO] M[ATCHING] "pattern"}
    [{AND | OR} {relation |
                 item identifier [NO] M[ATCHING] "pattern"}]
    [...] [END]

For example:


  >FIND BADGE# IS "09.18"

Where relation = BADGE# IS "09.18"


  >FIND L-NAME IS MARTENSEN AND F-NAME IS SALLY

Where relation = L-NAME IS MARTENSEN AND F-NAME IS SALLY


  >F STATE M "C?"

Where item identifier = STATE and pattern = "C?"

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 into the select file.

item identifier

takes the form:


  [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 in a currently open data base. If a data base is specified, the data set must belong to that data base.

data item name is either the name of a simple data item, or the name of a compound data item with an optional (subscript) parameter. If a data set name is used, the data item must belong to the specified data set. 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 parenthesis, and must be an integer >= 1 and <= the number of sub-items defined for the compound data item. QUERY defaults to the first sub-item if no subscript is specified.

MATCHING

allows you to retrieve data based on the comparison of data item values with a specified pattern.

NO MATCHING

allows you to retrieve data based on all data records NOT MATCHING the pattern provided.

The NO may be followed by additional characters such that all of the following examples are accepted:


  FIND PRODUCTS.NAME NO MATCH "ABC?*"
  FIND PRODUCTS.NAME NOT MATCHING "ABC?*"
  FIND PRODUCTS.NAME NONE M "ABC?*"

"pattern"

is defined by pattern matching specifications. Pattern must be enclosed in quotation marks.

relation

takes the form:


  [data base name:] [data set name.] data item name [(subscript)]
    relop "value" [,"value"] ...

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. Values not in quotation marks will be upshifted. For example, California is changed to CALIFORNIA before it is compared to data item values in the data base. However, data items of type X are not upshifted if no quotation marks are used. Value must be an exact match for character type data items (type U and X).

Null values will cause QUERY to prompt you for the values that you want to retrieve. Refer to "Using Null Values" under the FIND command.

END

must be included in a procedure.

Methods of Retrieval

If a FIND command is entered, QUERY will use either a keyed (indexed) or a serial method of retrieval. A keyed retrieval occurs when master or detail data set search keys can be used to locate entries. A serial read occurs when the data set requires serial scanning without the benefit of search keys.

QUERY will automatically invoke the B-Tree retrieval feature when a wildcard character is used in the search value. As QUERY may have up to ten databases open and each could have a different wildcard, the wildcard for the DEFINED database will be used when specified, else the first wildcard specified by an open database will be used. QUERY uses modes 21 or 24 for B-Tree DBFIND for best performance.

QUERY will utilize the B-Tree functionality when possible for both character and binary data types. The wildcard feature is only implemented by TurboIMAGE/XL for character data types (X and U) and not for binary data types. Neither has the Not Equal operator been implemented for B-trees and so QUERY will continue to use the older access methods (normally serial reads) for Not Equal retrievals. Note that the LIST command always uses a serial read but will apply the wildcard search character for relations.

When a database has the BTREEMODE1 flag set to OFF, QUERY will not attempt to use the B-Tree functionality for Wildcard retrievals. This condition is reported by all forms of the FORM command.

Certain possible wildcard characters are used by QUERY for syntax scanning and are limited in their usage. These special characters may be utilized when the data values are delimited by double quotes (for example, tilde, carat, comma, and parenthesis). See following examples:


  >FIND CUSTOMERS.CUSTOMER-sNO=01540~
    Tilde is a limited wildcard
  
  EXPECTED A CONNECTOR OR 'END'
  INVALID CONNECTOR OR TERMINATOR!
  
  >FIND CUSTOMERS.CUSTOMER-NO="01540~"
    Tilde enclosed in quotes works
  8  ENTRIES QUALIFIED
  
  >FIND CUSTOMERS.CUSTOMER-NO=01540"
    Double-quote will not work...
  EXPECTED A CONNECTOR OR 'END'
  INVALID CONNECTOR OR TERMINATOR
  
  >FIND CUSTOMERS.CUSTOMER-NO="01540"""
    unless doubled in quotes and double-quote is wildcard
  8  ENTRIES QUALIFIED
  
  >FIND CUSTOMERS.CUSTOMER-NO=01540*
    If asterisk is NOT the wildcard for this database,
    then no match unless the value 01540* is in dataset.
  0  ENTRIES QUALIFIED

In some cases, a serial method of retrieval may take a great deal of time. If you decide you do not want to wait for completion, you can abort a search by entering CONTROL-Y. QUERY will print the number of qualifying entries and ask you if you want to continue the search. A "NO" reply will store the record addresses of retrieved entries in a select file and discontinue retrieval of additional entries. Search time can also be controlled with the #LIMIT = parameter, which specifies the maximum number of qualifying entries you want to retrieve.

Table 3-1 FIND Command Relational Operators

OPERATOR	MEANING
`= IS IE EQ`	is equal to (Multiple `values` may be used with these operators.)
`# <> ISNOT INE NE`	is not equal to (Multiple `values` may be used with these operators.)
`< ILT LT`	is less than
`>= INLT GE`	is not less than (is greater than or equal to)
`> IGT GT`	is greater than
`<= INGT LE`	is not greater than (is less than or equal to)
`IB value₁, value₂`	is between (and including) `value`₁ and `value`₂
Note: The operators <>, <=, and >= cannot have any intervening spaces (embedded blanks).

U and X Type Values

When entering values for X and U type data items, the values must appear exactly as the data was originally entered. For example, if the data item STREET-ADDRESS has a value with three spaces between the street number and name, you must enter those spaces or QUERY will not find the item. Leading blanks must also be entered if they appear in the item. Since blanks or spaces are special characters, all such values must be enclosed in quotation marks.

Logical Connectors

To make more than one comparison for each entry selected, you connect relations with the logical connectors AND or OR. The AND connector instructs QUERY to select only those entries whose data item values satisfy the relations on both sides of the AND. The OR connector indicates that the entries are selected if one (or both) of the two relations on either side of the OR is satisfied.

Both types of logical connectors can appear in a FIND command. All relations connected with AND are examined as if they were surrounded by parenthesis. Any relation or set of relations separated from others by OR are compared to the data entry and the entry is selected if the relations are true. For example, the command:


  >FIND A = 3 AND B = 4 OR C IGT 9 END

locates all entries with both A equal to 3 and B equal to 4 as well as all entries containing a C value greater than 9. The command:


  >FIND A = 3 OR A = 2 AND B = 5 AND C = 8 OR B = 9 END

locates all entries with either A equal to 3 or A equal to 2 and B equal to 5 and C equal to 8, or entries with B equal to 9.

Parentheses cannot be used in a command, but you can create constructs which act as parentheses and force an OR comparison to take precedence over an AND comparison. For example, if Cn stands for a relation:


  (C₁ and C₂) and C₃

is represented as:


  C₁ and C₃ or C₂ and C₃

Up to 50 logical connectors may be used in one FIND command.

Compound Items

A compound data item is an item that occurs more than once in the same data entry. Each occurrence of the data item is called a sub-item. Each may have a value and any or all sub-items in a data entry can be accessed.

For example, if you have defined a compound item called MONTHLY-SALES with 12 sub-items (1 for each month), a retrieval of all entries with June sales of greater than $2000 could look like this:


  >FIND MONTHLY-SALES(6) > 2000

If no subscript is specified, the subscript is assumed to be the first sub-item.

Multiple Values

To specify more than one value for the same data item, list the values one after the other, separated by commas. For example, the command:


  >FIND STATE IS "CALIFORNIA","NEVADA","WASHINGTON"

locates the entries with the value of data item STATE equal to either CALIFORNIA, NEVADA, OR WASHINGTON. The above command is equal to:


  >FIND STATE IS "CALIFORNIA" OR STATE IS "NEVADA"&
  >>OR STATE IS "WASHINGTON"

Multiple values can only be used with the "equal" or "not equal" relational operators.

Using Null Values

The FIND command can prompt you for data item values to be compared with data item values in the data entries of the data set. To do this, you use null data item values in the command. Null values are represented by a pair of quotation marks without any intervening characters or blank spaces (""). When the command is executed, you are prompted to enter a value for each null value in the command. This is useful when the FIND command is stored as a procedure in a Proc-file.

The procedure can be executed using different comparison values without modifying the procedure each time. For example, the following command would prompt you for a value of ACCOUNT with the following message.


  >FIND SALES.ACCOUNT <> ""
  WHAT IS THE VALUE OF - ACCOUNT
  >>24536173
  USING SERIAL READ
  10  ENTRIES QUALIFIED

QUERY searches the appropriate data set for the values you specify. The value should be entered without the surrounding quotes since all characters entered (including leading blanks, quotes, and other special characters) are significant. Lowercase characters are upshifted unless the data item type is X. The maximum value size is 72 characters.

You are prompted once for each null value in the command. You must supply a value for each null value in the procedure. You can use a known invalid response if you do not want to find another entry. For example:


  >FIND CUSTOMER.CITY IS "",""
  WHAT IS THE VALUE OF - CITY
  >>PETALUMA
  WHAT IS THE VALUE OF - CITY
  >>X
  USING SERIAL READ
  1 ENTRIES QUALIFIED.

When using null values from a FIND command in an XEQ file, the XEQ parameter NODATA must be used. This parameter indicates that the values to be searched for are not included in the FIND command but must be entered by you. Refer to the XEQ command for more information about using null values.

In session mode, supply the desired value after each prompt message. In job mode, you must anticipate the order of prompts and supply the desired values, one per record, following the FIND command.

Examples

Example 1

To determine whether or not a customer is already in the data set, you can try to find the name.


  >FIND LAST-NAME IS MARTENSEN
  USING SERIAL READ
  0  ENTRIES QUALIFIED

Example 2

Since ACCOUNT is in more than one data set you can either qualify the data item name or let QUERY prompt you for it. The response, SALES, is automatically entered in the data set list.


  >FIND CUSTOMER.ACCOUNT EQ 24536173
  USING SERIAL READ
  1  ENTRIES QUALIFIED
  >DATA SETS=
  >FIND ACCOUNT EQ 24536173
  ACCOUNT            IS A MEMBER OF THESE SETS:
  CUSTOMER,SALES
  WHICH SET DO YOU WISH TO USE?
  >>SALES
  3  ENTRIES QUALIFIED

Example 3

In the example below, QUERY uses the data set list to determine which of the three data sets containing STOCK# to use. Since SALES was placed in the data set list in the previous example, it is used. However, the data item DESCRIPTION is in the PRODUCT data set, and the FIND command can only refer to one data set so an error results. It is usually best to clear the data set list if you are unsure about what it contains. PRODUCT was entered in the data set list automatically as a result of the previous command, so QUERY uses PRODUCT and does not prompt for the data set name.


  >F STOCK# IGT 33333333 AND DESCRIPTION IS "NEHRU JACKET"
  RETRIEVAL FROM MORE THAN ONE DATA SET
  >S=
  >F STOCK# IGT 33333333 AND DESCRIPTION IS "NEHRU JACKET"
  STOCK#       IS A MEMBER OF THESE SETS:
  PRODUCT, SALES, INVENTORY
  WHICH SET DO YOU WISH TO USE?
  >>PRODUCT
  USING SERIAL READ
  0 ENTRIES QUALIFIED
  >F STOCK# IGT 33333333 AND DESCRIPTION IS "NEHRU JACKET"
  USING SERIAL READ
  0  ENTRIES QUALIFIED

Example 4

The following error occurs because an ampersand is used to continue the line but there is no space before it or at the beginning of the next line.


  >F ACCOUNT IGT 55555555 AND STATE IS CA OR ACCOUNT IS 121212121&
  >>AND STATE IS MA OR CUSTOMER.STATE IS AZ
  ACCOUNT     IS A MEMBER OF THESE SETS:
  CUSTOMER,SALES
  WHICH SET DO YOU WISH TO USE?
  >>CUSTOMER
  INVALID NUMERIC DIGIT

Example 5

All items which appear in multiple sets must be qualified unless the set name is in the set list. After entries are located, you can use the REPORT command to print the data.


  >F CUSTOMER.ACCOUNT IGT 55555555 AND STATE IS CA OR ACCOUNT&
  >>     IS 121212 AND STATE IS MA OR STATE IS AZ
  STATE IS A MEMBER OF THESE SETS
  CUSTOMER,SUP-MASTER
  WHICH SET DO YOU WISH TO USE?
  >>CUSTOMER
  6  ENTRIES QUALIFIED
  >REPORT D,ACCOUNT,10;D,STATE,15;END

    76623455    CA
    74001813    CA
    87654321    CA
    80808080    CA
    77765555    CA
    99998877    CA

Example 6

A simpler technique for determining the data set to be used is to enter it into the data set list with a DATA-SETS= command.


  >S=CUSTOMER
  >F ACCOUNT IGT 55555555 AND STATE IS CA OR ACCOUNT IS 12121212&
  >>      AND STATE IS MA OR STATE IS AZ
  USING SERIAL READ
  6  ENTRIES QUALIFIED

Generic Search

A pattern consists of a series of the following special characters that indicate the type of data that can be entered in that position:


   text
      a    upper or lowercase alphabetic character (A-Z, a-z)
      u    uppercase alphabetic character (A-Z)
      l    lowercase alphabetic character (a-z)
      b    blank
      d    digit (0-9)
      ?    any character

Note: A pattern must specify the maximum number of characters allowed for the data item length. Therefore, to find data item values of varying lengths, you must specify that the rest of the data item value is filled with blanks. Refer to the discussion of repetition for more information.

The beginning of the pattern is defined by a beginning quotation mark and the end is defined by an ending quotation mark. QUERY does not read spaces as blanks; therefore, you can leave spaces inside a pattern to allow for better readability.

The matching pattern can include specific characters in addition to the types listed above. For example:


  MATCHING "Aaa-dddd"

The pattern above means that the value must start with the letter "A" followed by any two upper or lowercase letters, followed by a hyphen and any four digits. For example, the values "Acs-1234" and "AAA-9999" are acceptable, but the values "Bcs-1223" and "A12-345" are not acceptable.

Transparency

A special operator can be used to indicate that a pattern is to be used as an actual value. For example, suppose you want the lowercase letter "a" to be an exact value in the pattern, you can do this by preceding it, or any of the other special characters, with an exclamation point (!). For example:

MATCHING "!ad" Value must start with the letter "a" followed by one digit.

The exclamation point (called the transparency operator) is also used to allow inclusion of any of the pattern operators listed below and described in Table 3-2 "Matching pattern operators/functions".


  !    transparency
  ,    choice
  :    range
  { }  grouping
  [ ]  optional
  +    repetition  (1 or more)
  *    repetition  (0 or more)

Choice

You can indicate a selection of acceptable patterns as part of the MATCHING pattern. Each possible choice is separated by a comma. For example:

MATCHING "ABCD,DEFG,dddd" The values "ABCD", "DEFG", or any four digits are acceptable.

Range

A range of acceptable characters for a single character position can be indicated with the colon. All characters within the range are acceptable. This acts as shorthand for listing a series of single characters in ASCII sequence. For example:

`MATCHING "Cb:Jb"`	This pattern would accept the values C, D, E, F, G, H, I, J in a two-character field.
`MATCHING "10:15"`	The values 10, 11, 12, 13, 14, and 15 are accepted by this pattern.
`MATCHING "!a?:f?"`	Since "a" is a special character, it is preceded by an exclamation point (!). Other characters in the range (except the special character ?) are implicitly preceded by this operator. This pattern is equivalent to: `MATCHING "!a?,!b?,!c?,!d?,!e?,!f?"`.

Grouping and Optional

You can group pattern specifications by enclosing the pattern in braces { } or brackets [ ]. Braces indicate that data must correspond to one item in the group. Brackets make the pattern optional, indicating that data can correspond to one or none of the items in the pattern. For example:

`MATCHING "{AAA,BBB,CCC} ddd"`	One of the choices within the braces must be matched. The values "AAA123" and "BBB999" and "CCC562", among others, are acceptable matches for this pattern.
`MATCHING "[A,B,C] ddd [b]"`	The choices within the brackets can be omitted, or one can be matched. For example, "A345", "C567", "B441", and "123" are acceptable matches.
`MATCHING "[u,d] !+ [1:5]"`	Accepts such values as "A+", "3+", and "+5".
`MATCHING "{[-,dd] dd [b,d]}"`	Accepts such values as "-125" or "2345" or "500" or "-10".

Repetition

Repetition of any character or sets of characters can be indicated by an asterisk (*) or by a plus sign (+) following any pattern character or pattern group within braces { }. A plus sign (+) means that at least one occurrence of the pattern is required for the match. An asterisk (*) means that zero or more occurrences can be matched. (These repetition indicators cannot follow items enclosed within brackets [ ].) For example:

`MATCHING "d+b*"`	The plus sign indicates repetition of the digit, with at least one occurrence required for the match. Thus, "2" or "745227" or "55" are acceptable, but a blank is not.
`MATCHING "Xd+b*"`	This pattern accepts the letter X followed by one or more digits. "X1" or "X12323" and so forth are acceptable, but not "X".
`MATCHING "M {A,C,d}+"`	A plus sign after the braces indicates repetition of any item within the braces, in any order. Some acceptable values are "MAC1", "MCCC", or "M123".
`MATCHING "db"`	The asterisk indicates optional repetition that allows zero or more occurrences of the pattern. Thus, the digit can be omitted, or repeated any number of times. Nothing or "3" or "123456" are all acceptable patterns.
`MATCHING "[d+]b*"`	This pattern is another way of expressing the pattern shown above as db.
`MATCHING "a+"`	Accepts an alphabetic value.
`MATCHING "Xub"`	This pattern accepts "X" alone or followed by any number of uppercase letters. For example, "XABC" or "XX" or "X" are all acceptable.
`MATCHING "M+ {A,D,d}*"`	Any of the enclosed characters can be repeated in any order, or can be omitted. Thus, "MMMM" is acceptable, as are "MAA1", "MCCA", "M222", and so forth.

When you use a pattern to find variable length values such as "1", "12" and "123", you must indicate that blanks following the value fill the rest of the length of the item. For example, if a data item type is X6 and the value contained in the item is "123", there are three blanks following the value. In this case, the pattern "ddd" will not find the value. The pattern must account for the blanks that fill the item length. The pattern "dddb*" will find the value if blanks follow the value. If other characters or unknown characters follow the value, you can use the pattern "ddd?*".

Operator Hierarchy

The pattern operators are evaluated in the following order, where x and y are any patterns.


   Highest     !x           Transparency
      |        x:y          Range
      |        x+ or x*     Repetition
      v        xy           Concatenation
    Lowest     x,y          Choice

Some further examples of the MATCHING statement are:

`MATCHING "1ddd"`	Accepts an integer between 1000 and 1999. Can also be expressed as "1000:1999".
`MATCHING "[d] [d!:dd] b* [AM,PM]"`	Accepts a time such as "3:00 PM" or "12:00".
`MATCHING "{1:7} {0:7}* b*"`	Accepts a number greater than zero with at least one digit and no leading zeros, such as "2047", or "1", or "74".
`MATCHING "ddd-dd-ddddb"`	Accepts any social security number, such as "044-24-0474".
`MATCHING "[(ddd)] ddd-dddd b*"`	Accepts a phone number with an optional area code.

Table 3-2 "Matching pattern operators/functions" summarizes the operators allowed in a MATCHING pattern.

Table 3-2 Matching pattern operators/functions

OPERATOR	FUNCTION	EXAMPLE
`!`	Transparency operator allows use of any special MATCHING characters as an element in the pattern.	`MATCHING "!u,!d,!,,!!"` accepts any of the values `"u"`, `"d"`, `","` or `"!"`.
`,`	Choice of subpatterns, any one of which satisfies the match.	`MATCHING "A,B,dd"` accepts values such as `"A"`, `"B"` and `"22"`.
`:`	Range of single characters in ascending ASCII order, any one of which satisfies the match.	`MATCHING "2:6"` accepts the values `"2"`, `"3"`, `"4"`, `"5"` or `"6"`.
`{ }`	Grouping (required) requires one occurrence of any pattern within braces.	`MATCHING "{A,B}dd{%,d}"` accepts `"A223"`, `"B34%"`, `"A795"` and so forth.
`[]`	Grouping (optional) allows zero or 1 occurrence of any item in a pattern within brackets [ ].	`MATCHING "[A,B]dd[%,d]"` accepts `"24"`, `"A99"`, `"10%"`, `"123"` and so forth.
`+`	Repetition (required) requires one or more occurrences of a preceding item, or a pattern within braces { }.	`MATCHING "Xd+"` accepts values such as `"X1"`, `"X22"`, `"X3334789"` and so forth, but not `"X"`. `MATCHING "{d,a}+"` accepts values such as `"11" "A23"`, `"acb"`, `"33ABC9"`.
`*`	Repetition (optional) allows zero or more occurrences of a preceding item or a pattern within braces { }.	`MATCHING "Xd"` accepts values such as `"X"`, `"X1"`, `"X22"` and `"X3334789"`. `MATCHING "{d,a}"` accepts a null value, or such values as `"11"`, `"A23"`, `"acb"` or `"33ABC9"`.

EXIT

FIND ALL