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.

Related information:

Best Practices for Tags

The ID tab of every tag includes the same common elements: Name, Area, Description, and Help ID.

Name:

Uniquely identifies each tag in the application. If the tag is a child of another, the parent names will be displayed in a separate area before the name field.

You may right-click on the tag's name to add or remove a conditional start expression.

Area

The area field is used to group similar tags together. By defining an area, you make it possible to:

  • Filter for particular tag groups when searching in the tag browser
  • Link dial-out alarm rosters to Alarm tags having a particular area
  • Limit the number of tags loaded upon startup.
  • Filter the alarm display to show only certain areas.
  • Filter tag selection by area when building reports

When working with Parent-Child tag structures, the area property of all child tags will automatically match the configured area of a parent. Naturally, you can change any tag's area as required. In the case of a child tag, the field background will turn yellow to indicate that you have applied an override. (Orange in the case of user-defined types. Refer to Configuration Field Colors)

To use the area field effectively, you might consider setting the same Area for each I/O driver and its related I/O tags to group all the tags representing the equipment processes installed at each I/O device. You might also consider naming the Area property for the physical location of the tag (i.e. a station or name of a landmark near the location of the I/O device). For serial port or Roster tags, you might configure the Area property according to the purpose of each tag, such as System or Communications.

You may define as many areas as you wish and you may leave the area blank for some tags (note that for Modem tags that are to be used with the Alarm Notification System, it is actually required that the area field be left blank).

To define a new area, type the name in the field. It will immediately be added. To use an existing area, use the drop-down list feature. Re-typing an existing area name is not recommended since a typo or misspelling will result in a second area being created.

There is no tool to remove an area name from VTScada since such a tool is unnecessary. An area definition will exist as long as any tag uses it and will stop existing when no tag uses it (following the next re-start).

Description

Tag names tend to be brief. The description field provides a way to give each tag a human-friendly note describing its purpose. While not mandatory, the description is highly recommended.

Tag descriptions are displayed in the tag browser, in the list of tags to be selected for a report and also on-screen when the operator holds the pointer over the tag’s widget. For installations that use the Alarm Notification System, the description will be spoken when identifying the tag that caused the alarm.

The description field will store up to 65,500 characters, but this will exceed the practical limits of what can be displayed on-screen.

This note is relevant only to those with a multilingual user interface:
When editing any textual parameter (description, area, engineering units...) always work in the phrase editor. Any changes made directly to the textual parameter will result in a new phrase being created rather than the existing phrase being changed.
In a unilingual application this makes no difference, but in a multilingual application it is regarded as poor practice.

Help Search Key

Used only by those who have created their own CHM-format context sensitive help files to accompany their application. Refer to Custom Help Files

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.