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 scalar time series data streams that are linked to Scalar TSDobjects 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.

To create an SQL Query:

  1. Open a network. One of the network views must be the Current Active Window The current Active Window in any Microsoft Windows application has a differently coloured title bar to all the other windows. In the default colour scheme, the active window title bar is blue, all other windows have a grey title bar..
  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 list 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.
  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.

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.

See the following topics for more information:

SQL Syntax

Selecting Objects Using SQL

Updating Data Using SQL

SQL and Connected Objects

SQL and Array Data

SQL and Spatial Searches

SQL Examples

SQL Queries

SQL Dialog

Theory of Time Series Expressions