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.

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 CS, but the basic principles are relevant to whichever Innovyze product is being used.

Example 1

The very simple example below shows a query to select pipes that are connected to an upstream node with chamber floor greater than 30.

Example 2

The example below shows a query to select nodes that are connected to an upstream conduit object with length greater than 300.

A node will be selected if the query is true for any of its upstream links.

Example 3

The example below shows a query to update the User Text 1 field of nodes that are connected to an upstream conduit object with length greater than 300.