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 then or equal to and reduce                 sv                            starting value                 av                            any value                 nav                          NOT any value                 asv                          any starting value                 lk                             SQL-compatible LIKE                 nlk                           SQL-compatible NOT LIKE Note that for negation operators [i.e., nav, nlk] it is not true in general that       X n-op Y is the same as NOT X op Y but rather:       X n-op Y is the same as NOT [X op Y OR X eq SQL-null] 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

Examples:

         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. Date literals must represent legitimate calendar dates. For example, '02-30-42' will cause an error. 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, for example:                 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 all Customers whose name begins with the letters SMI, such as SMITH, SMITHSON, etc., into collection A.  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 ddlReduce function 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. 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.