By applying criteria to one or more fields, you narrow your query to return only the information that you want.
You can use Cell as a data source for criteria, and you can make the Cell data source required.
-
Click the Criteria tab, and select the first field that is listed. If necessary, change the padding option and the source for look-up values.
(If you do not see these options, hover over the field.)
- Choose an operator for the field.
For example, to return data for a set of material numbers that begin with 100-, click Like.
Note:In Studio 11.x and 12.x, the Between operator includes both of the specified values as well as the values between them.
In Studio 11.2, the Between operator appears as >=*=< to represent that it is inclusive in Studio 11.x and 12.x.
The Between operator for Studio 10.x scripts that are run in Studio 11.2 appears separately as A > 2 and A < 3 to represent that it is not inclusive.
Studio 11.2 converts the 10.x From-To operator to Between; 10.x will display the 11.x and 12.x Between operator as From-To.
If you are using an InfoSet, P-type fields can use only the = operator. S-type fields can use any operator.
- Do one of the following:
- Enter the value. In the example, the value would be 100-*, where * is a wildcard.
If you want this value to be the same every time the query is run, click the arrow and then click Fixed Value.
Otherwise, the value is User-Defined, which means it can be set by the person who is running the script.
To ensure that a value is always entered in User-Defined criteria, click the Required icon.
- Excel only: If you want Query to use a value that is already in the Excel file, type the column or cell, click the arrow, and then click Cell.
For more information, see Use the IN operator and The LIKE operator and wildcard characters.
- Enter the value. In the example, the value would be 100-*, where * is a wildcard.
- To add more criteria to a field, select the field and click Add Condition on the ribbon.
- To add a field, click the plus sign next to Find All Items, and then click the field name.
If the field was not originally selected as a criteria field in the workspace, Studio automatically selects it.
In an InfoSet, you can add an Or condition to an S-type field. You cannot add conditions to P-type fields.
In an LDB, you can add one Or condition to a field.
A query statement cannot exceed 65,536 characters.
Preview the run
- Click the Run Time tab to see a preview of the Run Time Variable Wizard, which runners will see when they run the script.
- Enter or change the values for any user-defined criteria.
- Click the Preview Run button at the lower left of the Run Time box.
If the results in the Data Set panel match the data that you want to download, the next step is to map the fields.