Criteria Overview

Criteria establishes the condition(s) that records from an external data source must meet in order to be included in the cases that are returned by the query. Therefore, criteria is part of the SQL statement that is used to limit which records are returned to the STATISTICA Spreadsheet. To add criteria, use the options on the Add Criteria dialog, accessible by selecting Add from the Criteria menu, or select a field with the mouse pointer in the Graphic pane and drag it onto the Criteria tab.

When you select a criteria field, make sure that you use the field from the correct table. The following examples are taken from fictitious databases and are provided to illustrate the various ways criteria can be specified to retrieve records.

  • You can return all records that contain (or do not contain) certain value(s) you specify. For example, if you want to return only the customers who live in Louisville, specify that the field City in the Customer table equals Louisville.
  • You can return a range of records. For example, if you want to return all orders that are between $500 and $700, specify that the field ProductAmount in the Orders table is greater than or equal to $500 AND the field ProductAmount is less than or equal to $700.
  • You can return records that are one of (or are not one of) a specific group of items. For example, if you want to return sales that were not made in the United States, Switzerland, or Germany, specify the field Country in the Orders table is not one of the values United States, Switzerland, or Germany.
  • You can return records that are between (or are not between) specific values. For example, if you want to return orders that were purchased between December 5 and December 10, specify that the field Date in the Order table is between 12-5 AND 12-10. Note that STATISTICA Query will automatically include AND in this criteria when you specify between as your operator. You only need to select the two values.
  • You can return records that begin with, end with, or contain (or do not begin with, do not end with, or do not contain) certain characters. For example, if you want to return all customers with a 330 area code, specify that the field PhoneNumber in the Customer table begins with 330. Note that you can type in 330 with or without single quotes.
  • You can return a record that is like (or is not like) specific criteria. For example, if you want to retrieve the personnel information on an employee named Kasmirski, but you don't remember how to spell the name, specify that the field EmployeeName in the Personnel table is like Ka%.
  • You can return records that contain (or do not contain) missing or incomplete values. For example, if the IRS wants to identify the tax forms that have missing signatures, specify that the field Signature in the 1040 table is null. Note that when you choose the operator to be is null or is not null, the value field will no longer be active.
  • You can return records with values that meet one criteria or another criteria. For example, you can find all customers who live in Louisville or who have a 330 area code. To select these records, you will type two sets of criteria. First specify that the field City in the Customer table equals the value Louisville. Then, add another criteria, select the Or option button, and specify that the AreaCode field in the Customer table equals the value 330.
  • You can return records with values that meet multiple criteria. For example, you can return all the customers who live in Louisville and who purchased items in the months of November or December. To select these records, you will type two sets of criteria. First, specify that the field City in the Customer table equals the value Louisville. Then, add another criteria, select the And option button, and specify that the Month field in the Orders table is one of values November and December.

See Criteria Operators and Criteria Tab for further details.