This dialog allows you to apply filters to a database table which is to be used in a sample list. It thus allows you to control which records in the table appear in the sample list. It is reached by clicking sampling in the external data details dialog.
There are two ways to define the filter; either using the GUI interface or by typing a SQL script. Once you have finished defining your filter, click OK.
The following options are available:
By GUI: Select this option to specify the filter by using the GUI interface (by building logical expressions using the mouse).
By Script (SQL): Select this option to specify the filter by entering a SQL script.
Limit number of records to: If you select this option, you can specify the maximum number of records that will be extracted from the database table and included in the sample list. For example, if you wanted to limit your sample list to 1000 individuals, you would enter 1000.
This method allows you to define up to four logical conditions that define which records are returned from the selected table.
To add the first logical condition, click .... The filter condition dialog appears, where you can define the condition. Once you have defined the condition, you can add further conditions by selecting the logical operator (AND/OR) that joins the two conditions, then clicking the second ... buttons to define the condition, and so on, up to four conditions.
The logical conditions are grouped into pairs, so the first pair are evaluated, then the second pair, and then the results of the two pairs are evaluated together. For example, if the first pair evaluates true and the second pair evaluates false, then the combined pairs will evaluate as follows:
The following options are available when using the GUI interface:
...: Click one of these buttons to add a new logical condition (or edit an existing one). The filter condition dialog appears.
AND/OR: Select the logical operator that joins two conditions (you must select an appropriate operator before you can add a second, third, or fourth condition).
The following options are available when using the script interface:
Parameters: Select a field from the table you specified in the external data details dialog. Click insert to place the selected field into the SQL code box (see below for details).
SQL Code (WHERE clause): In this box, enter your SQL code. You can perform a syntax check on your code at any point by clicking check. If there is an error in your code, a message will appear explaining the details of the error. Note that this check validates the syntax of your SQL code only, not the logic. In other words, it identifies code which will cause a SQL error, but it does not guarantee that your SQL code will extract the records you intend it to.