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.

Example 1

SQL query context: Object type: Pipe

us_node.has_demand

selects all pipes that have at least one upstream node with demand.

Example 2

SQL query context: Object type: Node

SELECT 
 WHERE us_links.length > 50

selects all nodes that have at least one upstream link with a length greater than 50.

Example 3

SQL query context: Object type: Node

SET 
  user_text_1 = 'US link length > 50m' 
 WHERE us_links.length > 50

sets user_text_1 to "US link length > 50m" on all nodes that have at least one upstream link with a length greater than 50.