SQL View Tag

Not counted towards your tag license limit.

This is a tag intended for customers who are using VTScada's Remote Data Access and ODBC features to query the application's stored history. Use it to create views of data within your application, for use by SQL querying tools.

Without this tag, 3rd party query tools must connect to the virtual History table (or one of the time-aggregation modes of that table), which makes all information from all tags available. Typically, this is far more information than most 3rd party programs can work with. As an example of how many columns there can be in the virtual History table, consider just the data available from driver tags: each driver records seven values tracking the driver's health (quality, error, etc.). If you are querying a History_TPP table (data aggregation) then there are seven columns for every aggregation time period, for every driver.

Note also that VTScada will limit the number of columns returned in any query to 64.

VTScada stores history using only it's own proprietary format, which is not a relational database.
The SQL interface allows a limited subset of the SQL language to be used as if querying SQL tables.

Example:

In the following image, the table, LEVEL AND FLOW, as seen in a 3rd party ODBC query tool, is the result of an SQL View tag. It contains only data from tags selected for the view.

SQL View properties, View tab

Every view must have a name, which is configured on the View tab.

By default, views include only the historical values of tags selected in the Tag Filters tab. Use the Value Attributes and TPP Modes to extend the view to include selected flags that may be relevant to the data returned, or value aggregations such as averages or maximums over a defined time period.

If you are interested only in tag values and not in data aggregation over defined time periods, there is no need to select a TPP mode.

Optional attributes and calculations of an SQL View

View Name

Create a name for the view. This will be needed by your third-party viewing tool.

View names must be shorter than 55 characters. Spaces are allowed except at the beginning or end of the name. Punctuation characters are allowed except for underscore (_), colon (:), apostrophe (‘), and double quote (“).

If the View Name is cleared, after applying changes, the existing table registered by the same tag with the old view name will be unregistered (deleted).

Do not use an existing tag’s name as the view name. You will not be able to retrieve data from the matching tag.

Privilege

Select a custom security privilege from this drop down to limit the access to this view to only those operators who have been granted the matching security privilege.

Value Attributes

Value attribute related fields support only the BitwiseAND and BitwiseOR TPP mode. If value attributes are selected but neither BitwiseAND or BitwiseOR is selected, the result from a TPP query will not contain attribute columns.

Value!

A 6-bit binary with each bit representing one of the attributes (ManualData, ManualEntry,...) in the following list, in the order as shown.

It is not necessary to enable the Value! check box if you want only certain attributes. Enabling Value! will add the TagName:Value! columns to the view, where Value! is a 6-bit binary, with each bit representing an attribute. If you want only the EditedData attribute (TagName:Value!:EditedData), then select only the "EditedData" option.

ManualData

If Manual Data is set in a tag, it will be flagged to indicate that it is manual data rather than process I/O data. Select this option to include that flag in the view.

(TagName:Value!:ManualData)

ManualEntry, EditedData

Authorized users are able to add and Edit Data using the Historical Data Viewer. (Original values are always kept, and edited values are flagged as such.)

The difference between the two is that, if the timestamp associated with the new value has already been logged in the history, EditedData will be set (as the user is editing an existing value). If the new value is being added to a fresh timestamp, ManualEntry will be set.

(TagName:Value!:ManualEntry)

(TagName:Value!:EditedData)

Questionable

Values recorded while the Questionable Data flag will be marked as such. Select this option to include that flag in the view.

(TagName:Value!:Questionable)

Imported

Authorized users are able to Import Tag History. Select this option to include the flag that marks imported data as such.

(TagName:Value!:Imported)

Quality

Indicates a bad quality issue. "Good Quality" is defined as a value of "0 or Invalid" and "Bad Quality" is defined as any valid, non-zero value. Refer to the I/O and Calculations tag, topic: Quality Based on.

(TagName:Value!:Quality)

StaleData

The data has been logged again using a previous value. This happens most often if the driver has experienced a communication failure and the driver's Hold option is selected.

(TagName:Value!:StaleData)

CommFail

Indicates a failure to communicate with the device

(TagName:Value!:CommFail)

DriverCommFail

The PLC has reported a downstream communication failure.

(TagName:Value!:DriverCommFail)

DriverQuality

The PLC has reported a bad driver quality issue. Refer to the quality calculation described in Communication Driver Log-Enabled Variables

(TagName:Value!:DriverQuality)

DriverOverridden

