Create a SQL Query - Intergraph Smart Instrumentation - Help

Intergraph Smart Instrumentation Help

Language
English
Product
Intergraph Smart Instrumentation
Search by Category
Help
Smart Instrumentation Version
13.1

Only Select statements can be converted to run in the Query Builder, data and table altering, such as Insert, Delete, Update and so forth will not run.

  1. In the Reference Explorer, navigate to the Queries > SQL Editor folder.

  2. Right-click on the SQL Editor folder and on the shortcut menu click New > SQL Editor.

  3. In the Query Properties dialog box, type a Name and Description.

  4. Select Suppress Repeating Values if required.

  • When working with a Microsoft SQL database, selecting Suppress Repeating Values in combination with Note fields, causes the query/EDE to display incorrectly.

  • When any of the following Note attributes belonging to specific Item Types are included in your query, the complete EDE appears blank.

    Item Type

    Attribute Category

    Attribute

    Instrument

    General Properties

    Instrument Note

    Loop

    Identification

    Loop Note

    Panel

    General Properties

    Panel Note

    Process Data (Shared)

    General

    Calculation Sheet Program Notes

    Process Data Sheet Notes

    Spec

    Spec Sheet

    Spec Note

    Maintenance Event Activities

    Custom Fields

    Work Activity Text — User Defined Fields (UDFs) 001, 002, and 003.

  • When adding one of these Note attributes, when the software detect that it is not compatible with your query a message is displayed on the screen, but the software does not remove the attribute from the query, this you must do manually.

  • Browser views that were converted to EDEs after upgrading from version 2013 that include any Note field, appear completely blank when opened. To correct these issues, in the Edit EDE Definition dialog box clear the Suppress Repeating Values check box.

  1. Click OK.

  2. In the SQL Editor, write the SQL statement.

    SHARED Tip You can also paste an existing SQL statement.

  3. Do one of the following:

    • Click Actions > Run Query.

    • On the keyboard press CTRL +L.

    SHARED Tip The data from your query appears in the Data Preview pane, where you can review, search through, sort, or filter the results.

  4. Click Actions > Save Query.

  • Conversion from SQL Editor to Query Builder files has its limitations. Not every SQL query can be converted or correctly deciphered. The following are examples of non-supported SQL syntax:

    • Non-explicit properties (wildcards) such as Select * from.

    • Sub-queries.

    • Union statements.

    • Computed fields.

    • Hidden Smart Instrumentation properties.

    • where not exists statements.

    • SQL Functions such as Insert/Update/Delete/Alter and so forth.

    • Input variables.

    • Semicolon ";" is not valid.

  • The SQL statement cannot be saved when the SQL contains duplicate column names without an alias.