QUERY

version 3

QUERY ({table}{; queryArgument{; *}})

QUERY looks for records matching the criteria specified in queryArgument and returns a selection of records for table. QUERY changes the current selection of table for the current process and makes the first record of the new selection the current record.

If the table parameter is omitted, the command applies to the default table. If no default table has been set, an error occurs.

If you do not specify queryArgument or the * parameters, QUERY displays the Query editor for table (except when it is the last row of a multiple query, see example 2). The User environment Query editor is shown here:

For more information about using the Query Editor, refer to the 4th Dimension User Reference manual.

The user builds the query, then clicks the Query button or chooses Query in selection to perform the query. If the query is performed without interruption, the OK variable is set to 1. If the user clicks Cancel, the QUERY terminates with no query actually performed, and sets the OK variable to 0 (zero).

1. The following line displays the Query editor for the [Products] table:

   QUERY([Products])

2. The following line displays the Query editor for the default table (if it has been set)

   QUERY

If you specify the queryArgument parameter, the standard Query editor is not presented and the query is defined programmatically. For simple queries (search on only one field) you call QUERY once with queryArgument. For multiple queries (search on multiple fields or with multiple conditions), you call QUERY as many times as necessary with queryArgument, and you specify the optional * parameter, except for the last QUERY call, which starts the actual query operation. The queryArgument parameter is described further in this section.

3. The following line looks for the [People] whose name starts with an "a":

   QUERY([People];[People]Last name="a@")

4. The following line looks for the [People] whose name starts with "a" or "b":

   QUERY([People];[People]Name="a@";*) ` * indicates that there are further search criteria
   QUERY([People]; |;[People]Name="b@") ` No * ends the query definition and starts the actual query operation

• The queryArgument parameter uses the following syntax:

{ conjunction ; } field comparator value

• The conjunction is used to join QUERY calls when defining multiple queries. The conjunctions available are the same as those in the User environment Query editor:

The conjunction is optional and not used for the first QUERY call of a multiple query, or if the query is a simple query.

• The field is the field to query. The field may belong to another table if it belongs to a One table related to table with an automatic relation. The table to which QUERY is applied must be the Many table.

• The comparator is the comparison that is made between field and value. The comparator is one of the symbols shown here:

• The value is the data against which field will be compared. The value can be any expression that evaluates to the same data type as field. The value is evaluated once, at the beginning of the query. The value is not evaluated for each record. To query for a string contained in a string (a "contains" query), use the wildcard symbol (@) in value.

Here are the rules for building multiple queries:

• The first query argument must not contain a conjunction.

• Each successive query argument must begin with a conjunction.

• The first query and every other query, except the last, must use the * parameter.

• To perform the query, do not specify the * parameter in the last QUERY command. Alternatively, you may execute the QUERY command without any parameters other than the table (the Query editor is not shown; instead, the multiple query you just defined is performed).

Note: Each table maintains its own current built query. This means that you can create multiple built queries simultaneously, one for each table. You must use the table parameter or set the default table to specify which table to use.

No matter which way a query has been defined:

• If the actual query operation is going to take some time to be performed, 4th Dimension automatically displays a message containing a progress thermometer. These messages can be turned on and off by using the commands MESSAGES ON and MESSAGES OFF. If the progress thermometer is displayed, the user can click on the Stop button to interrupt the query. If the query is completed, OK is set to 1. Otherwise, if the query is interrupted, OK is set to 0 (zero).

• If any indexed fields are specified, the query is optimized every time that it is possible (indexed fields are searched first) resulting in a query that takes the least amount of time possible.

5. The following command finds the records for all the people named Smith:

   QUERY([People];[People]Last Name="Smith")

Note: If the Last Name field were indexed, the QUERY command would automatically use the index for a fast query.

Reminder: This query will find records like "Smith", "smith","SMITH", etc. To distinguish lowercase from uppercase, perform additional queries using the ASCII codes.

6. The following example finds the records for all people named John Smith. The Last Name field is indexed. The First Name field is not indexed.

   QUERY ([People]; [People]Last Name = "smith"; *)   ` Find every person named Smith
   QUERY ([People]; &; [People]First Name = "john")   ` with John as first name

