You are currently reviewing an older revision of this page.
How is the WHERE statement in ModelBuilder used?
The WHERE statement is a SQL query that allows a user to filter the data in the source file before importing the data into a model file. As an example, you could use this if you only wanted to import a certain pipe material.
In the first step of ModelBuilder, you specify the data source. After selecting the data source type and choosing the data source file itself, you can filter this checking the box next to the WHERE statement box.
The "WHERE" statement applies only for selected layer (in this case, "PIPE []"), and different "WHERE" statements can be specified for different layers. In the screenshot above, the WHERE statement is filtering the list of pipes to show only the pipes with a diameter of 16. Only these pipes would be included in the import at the end of the ModelBuilder process.
If the column you want to filter on is a text field, the syntax for the WHERE statement will be similar to the following: Material = 'Ductile Iron'. Note the use of a single quotation mark around the material type.
Supported comparison operators are: <, >, <=, >=, <>, =, IN and LIKE. Multiple logical statements can be combined by using AND, OR and NOT operators. Parentheses can be used to group statements and enforce precedence. The * and % wildcard can be used interchangeably in a LIKE statement. A wildcard is allowed at the beginning and/or end of a pattern. Wildcards are not allowed in the middle of a pattern.
For more information on the WHERE statement and the expected syntax, see the Help topic "Specifying a SQL WHERE clause in ModelBuilder".
Note: Some source file types, such as SDE files, allow the use of subtypes. If these exist in your source file, a WHERE statement will need to be included for each subtype.
Building a model using ModelBuilder