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
• individual join tables: will show joined table fields in the Field dropdown list.

Connected objects e.g. us_links and array fields e.g. cctv details 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 InfoAsset Manager 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 InfoAsset Manager - Order by Clauses.





Asset network and user defined object functionality is only available if the InfoAsset Manager Suite option is enabled on your licence. Contact Innovyze for information about adding this option to your existing licence.

Please note that an underscore is added to the field name for user defined fields belonging to user defined objects to distinguish them from built in fields. For example a user defined field called myfield will appear as myfield_ in the Field dropdown list.

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.