Use the SQL Query field on the SQL Export tab to enter the required SQL query. This is the SQL Query that Geo SCADA Expert is to execute whenever the SQL Export item’s Export SQL Query Result pick action is triggered. The query determines the area of the database that Geo SCADA Expert interrogates (‘queries’) for data, along with any restrictions that Geo SCADA Expert is to apply to that interrogation, whenever the pick action is triggered.
Geo SCADA Expert automatically exports the result of the SQL Query in the format and to the location specified using the rest of the fields on the SQL Export tab (see Configure the SQL Export Properties). The SQL Query determines the order in which the queried data values are listed in the output file.
Any SQL queries should be written by engineers who have the appropriate experience and knowledge of the SQL language. Running a badly designed SQL query can have an adverse affect on the system. All queries should include suitable restrictions to limit the number of records that are retrieved from the database. An SQL query that queries vast amounts of data will affect the performance of the server while that query executes.
Full guidance on using SQL is outside the scope of this guide. A short example is given below.
Geo SCADA Expert uses SQL to query the database in order, for example, to populate the various Queries Lists that you can display in Geo SCADA Expert. Depending on the data that you want to query in the database, you may find it useful to base your SQL query on such a query. For more information, see Ascertain which SQL Query is Presently used by a List in the Geo SCADA Expert Guide to Lists.
We recommend using QueryPad to test your SQL queries and improve their efficiency. QueryPad is an SQL query diagnostics tool that is supplied with Geo SCADA Expert. For more information, see the Geo SCADA Expert Guide to QueryPad.
Example:
The Configuration Changes Auditing feature is enabled on a particular Geo SCADA Expert system, to enable Geo SCADA Expert to keep a detailed record of the configuration changes that have occurred on that system.
An SQL Export item is used to export a list of those configuration changes that have occurred automatically over the last 24 hours, as a result of other configuration changes being made (configuration changes that have a ‘Database Internal’ origin).
When exported, the queried data is to be listed in ObjectName order, followed by RecordTime (the time that the change occurred).
The SQL Export item uses this SQL Query:
SELECT TOP( 500 )
"RecordId", "RecordTime", "ObjectName", "FieldName", "OldValue", "NewValue", "User"
FROM
CDBCONFIGCHANGES
WHERE
( "RecordTime" BETWEEN { OPC 'H-23H' } AND { OPC 'H+1H' } ) AND ( "OriginDesc" = 'Database internal change' )
ORDER BY
"ObjectName" DESC, "RecordTime" DESC
The Include Column Names check box on the SQL Export Form is used to specify whether the column names listed in the SQL Query’s SELECT Clause are included in output file (the file that is generated when the Export SQL Query Result pick action is triggered).
Further Information
Configuration Changes Auditing: see Configuration Changes Auditing in the Geo SCADA Expert Guide to Core Configuration.
For more information on SQL features supported by Geo SCADA Expert, see the SQL Guide.