The WHERE clause is available in the first step of ModelBuilder when importing external data. It uses standard SQL syntax to enable you to filter the data in a particular table in the source file before importing into a model. As an example, you could use this if you only wanted to import pipes of a certain material, or only a subset of valve types (in a case where a single valve feature represents multiple types of valves with a field differentiating them.)
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 clause box.
The "WHERE" clause applies to the layer that you select on the left side, and different "WHERE" clauses can be specified for different layers. In the screenshot above, the WHERE clause 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 clause will be similar to the following: Material = 'Ductile Iron'. Note the use of a single quotation mark around the material type.
If the field name has spaces, use brackets around it. For example: [internal diameter] = 16
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 clause and the expected syntax, see the Help topic "Specifying a SQL WHERE clause in ModelBuilder" or online documentation for SQL syntax.
Here is an example of using the LIKE operator for a "ValveType" field which contains various codes for different valve types such as 'PRV12345': ValveType LIKE 'PRV%'
The duplicate option (button) in the first ModelBuilder step (to the left of the WHERE clause) is often used in conjunction with the WHERE clause. This can be especially useful if you have a single table in the data source that spans multiple element types. For example a "valves" table that includes rows for PRVs, PSVs and FCVs in WaterCAD or WaterGEMS. These are different node types in WaterCAD and WaterGEMS and a particular table can only be mapped to one. So, you could use the duplicate button to include this table three times, and use a separate WHERE clause in each one to filter it only to the respective node type (assuming there is something that distinguishes them). Later in in the ModelBuilder steps, you can then specify the respective different element types for the different duplicated tables.
Building a model using ModelBuilder