The PLC has reported that a value has been manually overridden.

(TagName:Value!:DriverOverridden)

DriverNoData

The PLC has reported no data for an unspecified reason.

(TagName:Value!:DriverNoData)

TPP Modes

"TPP" stands for "Time Per Point". This is a time range over which data is aggregated. The length of time will be specified in the query by appending a suffix such as "_15M" to the history table. Your selection here is for the types of aggregation you want to make available to your query.

All the following are direct matches to the Modes specified in GetTagHistory.

Average

Average value over the time period.

Minimum

Minimum value over the time period.

Maximum

Maximum value over the time period.

Delta

Change in value over the TPP range.

ValueAtStart

The snapshot value at the beginning of the time period.

TimeOfMin

The time of the minimum value recorded during the period.

TimeOfMax

The time of the maximum value recorded during the period.

ZtoNZCount

The number of zero to non-zero transitions (starts) during the period.

NonZeroTime

Effectively, the running time during the period.

Total

The total of all values recorded during the period.

Interpolated

The snapshot value at the beginning of the time period. (Mode 10 is deprecated.)

RolloverTotal

The formula used is (RolloverCount * RolloverValue) + (LastValidValue - FirstValidValue).

Where, for any given time interval:

  • Rollovers are counted when a new value is smaller than the last valid value.
    (Use only for a tag that counts to a maximum and resets to zero. A tag with a fluctuating value will trigger many rollover counts.)
  • The RolloverValue is passed to the function in the parameter StaleTime.
  • LastValidValue starts with the last valid value in the previous time interval (if any), and is updated with each new valid value within the time interval.
  • FirstValidValue starts with the last valid value in the previous time interval (if any), and is updated with only the first valid value found in the current time span.

BitwiseOR

Performs a bit-wise OR of all the values stored during the time period. The result tells you which bits were set for every recorded value within that period.

BitwiseAND

Performs a bit-wise AND of all the values stored during the time period. The result tells you which bits were set for any recorded value within that period. (And conversely, which bits were not set during the time period.)

SQL View properties, Tag Filters tab

Use the Tag Selector tool within this tab to choose the tags that will be included in the view.

The Tag Selector is similar to the tool that you would use in the Historical Data Viewer to choose tags to plot. (Click the thumbnail image to expand.)

Sample tags are from a demo application.

The Tag selector can be widened as needed in order to view your tag names.

The upper list of the Tag Selector shows the tags in your application. Use filtering tools to limit which tags are shown in this list.

The lower list shows the tags that will be available in your SQL View. Move tags from the upper list to the lower, either by using the Select Tag button or by double-clicking the tag in the upper list. You may remove tags by the same method, except using the Remove button instead of Select Tag.

A powerful way to select a set of tags is to use a query. A query is simply the filter that you create using the tools at the top of this dialog. For example, setting the name filter to Station 1\* results in a query that includes only the tags within that station. Adding filters for area, type, etc. further restricts the set that will be included.

After defining the filters, use the Add Query button to save the filter set. You can save more than one query. Within a query, each filter further restricts the tag selection. When there are multiple queries, all tags that match any filter are included.

SQL View Tag Error Dialogs

Displayed when an existing name is reused. Select a new name then save again.

Displayed when an invalid character is used in the view name.

Examples: Querying for Attribute Data

The value attribute Value! and its bits column in an SQL View table of raw tag data will look like:

TagName:Value!, TagName:Value!:ManualData, TagName:Value!:EditedData. 

In an SQL View table of TPP data, this might look like:

TagName:Value!:BitwiseAND, TagName:Value!:ManualData:BitwiseAND, or TagName:Value!:ManualData:BitwiseOR

 

The following example shows a selection that includes attribute data. Note that the attribute columns must be wrapped in double quotation marks:

SELECT Timestamp, TagName:Value, "TagName:Value!:EditedData" FROM MyView

 

Value attributes support only the BitwiseOR and BitwiseAND TPP mode. For example:

SELECT Timestamp, "TagName:Value!:EditedData:BitwiseOR" FROM MyView_1D

will return data as long as "EditedData" and "BitwiseOR" are enabled in the SQLView tag. The query

SELECT Timestamp, "TagName:Value!:EditedData:Average" FROM MyView_1D

will return a "TagName:Value!:EditedData:Average - Column does not exist in table: MyView" error, even if the "Average" TPP mode is enabled.