Share
 
 

SQL - SQL Page

The SQL tab on the SQL dialog lets you build your SQL query.

A simplified version of the tab, without the spatial search settings, is displayed when editing SQL expressions for use in Themes.

If you are familiar with SQL queries, you can type in a query manually (using the Field Type and the Field boxes to insert the fields you need). Details of the SQL statements allowed in InfoAsset Manager can be found in the SQL Syntax topic. Details of time series expressions that can be used in an SQL query can be found in the Theory of Time Series Expressions topic.

Alternatively, click on the Builder >> button. This displays 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 entering SET 2d_pt_id = subcatchment_id, you can use SET [2d_pt_id] = subcatchment_id.

For information on the dialog buttons, see SQL Dialog.

Field

Description

Object Type

Dropdown of network objects

Field Type

Dropdown 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.

Field

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.

    • Example

    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. Please contact us for information about adding this option to your existing licence.

    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.

Display Flag Fields check box

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

Display precision

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.

Search Type

Dropdown of available spatial search types for use with 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

Layer Type

Description

ArcGIS Engine/

ArcGIS Desktop

GIS Layer

Searches for network objects completely inside a GIS layer polygon

MapXtreme GIS Layer

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

Network / background network layer

Searches for network objects with object centre inside a network layer or background network layer polygon

Contains

Searches for network zones with objects completely inside the zone.

Distance

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

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
Background layer

Network layer of network opened as a background network in the GeoPlan

GIS layer

Additional GIS background layer displayed behind the network in the GeoPlan

Spatial rain layer

The rainfall radar cells used in the simulation. This option is only available if the results of a simulation are opened on the GeoPlan.

Distance

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.

Layer

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

Field

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.

Builder

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.

Parent topic: SQL Dialog

Related Information

Was this information helpful?