Creating a SQL Query - PAS ICS Integrity - 7.3 - Help - Intergraph

ICS Integrity Help

Language
English
Product
PAS ICS Integrity
Subproduct
ICS
Search by Category
Help
PAS Version
7.3

You can use a SQL advanced query to search for a variety of objects and property data. These queries are the most flexible and allow you to combine results from multiple object types, other queries, or external data sources, and customize the formatting of the output columns. To create a SQL query, you need to understand SQL and how the object types in the asset model relate to one another.

To create and save a SQL advanced query:

  1. Click Queries > Create/Edit Queries in the left navigation bar.

  2. In Asset, select an asset for which you want to create the query. If you are creating a query for more than one asset of a specific type, select one asset of that type. The query will be listed for the asset you select.

  3. Click Create Query. The new query settings and criteria options are displayed in the right pane.

  4. In Query Type in the right pane, select SQL (Advanced).

  5. If you want to run this query against a list of assets in addition to the asset you selected, complete the following steps:

    1. Click From Asset(s). The Assets window is displayed.

    2. In Available Assets, select the assets you want to include in the query, and then click >. The selected assets are listed in Included Assets.

    3. Close the Assets window.

  6. Click SQL Query. The Advanced SQL Query window is displayed. This window allows you to build the SQL query statement. You can type the query you want, or use the data item selection options to help you build the SQL query statement.

  7. If you want to include data from objects for the selected asset, complete the following steps:

    1. In Select datasource, select Object Type.

    2. In the Available data items list, check the check box to the left of each table of data you want to include in the SQL Query.

    3. If you want the query to search cached data instead of the database, check Use Cache for each table you chose to include. Integrity returns data items from the first time the query was run.

    4. Click Add Data Items to add the selected tables to the query. The tables are added to the Selected data items list.

  8. If you want to include data from an existing query for the selected asset, complete the following steps:

    1. In Select datasource, select Query.

    2. Select whether you want to list Public, Private, or Built-in queries for this asset.

    3. In the Available data items list, check the check box to the left of each table of data you want to include in the SQL Query.

    4. If you want the query to search cached data instead of the database, check Use Cache for each table you chose to include. Integrity returns data items from the first time the query was run.

    5. Click Add Data Items to add the selected tables to the query. The tables are added to the Selected data items list.

  9. If you want to include data from an external data connection for the selected asset, complete the following steps:

    1. In Select datasource, select External data source.

    2. Select a defined connection for the selected asset. For example, you can select the tag server connection or the LCN Main connection, if one exists for the selected asset.

    3. In the Available data items list, check the check box to the left of each table of data you want to include in the SQL Query.

    4. If you want the query to search cached data instead of the database, check Use Cache for each table you chose to include. Integrity returns data items from the first time the query was run.

    5. Click Add Data Items to add the selected tables to the query. The tables are added to the Selected data items list.

  10. Click Create SQL to generate SQL from the selected data sources and populate the SQL* field with that SQL.

  11. In SQL*, edit the displayed SQL query statement as needed. For example, you can edit the JOIN clause to indicate the primary keys used to join the values of the selected tables.

  12. Click Update to transfer the text in the SQL* field to the SQL query you are creating.

  13. Close the Advanced SQL Query window.

  14. Click Run, and then review the results to make sure the query provides the results you want.

  15. Click Save As. The Save Query pane is displayed.

  16. In Enter a query name, type the name of the new query.

  17. Select whether to make the query Public or Private.

  18. If you want the query to be available for all assets of this type, check Save with Asset Model.

  19. Click OK.

  20. Click OK on the confirmation message.

Now you can run the query or use it as a basis for a report. For more information, see Running a Query.