SQL Statements

Drainage and Utilities automatically provides SQL queries for obtaining the information it needs from the historian database (e.g. the list is available signal names, the data associated with those signals, etc).

The provided SQL statements will often work as is, but we do offer the ability to provide custom SQL statements for your historian database. This allows you to take advantage of optimizations and/or syntax that is specific to your particular historian.

Tip: Your historian will very likely provide a tool for viewing and querying historian data. You can use that tool to refine and test your custom SQL Statement to ensure it is returning the appropriate results before entering it into Drainage and Utilities.

The customizations available to you will vary depending on whether you are querying Real-Time or Historical data, or the format of your data (e.g. one-value or many-values per row).

To specify custom SQL statements, check the "Customize SQL Statements" checkbox (located at the top of the SQL Statements dialog).

If your source format is one-value-per-row, using historical-data, you can specify three custom queries:

Available Signals SQL Statement: This statement queries the database in a way to return the unique signals to use for the data source. Any valid SELECT statement is allowed.
Signal Data SQL Statement - Historical, One Value per Row: This statement returns the signal data for a given date range.
Date/Time Range SQL Statement: This statement will return the date range to use for the signal data.

If your source format is multiple-values-per-row, using historical-data, you can specify two custom queries:

Available Signals SQL Statement: This query is not applicable in this case.
Signal Data SQL Statement - Historical, Multiple Values per Row: This statement returns the signal data for a given date range.
Date/Time Range SQL Statement: This statement will return the date range to use for the signal data.

If your source format is one-value-per-row, using real-time data, you can specify two custom queries:

Available Signals SQL Statement: Returns the list of unique signals from the data source.
Signal Data SQL Statement - Real-time, One Value per Row: This statement returns the signal data.

If your source format is multiple-values-per-row, using real-time data, you can specify a single custom query.

Available Signals SQL Statement: This query is not applicable in this case.
Signal Data SQL Statement - Real-time, Multiple Values per Row: Returns the signal data.

Note: When custom queries are entered, they should have valid SQL syntax. Be sure to test them against your historian first (as described above). If things do not appear to be working as expected, review the SCADA log (from the SCADA Signals dialog) for warnings.

Note: The Advanced Options of the database connection do not apply to these queries.

Note: This dialog will be grayed out until either "Real-Time" or "Historical" is selected as the database source.