FIND
The FIND command isolates the subset of interest into a specified collection.
The "query-expression" determines the subset of interest, and whether
this query is local (confined to within one table) or global depends on the
presence or absence of the table name "tname" argument.
Notation standards
Options
Answers
Syntax:
FIND/options cxname [tablename] [WHERE] <query-expression>
Where:
cxname represents the name of the target collection;
tablename represents the name of the target table;
if tablename is not present, the global collection is used;
<query-expression> has the form:
<condition1> {AND OR XOR EXCEPT} <condition2> ... <conditionN>
conditionN can be any one of the following:
cxnameN - i.e., the name of a collection;
fieldN OP valueN;
fieldN OP fieldN' (if /F option is present);
#SavedCollection - i.e., the name of a saved collection
Options:
Cn -Reduce
the result collection to just the first "n" records. The C
option
is used for table-specific queries only.
F -Supports
direct field-to-field queries. See more in Notes below.
N -Force
a non-keyed find, even though the field is keyed.
X -Suppress
the command response.
Y -Do
not alter the command answer cells.
Answers:
Ctl.Ans1 =Number
of records found
Ctl.Ans2 =Number
of records found before C option was applied
Ctl.Ans3 =Zero
Query expressions:
A general query expression is described by
OR
condition AND condition...
XOR
EXCEPT
For example, two conditions connected with an AND might be
FIND
A WHERE CITY=SEATTLE AND AGE=30
There can be any number of conditions in a query expression. The operators used
to connect any two conditions are
Operator Definition Description
---------- ------------ --------------
AND Intersection True
for both conditions
OR Union True
for either condition
XOR Exclusive
union True for one or
the other but not both
EXCEPT But not True
for one but not the other
These are called Boolean operators. See below for a review of the relational
operators that are used in the conditions themselves.
Query expressions are evaluated left to right, so generally conditions connected
with ORs should come first in an expression, before the ANDs.
The general description of any condition in a query expression is
fieldname
operator value
For example, in CITY=SEATTLE the fieldname is CITY, the operator is equals, and
the value is SEATTLE.
Any operator can be preceded with the word NOT, which negates the operator. For
example NOT LT 5 is the same as GE 5, NOT NE is the same as equals, and so on.
Operator Meaning
---------- ---------
eq
or = Equal to
GT
or > Greater than
GE
or >= Greater than or equal to
LT
or < Less than
LE
or <= Less than or equal to
NE
or <> Not
equal to
eqr Equal
to and reduce
ger Greater
than or equal to and reduce
sv starting
value
av any
value
asv any
starting value
Also, there are two special names that indicate a value
HI_VALUE -The
highest value for the specified field
LO_VALUE -The
lowest value for the specified field
Notes:
FIND
with Keys and Non Keys
FIND
for a Range of Values
FIND
and Date Fields
FIND
and the implied OR
FIND
and the SV, AV, and ASV operators
FIND
and MEMO Fields
FIND
and the GER and EQR operators
FIND
directed Globally or Specifically
FIND
and the F option
FIND
and the special values HI_VALUE and LO_VALUE
FIND
and the Boolean algebra of data sets
FIND with Keys and Non Keys
FIND supports queries on either keyed or non-keyed fields. Non-key queries take
longer than queries based on keyed fields. The GER operator and the HI and
LO_VALUE are not supported in non-key queries.
In a multi-condition query that involves a non-key field always make sure that
the non-key condition is stated last. This takes advantage of the subset as
developed so far, and avoids a full table scan. For example, assume the field
named SEX is not keyed in the following two cases:
Case1: FIND A WHERE CITY=DALLAS AND
HAIR_COLOR=RED AND
HANDED=LEFT
AND SEX=FEMALE
Case2: FIND A WHERE SEX=FEMALE AND
CITY=DALLAS AND
HAIR_COLOR=RED
AND HANDED=LEFT
Case1 may provide a sub-second response, but Case2 may take an hour.
FIND for a Range of Values
A range of values can be
specified by using THRU or TO.
THRU means "inclusive of." TO means "exclusive of."
FIND
A WHERE AGE GE 33 THRU 45 (isolates ages 33 - 45)
FIND
A WHERE AGE GE 33 TO 45 (isolates ages 33 - 44)
FIND
A WHERE AGE GT 33 THRU 45 (isolates ages 34 - 45)
FIND
A WHERE AGE GT 33 TO 45 (isolates ages 34 - 44)
Note that the syntax uses the short-cut spelling THRU and not
"THROUGH."
The above method of specifying a range is always superior (more efficient, and
besides, it's easier to type) to the equivalent statement FIND A WHERE AGE GE
33 AND AGE LE 45. This latter form causes much more processing and provides
nowhere near the same response times.
FIND and Date Fields
FIND recognizes both internal and external date forms in query expressions. The
internal date form is YYYYMMDD. External date forms are MM/DD/YYYY or
MM-DD-YYYY or MM.DD.YYYY. Examples are:
FIND
A WHERE PDATE GE 12/15/1997 THRU 01/15/1998
FIND
A WHERE PDATE GE 19971215 THRU 19980115
The first uses an external date form and the second uses the internal date
form. FIND distinguishes one from the other by the fact that the internal form
employs no delimiter characters, that is, no slash, dash, or period. So the
syntactical rule for FIND is that when an external form is used, delimiter
characters will be present. If no delimiter character is present, you MUST be
using the internal form YYYYMMDD.
When using the SV operator, always use the internal form YYYYMMDD.
For more information about DATE fields, see "The DATE Data Type."
FIND and the implied OR
The syntax
FIND
A WHERE STATE=CA,NY,TX
is shorthand for
FIND
A WHERE STATE=CA OR STATE=NY OR STATE=TX
In a compound expression the shorthand is necessary for the desired result, as
in:
FIND
A WHERE HAIR_COLOR=RED AND STATE=CA,TX
would give you the redheads who lived in either California or Texas, whereas
the query FIND A WHERE HAIR_COLOR=RED AND STATE=CA OR STATE=TX would give you
the redheads who lived in California but everyone who lived in Texas regardless
of hair color. Of course, you could always correct that by making the
HAIR_COLOR part of the expression last as in FIND A WHERE STATE=CA OR STATE=TX
AND HAIR_COLOR=RED but why bother? Using the shorthand makes it all simpler.
FIND and the SV, AV, and ASV operators
These operators (SV, AV, and ASV) can only be used on fields that are defined
as alphanumeric (type X) or MEMO or DATE. That is, these operators are not
defined for any of the numeric data types.
SV- Starting Value- is directed at the first one or more
characters of the field. For example
FIND
A CUSTOMERS WHERE NAME SV SMI
FIND
A CUSTOMERS WHERE ZIP SV 750
FIND
A PARTS WHERE PART# SV P
FIND
A SALES WHERE PDATE SV 1976
The first isolates into collection A all Customers whose name begins with the
letters SMI such as SMITH, SMITHSON, etc.
The second assumes that ZIP codes were defined as type X data such as X(5) or
X(9), and would isolate all customers whose ZIP code starts with 750.
The third isolates all parts whose part number begins with the letter P.
The fourth finds all Sales
records whose purchase date (a DATE field) starts with the year 1976.
The mask character
The @ sign is used as a mask. It is a wildcard that stands for any character.
For example:
FIND
A CUSTOMERS WHERE NAME SV @@I
FIND
A PARTS WHERE PART# SV @A@@G
The first isolates all Customers who have the letter I as the third character
of their name such as Smith, White, Prince, etc.
The second isolates all parts whose second character of the part number was an
A and the fifth character was a G no matter what the first, third, and fourth
characters were.
AV- Any Value- will find the match for the given string of
characters anywhere in the field, not just at the start. For example:
FIND
A CUSTOMERS WHERE NAME AV CHURCH
FIND
A PARTS WHERE PART# AV A@@G
The first will find all names that have CHURCH anywhere in them such as
CHURCHILL, UPCHURCH, and so on.
The second finds all parts where the A and G are separated by exactly two
characters anywhere in the part number.
ASV- Any Starting Value- will look at each word in the field and
see if it starts with the given string of characters. By contrast, AV will
produce a hit if any word contains the specified string but ASV produces a hit
only if any word starts with the specified string. So if you had a MEMO field
named NOTES the following query
FIND
A WHERE NOTES ASV CHURCH
would find the word CHURCHILL if it is anywhere in the memo field, but would
not find UPCHURCH. By further contrast, SV would find CHURCHILL only if it were
the first word of the memo field.
FIND and MEMO Fields
All MEMO fields are automatically queryable. You do not declare MEMO fields as
keys in the Data Base Definition. For MEMO fields, only the SV, AV, or ASV
operators can be used. For example
FIND
A LETTERS WHERE LETTER SV BRO
FIND
A LETTERS WHERE LETTER AV BRO
FIND
A LETTERS WHERE LETTER ASV BRO
The first of the above queries will find records only if the first word of the
memo field begins with the characters BRO. The second finds records if any word
of the memo field contains the character string BRO anywhere in them. The third
finds records if any word in the memo field starts with the string BRO.
A special memo field query: There is a special query to find all MEMO
fields that have multiple generations:
FIND
collection tablename memofieldname GT n
where n is a number. If N=1 you will find all memo fields with multiple
generations. If n=2 you will find all memo fields that have more than two
generations, and so on.
FIND and the GER and EQR operators
GER stands for "Greater than or equal to and reduce," EQR stands for
"equal to and reduce." First, lets talk about REDUCE. There is a REDUCE command that reduces any isolation to its
uniqueness and is used to eliminate duplicates based on any criteria.
When the reduce criteria happens to be a keyed field you can combine a FIND
with a REDUCE in one step by using the GER or EQR operators. For example,
suppose you want to get exactly one of each state that is represented in the
CUSTOMERS table:
FIND
A CUSTOMERS WHERE STATE GER LO_VALUE
since all possible values for state (even blank) are greater than LO_VALUE this
query gets all states and reduces this subset to its uniqueness (eliminate
duplicates) based on STATE. The above accomplishes the same thing as
FILL
A CUSTOMERS
REDUCE
A CUSTOMERS STATE
except it does it much faster than the FILL-REDUCE sequence. The former may
take a couple of seconds where the latter may take several minutes. You would
do this sort of thing to set up a CHOOSE or a LISTPICK.
Another example:
FIND
A CUSTOMERS WHERE STATUS EQR OK, VG
the resulting subset would consist of two records, the first one in the
CUSTOMERS table that had the value OK and the first one that had the value VG
in their STATUS fields.
FIND directed Globally or Specifically
The FIND command is directed globally by specifying only a collection, that is,
leaving the tablename argument out of the command:
FIND
A WHERE CUST# = A00019
This is referred to as a global query and will isolate (into collection A)
records from every table in the data base where the above selection criteria is
true. All portions of the collection are touched. Those portions that represent
tables where no records fit the query are set to zero.
Global queries do not include tables that were ATTACHed from another database.
A table-specific query would include the tablename argument:
FIND
A CUSTOMERS WHERE CUST# = A00019
This table-specific query isolates (into collection A) the single record for
the specified customer. The rest of the collection is left untouched. That is,
other portions of the collection that represent records from other tables are
not disturbed from what they were before this operation.
FIND and the F option
The F option on the FIND command causes FIND to support direct field-to-field
queries. When the F option is present and the "value" portion of the
"fieldname-operator-value" triad does not have quotes around it, it
is interpreted to be another fieldname. For example:
FIND/F
A INVENTORY WHERE QUAN_ON_HAND < ORDER_QUAN
produces a correct result. ORDER_QUAN is understood to be a fieldname (as
opposed to a value) because there is an F option and ORDER_QUAN does not have
quotes around it.
When the source and target fields are type X the comparison is a string
compare. In this case the length of the source field dictates the length of the
compare. If either field is not type X the comparison is numeric.
The following forms are legitimate:
FIND/F
c tablename where fieldname1 operator fieldname2
FIND/F
c tablename where fieldname1 ge fieldname2 thru fieldname3
FIND/F
c tablename where fieldname1 ge fieldname2 thru "value"
FIND/F
c tablename where fieldname1 ge "value" thru fieldname2
The comma notation shortcut for multiple values is not supported by FIND/F with
multiple fieldnames.
FIND and the special values HI_VALUE and LO_VALUE
HI_VALUE means the highest value that a particular key has achieved anywhere in
the database, and LO_VALUE means the lowest value that a particular key has
achieved anywhere in the database. The "anywhere in the data base"
part of this definition is important as follows:
Lets assume for the purpose of this illustration that there is only one record
that has the highest value for age, and that AGE is a global key, that is,
there is more than one table that contains AGE as a keyed field. If you say
FIND A WHERE AGE = HI_VALUE you are guaranteed to get this record. But then the
question becomes what table did it come from?
Well, that is easy enough to discover (try a TALLY for instance) but that is
not the point of this illustration. The point is if you had said
FIND
A CUSTOMERS WHERE AGE = HI_VALUE
it is possible to get a zero result if the record that contains the highest
value for AGE was not in the CUSTOMERS table.
So you need to be aware of this. Maybe the moral to this story is that the HI
and LO_VALUE tokens are most useful when applied to non-global keys.
For an example of the use of the LO_VALUE token see the description of the GER
operator with FIND.
FIND and the Boolean algebra of data sets
A collection can be a term or a factor in a subsequent query. For example,
suppose you had done the following two queries:
FIND
A WHERE HAIR_COLOR=RED
FIND
B WHERE CITY=SEATTLE
Now you could say:
FIND
C WHERE A and B
or
C
= A and B
to get the collection C that has all the redheads who live in Seattle. The
second form shown above is legitimate syntax and is a standard way to
abbreviate queries. That is, the word FIND is actually optional in the syntax,
and further, you can substitute either the word IS or the equal sign for the
word WHERE.
You could also have said
A
= A and B
instead of the C=A and B shown earlier. This shows that a previously isolated
collection can be a term of the query as well as the result. In this case what
was in A is replaced by the result, but only after its contribution as a term
was dealt with. This is important as the following example illustrates.
Suppose we want June sales for the redheads who live in Seattle:
FIND
A WHERE CITY=SEATTLE AND HAIR_COLOR=RED
MATCH
A CUSTOMERS THRU CUST# WITH SALES
FIND
A SALES WHERE A AND MO=06
The first query gets us the redheads who live in Seattle, the MATCH gets us the
SALES records that belong to those customers, but it gets all the sales records
for those customers, not just June sales. The final query is table-specific and
is saying "of those records that are already in the SALES portion of A
find only those that have month equal June." The final query acts as a
filter, reducing the subset down to the desired result. The final result is
that collection A contains in its CUSTOMERS portion the redheads who live in
Seattle, and in its SALES portion only the June sales for those customers.
Copyright © 2019 , WhamTech, Inc. All rights reserved. This
document is provided for information purposes only and the contents hereof are
subject to change without notice. Names may be
trademarks of their respective owners.