Selecting Objects Using SQL

SQL Queries can be used in InfoAsset Manager to select or deselect network objects by use of conditional expressions entered in the SQL Dialog. There are two types of selection query:

Some examples of queries used to select / deselect objects are presented below.

See SQL Syntax for more information on keywords.

See the SQL Examples section for further examples of the use of SQL in InfoAsset Manager.

Simple Selection Queries

Selections can be carried out easily by using SQL. By default, whenever an SQL Query is run, any network objects that meet all the criteria defined in the query are automatically selected when the query finishes.

The examples are taken from the Innovyze product InfoWorks ICM, but the basic principles are relevant to whichever Innovyze product is being used.

The most basic query does not require the use of any keywords:

<conditional expression>

e.g.

x > 100000

Selects all objects of the chosen object type (selected in the Object type field of the SQL Dialog) where the x coordinate > 100000.

This basic selection example selects all Node objects with x coordinate greater than 100000.

Complex Selection Queries

It is possible to override the Object type chosen in the SQL dialog and select / deselect objects in one or more objects types by using the FROM keyword.

SELECT FROM <object type> WHERE <conditional expression>

e.g.

SELECT FROM conduit WHERE conduit_length > 300

Selects all conduit objects with length greater than 300.

SELECT FROM SELECTED node WHERE x > 100000

Selects currently selected nodes with x coordinate greater than 100000. Nodes with x coordinate less than 100000 are removed from the current selection.

SQL Syntax

Updating Data Using SQL

SQL and Connected Objects

SQL and Array Data

SQL and Spatial Searches

SQL Examples