SQL page

The SQL page lets you build your SQL query.

ClosedShow image

If you are familiar with SQL queries, you can type in a query manually (using the Field Type and the Field drop-down lists to insert the fields you need). Details of the SQL statements allowed in InfoWorks WS Pro can be found in the SQL syntax topic.

Alternatively, you can click Builder >> to display a set of buttons representing the operators that you can include in your query.

You can represent constant values in the query by typing the name of a user macro instead of a value. User macros are highlighted in blue.

If you are viewing this page on the SQL dialog, see SQL dialog for further options.

Field Description

Object Type

Dropdown list of the network object(s) for which you can write a SQL expression.

Field Type

 

Dropdown list of the data fields that are applicable to the 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.
  • sim: will show results fields in the Field dropdown list.
  • joined: will show joined table fields in the Field dropdown list.

Field

Dropdown list of data fields applicable to the field type (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 may 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. Click the Example button below to reveal a couple of examples of how otype can be used in queries.

    • Closed

    SELECT oid,otype on all links. Running this query will result in a grid listing each link and its type (such as 'conduit', 'pump', 'orifice', 'river', etc in InfoWorks CS) 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 WS Pro 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.

Display Flag Fields check box

Check to include flag fields in the Field dropdown list.

GIS Search Type

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

The available options are:

Search type Description

Cross

Searches for network objects which intersect a GIS layer line or polygon area.

Inside
Map Control Description

ArcGIS

Searches for network objects completely inside a GIS layer polygon.

MapX and MapXtreme

Searches for network objects with object centre inside a GIS layer polygon.

Contains

Searches for network polygons with GIS objects completely inside the polygon.

Distance

Searches for network objects within a Search Distance of a GIS layer line or polygon area.

Search Distance

This option becomes available when Distance is selected as the GIS search type.

Network objects within the search distance of a GIS layer line or polygon area will be selected.

GIS Layer

This option becomes available when a GIS Search Type is selected.

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

GIS Field

This option becomes available when a GIS Search Type is selected.

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

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

Builder >> (button)

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

SQL and stored queries