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:
- Simple selection: a query that only selects / deselects objects of the type selected in the Object type list on the SQL Dialog.
- Complex selection: a query that selects / deselects objects of one or more types, not necessarily the type selected in the Object type list on the SQL Dialog
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.
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>
e.g.
SELECT WHERE x > 100000
This query has the same effect as the query in Example 1.
DESELECT WHERE <conditional expression>
e.g.
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.
e.g.
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 > 100000.
Using the SELECTED and ALL Keywords
The SELECTED and ALL keywords can be used to override the Apply as filter to current selection option in the SQL Dialog.
e.g.
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.
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 as filter to current selection option.
Using Date Functions
Date functions such as: NOW(), yearpart, monthpart and daypart, can be used in order to provide the user with means of writing queries 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.
Click on the images below to reveal the examples.
Example 1
Example 2
A more advanced example of an SQL query using functions to determine the time in months between the report of a customer complaint and its resolution can be found in the SQL example - Using Date Functions topic.
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.