When the query is performed, it quickly does an indexed search on Last Name and reduces the selection of records to those of people named Smith. The query then sequentially searches on First Name in this selection of records.

7. The following example finds the records of people named Smith or Jones. The Last Name field is indexed.

   QUERY ([People]; [People]Last Name="smith"; *)  ` Find every person named Smith…
   QUERY ([People]; | ; [People]Last Name="jones") ` ...or Jones

The QUERY command uses the Last Name index for both queries. The two queries are performed, and their results put into internal sets that are eventually combined using a union.

8. The following example finds the records for people who do not have a company name. It does this by finding entries with empty fields (the empty string).

   QUERY ([People]; [People]Company="") ` Find every person with no company

9. The following example finds the record for every person whose last name is Smith and who works for a company based in New York. The second query uses a field from another table. This query can be done because the [People] table is related to the [Company] table with a many to one relation:

   QUERY ([People]; [People]Last Name = "smith"; *)  ` Find every person named Smith…  
   QUERY ([People]; & ; [Company]State = "NY")   ` ... who works for a company based in NY

10. The following example finds the record for every person whose name falls between A (included) and M (included):

   QUERY ([People]; [People]Name < "n")  ` Find every person from A to M

11. The following example finds the records for all the people living in the San Francisco or Los Angeles areas (ZIP codes beginning with 94 or 90):

   QUERY ([People]; [People]ZIP Code = "94@"; *)   ` Find every person in the SF…
   QUERY ([People]; | ; [People]ZIP Code = "90@")   ` ...or Los Angeles areas

12. The following example queries an indexed subfield. The query returns a selection of parent records (records for the [People] table). It does not return a selection of subrecords. The result of the query would be the selection of records for all the people who have a child named Sabrina:

   QUERY ([People]; [People]Children'Name = "Sabrina") ` Find people with a child named Sabrina

13. The following example finds the record that matches the invoice reference entered in the request dialog box:

   vFind:=Request("Find invoice reference:")  ` Get an invoice reference from the user
   If (OK = 1) ` If the user pressed OK
      QUERY ([Invoice]; [Invoice]Ref = vFind)   ` Find the invoice reference that matches vFind
   End if

14. The following example finds the records for the invoices entered in 1996. It does this by finding all records entered after 12/31/95 and before 1/1/97:

   QUERY ([Invoice]; [Invoice]In Date > !12/31/95!; *)   ` Find invoices after 12/31/95…
   QUERY ([Invoice]; &; [Invoice]In Date < !1/1/97!)   ` and before 1/1/97

15. The following example finds the record for each employee whose salary is between $10,000 and $50,000. The query includes the employees who make $10,000, but excludes those who make $50,000:

   QUERY ([Employee]; [Employee]Salary >= 10000; *)  ` Find employees who make between… 
   QUERY ([Employee]; & ; [Employee]Salary < 50000)  ` ...$10,000 and $50,000

16. The following example finds the records for the employees in the marketing department who have salaries over $20,000. The Salary field is queried first because it is indexed. Notice that the second query uses a field from another table. It can do this because the [Dept] table is related to the [Employee] table with an automatic many to one relation. Although the [Dept]Name field is indexed, this is not an indexed query because the relation must be activated sequentially for each record in the [Employee] table:

   QUERY ([Employee]; [Employee]Salary > 20000; *)   ` Find employees with salaries over $20,000 and...
   QUERY ([Employee]; &;[Dept]Name = "marketing")   ` ...who are in the marketing department

17. The following example queries for information that was entered into the variable myVar.

   QUERY ([Laws]; [Laws]Text = myVar)  ` Find all laws that match myVar

The query could have many different results, depending on the value of myVar. The query will also be performed differently. For example:

• If myVar equals "Copyright@", the selection contains all laws with texts beginning with Copyright.

• If myVar equals "@Copyright@", the selection contains all laws with texts containing at least one occurrence of Copyright.

QUERY SELECTION.