Share
 
 

SQL dialog - SQL page

The SQL page lets you build your SQL query.

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.

Tip: You may encounter a parsing error when you have included a field name which starts with a number. It is recommended that you place such field name between [ ] in your SQL query. For example, instead of writing SET 2d_pt_id = subcatchment_id, you can use SET [2d_pt_id] = subcatchment_id.

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

Field Description

Object Type

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

Field Type

Drop-down 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 drop-down list:

  • <normal>: (default) will show fields related to the object type in the Field drop-down list.
  • sim: will show results fields in the Field drop-down list.
  • joined: will show joined table fields in the Field drop-down list.

Field

Drop-down 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 to type can be used in queries.

    • Example 

    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 drop-down list.

Search Type

Drop-down list of available search types for use with the layers currently loaded in the GeoPlan.

The available options are:

Search type Description

Cross

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

Inside

Map Control Description

ArcGIS

Searches for network objects completely inside a layer polygon.

MapX and MapXtreme

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

Contains

Searches for network polygons with objects completely inside the polygon.

Distance

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

Nearest

Searches for nearest network object within a specified Distance. If there is more than one object within the specified distance, the first one found is returned as the result.

Distance

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

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

Layer Type

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

Layer Type Description
Network layer Object layer in the current network.
GIS layer Additional GIS background layer displayed behind the network in the GeoPlan.

Layer

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

Drop-down 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).

Field

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

Drop-down 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.

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.

Parent topic: SQL dialog

Was this information helpful?