Limiting Retrieved Cases

The Limit Retrieved Cases step allows you to specify the criteria to select subsets of cases (rows). Limiting cases generally consists of filling the criteria grid with criteria. Criteria consist of two expressions and some relation between them. The expressions return a value of true, false, or missing for each case.

  • If the result is true, the case is selected.
  • If the result is false or missing, the case is not selected.
  • Most criteria use one or more of the six relational operators (<, >, <=, >=, =, and <>).
  • Expressions can include field names, constants, arithmetic operators, numeric and other functions, and logical variables. You can use fields that you do not plan to import as variables.

To build your criteria, you need at least two expressions and a relation to connect the expressions.

  1. To build an expression, choose one of the following methods:
    • In an Expression cell, type field names, constants, arithmetic operators, numeric and other functions, or logical variables.
    • Double-click the field in the Fields list.
    • Drag the field from the Fields list to an Expression cell.
    • Choose a field from the drop-down menu in any active Expression cell.
  2. To choose the relational operator (such as = or >), put your cursor in the Relation cell and either type the operator or choose it from the drop-down menu.

    If the SQL contains WHERE clauses with expressions for case selection, dates and times in expressions need to be specified in a special manner (including the curly braces shown in the examples):

    • Date literals should be specified using the general form {d 'yyyy-mm-dd'}.
    • Time literals should be specified using the general form {t 'hh:mm:ss'}.
    • Date/time literals (timestamps) should be specified using the general form {ts 'yyyy-mm-dd hh:mm:ss'}.
    • The entire date and/or time value must be enclosed in single quotes. Years must be expressed in four-digit form, and dates and times must contain two digits for each portion of the value. For example January 1, 2005, 1:05 AM would be expressed as:

      {ts '2005-01-01 01:05:00'}

    Functions. A selection of built-in arithmetic, logical, string, date, and time SQL functions is provided. You can drag a function from the list into the expression, or you can enter any valid SQL function. See your database documentation for valid SQL functions.

    Use Random Sampling. This option selects a random sample of cases from the data source. For large data sources, you may want to limit the number of cases to a small, representative sample, which can significantly reduce the time that it takes to run procedures. Native random sampling, if available for the data source, is faster than IBM® SPSS® Statistics random sampling, because IBM SPSS Statistics random sampling must still read the entire data source to extract a random sample.

    • Approximately. Generates a random sample of approximately the specified percentage of cases. Since this routine makes an independent pseudorandom decision for each case, the percentage of cases selected can only approximate the specified percentage. The more cases there are in the data file, the closer the percentage of cases selected is to the specified percentage.
    • Exactly. Selects a random sample of the specified number of cases from the specified total number of cases. If the total number of cases specified exceeds the total number of cases in the data file, the sample will contain proportionally fewer cases than the requested number.

    Note: If you use random sampling, aggregation (available in distributed mode with IBM SPSS Statistics Server) is not available.

    Prompt For Value. You can embed a prompt in your query to create a parameter query. When users run the query, they will be asked to enter information (based on what is specified here). You might want to do this if you need to see different views of the same data. For example, you may want to run the same query to see sales figures for different fiscal quarters.

  3. Place your cursor in any Expression cell, and click Prompt For Value to create a prompt.