SQL for selecting objects

SQL queries can be used in InfoWorks WS Pro 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 topics for further examples of the use of SQL in InfoWorks WS Pro.

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 most basic query does not require the use of any keywords:

<conditional expression>

For example,

x > 100000

selects all objects of the chosen object type (selected in the Object type field of the SQL dialog) where the x coordinate is greater than 100000.

ClosedShow image

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

Using the  SELECT and DESELECT keywords

The SELECT WHERE and DESELECT WHERE keywords can be used to select or deselect objects of the Object type currently chosen in the SQL dialog.

SELECT WHERE <conditional expression>

For example,

SELECT WHERE x > 100000

This query has the same effect as the query in the first example.

DESELECT WHERE <conditional expression>

For example,

DESELECT WHERE x > 100000

removes all objects of the chosen object type from the current selection.

Using the CLEAR SELECTION keywords

By default, SQL queries do not clear selections before being applied. The CLEAR SELECTION  keywords can be used to clear all currently selected objects.

For example,

CLEAR SELECTION; SELECT WHERE x > 100000

clears all currently selected objects and then selects all objects of the chosen object type where the x coordinate is greater than 100000.

Using the SELECTED and ALL keywords

The SELECTED and ALL keywords can be used to override the Apply filter to current selection option in the SQL dialog.

For example,

SELECT SELECTED WHERE x > 100000

selects objects of the chosen object type only if x is greater than 100000 and the object is in the current selection. Objects with x coordinate less than 100000 will be removed from the current selection.

Additionally,

SELECT ALL WHERE x > 100000

selects all objects of the chosen object type with x coordinate greater than 100000 regardless of the status of the Apply filter to current selection option.

Using date functions

Date functions NOW(), yearpart, monthpart and daypart can be used to determine the time between two events for a given object type.

The following examples are based on the Innovyze product InfoAsset Manager but the basic principles are relevant to whichever Innovyze product is being used.

Example 1

yearpart(when_surveyed) > 2002 AND yearpart(when_surveyed) < yearpart(NOW())

ClosedShow image

Finds all the CCTV surveys undertaken after the year 2002 and before the current year.

Object type: CCTV survey

Field: When_surveyed

Example 2

yearpart(when_surveyed) - year_laid < 2

ClosedShow image

Finds all the CCTV surveys that have been surveyed within 2 years of the pipes being laid.

Object type: CCTV survey

Field: When_surveyed

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>

For example,

SELECT FROM conduit WHERE conduit_length > 300

selects all conduit objects with length greater than 300.

Additionally,

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 and stored queries