Using the WHERE clause and Duplicate option in ModelBuilder to filter imported data

  Applies To 
  Product(s):  WaterGEMS, WaterCAD, HAMMER, SewerGEMS, SewerCAD, StormCAD, CivilStorm
  Version(s): V8i, CONNECT Edition
  Area:  Layout and Data Input
  Original Author: Scott Kampa, Bentley Technical Support Group

Using the WHERE Clause

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.

If you want to import multiple specific elements (from a list), you can use the OR operator but it is suggested that you use a text or whole number field. For example trying to import polylines with a specific shape length may not work well because floating point numbers that go out to many decimal places can be very difficult to match exactly.

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%'

Note: 

  • Subtypes: 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.
  • Spaces in column names - if you want to use the WHERE clause on a column whose name has a space, use brackets. For example [In Model] or [Year Installed].
  • NULLs: If you want to filter the data source to rows where the value in a specific column is null (blank), use the syntax IS NULL. For example if you have a column called "In Model" and you want to only import the data from that table where there is no value in that column (it is blank/null), then use the WHERE clause [In Model] IS NULL

Duplicating Tables

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.

See Also

Building a model using ModelBuilder

Recommended
Related