SQL Dialog - SQL Page

Dropdown list of network objects

Dropdown list of data fields applicable to network object selected in the Object Type box.

Selecting a Field Type from the list defines which fields will be available in the Field dropdown list :

• <normal>: default. Will show fields related to the object type in the Field dropdown list.
• flags: will show flag fields in the Field dropdown list. Flags for an object can be treated as an array, where the array contains two fields - value and name.
• network: will show network id and name fields in the Field dropdown list which allow queries to be carried out across networks
• sim: will show results fields in the Field dropdown list.
• risk: will show results fields for a risk analysis simulation or a risk analysis results object in the Field dropdown list.
• tsr: will show time series results fields in the Field dropdown list.

Connected objects e.g. us_links and array fields e.g. node storage_array are also available for selection.

Dropdown list of data fields applicable to parameter selected in the Field Type box.

Selecting a Field from the list inserts the field in the query.

When Field Type is set to <normal>, fields related to the selected Object Type will be displayed.

In addition the following will be available:

• oid (Object ID) - returns the primary key of the object. This can be used to get multi-part IDs for objects such as pipes that include link suffix as part of their ID.
• otype (Object Type) - used to return the object type. Please click on the Example button below to reveal a couple of examples of how otype can be used in queries.



SELECT oid,otype on all links. Running this query will result in a grid listing each link and its type (such as 'conduit', 'bridge', 'river reach', etc in InfoWorks ICM) being displayed.

ANY(us_links.otype<>'Conduit') on all nodes. This will select on the GeoPlan all the nodes for which any of the upstream links is not a conduit.



Note

If running InfoWorks ICM in a language other than English, it is important to note that the English words describing object types should be used and not their translation (for example, in the second query above, the word "Conduit" must be used, not its translation, for the query to work).

• rank (Rank) - used to return the rank, (position), of the object as it appears in an ordered list. See SQL in InfoWorks ICM - Order by Clauses.

Check the Display Flag Fields box to include flag fields in the Field dropdown list.

The Display precision dropdown box is only displayed when editing theme SQL expressions.

Select the number of decimal places to be shown when displaying the results of the SQL expression in labels.

Dropdown list of available spatial search types for use with layers currently loaded in the GeoPlan.

The available options are:

The Layer Type option becomes available when a spatial Search Type is selected.

The Distance option becomes available when Search Type Distance is selected.

Network objects within the specified Distance of a layer line or polygon area will be selected.

The Layer option becomes available when a spatial Search Type is selected.

Dropdown list of layers currently loaded in the GeoPlan (only those layers loaded in the GeoPlan Window before the SQL dialog is opened will be listed).

The Field option becomes available when a spatial Search Type is selected.

Dropdown list of fields within the Layer selected in the Layer box.

Selecting a spatial search Field from the list inserts the field in the query.

Click the Builder >> button to display a set of buttons representing the operators that can be included in the query. Click the Finish << button to close the builder.