Using SQL

InfoAsset Manager implements its own subset of SQL (Structured Query Language) for selecting and updating network objects using specified criteria.

Time series expressions, which enable you to analysis and extract individual values and statistics from time series database streams that are linked to objects in a network, can also be used in an SQL query.

An SQL query consists of a number of clauses separated by semi-colons. Each clause can do one of the following:

The SQL query is built on the SQL Dialog. To open the dialog, choose SQL select from the Selection menu, or click the button from the Selection toolbar.

Creating an SQL Query

To create an SQL Query

  1. Open a network. One of the network views must be the Current Active Window.
  2. Choose SQL select from the Selection menu, or click the button from the Selection Toolbar. This opens the SQL Dialog.
  3. In the SQL Dialog, click on the SQL tab to open the SQL Page.
  4. On the SQL Page select the options to be used when running the query:
    • Choose the type of object to be queried from the Object Type dropdown list. (The object type selected can be overridden by use of the FROM keyword. See SQL Syntax for more details.)
    • Choose GIS select options, if using a GIS background layer as part of the query process.
    • Apply the query to currently selected objects only by checking the Apply as Filter to Current Selection option. (The setting of this option can be overridden by use of the keywords ALL and SELECTED. See SQL Syntax for more details.)
  5. Enter the SQL query in the text box at the bottom of the dialog. The Field dropdown only contains valid field names for the object type selected and the GIS Field dropdown list only contains valid GIS fieldnames for the GIS Layer selected. Choosing fields from either of these lists automatically adds them to the query.
    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.
  6. Validate the syntax by clicking the Test button
  7. Carry out the query by clicking the Apply or Run button. (The dialog is closed after the query has been run if the Run button is used, but will remain open if the Apply button is used.)

To save the query into an SQL Query group, click the Save button. Remember to do this before hitting Run. You can save later if you used Apply.

Tip: Click the Save As button on the SQL Dialog to save the query in the database tree. Once the query has been saved, it can be run by dragging the Stored Query object from the Explorer Window into the GeoPlan Window.

Multiple queries can be run by dragging the Asset Group containing the queries into the GeoPlan Window. All Stored Queries in the group will be applied in alphabetical order.

Viewing an SQL grid

The results of an SQL query can be viewed on a grid. The contents of the grid can also be saved in a CSV file.

To view an SQL grid

  1. Create and run the SQL query.
  2. Click on the SQL Grid tab and choose the fields to include on the grid.
  3. Click on Open As Grid.

For details of the grid and how to export it to a CSV file, see SQL Query View.

Further Details

See the following topics for more information:

Topics in this section
Parent topic: Database Items

Related Information