SQL and Connected Objects

SQL queries can be carried out using the data fields of related or linked objects as well as the object type selected on the SQL Dialog.

Note

Time series expressions cannot be used in an SQL query for linked objects.

To see the full list of fields which can be queried for a specific object type, simply scroll through the list in the Field box of the SQL Dialog.

The options us_links.<field> and ds_links.<field> are available for all link and node objects. In addition the options us_nodes.<field>and ds_nodes.<field> are available for all link objects.

There are many other such linked objects depending on what has been selected in the Object Type field.

Note

Be careful not to confuse linked objects with array data as the syntax is the same - array.field and linked_object.field.

Some examples of SQL Queries using connected object data fields are given below. The examples are taken from the Innovyze product InfoWorks ICM, but the basic principles are relevant to whichever Innovyze product is being used.

Click on the images below to reveal the respective examples.

Example 1 Closed

Example 2 Closed

Example 3 Closed

SQL Syntax

Selecting Objects Using SQL

Updating Data Using SQL

SQL and Array Data

SQL and Spatial Searches

SQL Examples