Column Aliases in SQL Queries

Introduction

In an SQL SELECT statement you can use a column alias to assign a specific name to a column. Normally if you use a scalar expression in an SQL SELECT statement, the column name is blank. The alias feature is useful to set a specific column name. For example:

SELECT ROUND( Value, 4 ) AS "Value"
FROM TagDataPointAnalog

Certain column aliases, beginning with the number sign (#) character, have a special meaning when used with the SQL Test Tool. These column aliases turn on features such as row color, row blink or row menu. The column alias values that have special meaning include:

Alias	Description
#RowColor	The column value is converted to an integer, and the value defines the row color
#RowBlink	The column value is converted to a boolean value, and the value defines whether the row blinks or not
#RowMenu	The column value is converted to a string, and the value defines the context menu associated with the row

To see examples of how these column alias values are used, browse the standard SQL queries:

From the menu, select File followed by Open Query.
Choose a query to view.
See the corresponding SQL statement in the SQL window.

#RowMenu: Set row menu

To use the #RowMenu column alias, you need to construct a column alias using the correct syntax. Two types of row menu are supported:

The standard tag menu
The standard alarm menu

Standard Tag Menu

The Standard Tag Menu provides access to the features of a tag. The standard tag menu is defined by a string using this syntax:

Tag:full-tag-name [ | menu-options]

Where:

full-tag-name is the full name of the tag used to build the menu.
menu-options is an optional comma separated list of menu options to include.

For more information on these parameters see Standard Tag Menu Parameters.

To obtain a string in this format use the following SQL expression in a select list:

'Tag:'||FullName AS "#RowMenu"

Note: The || operator concatenates the string 'Tag:' with the full name. This should result in a tag menu string with the correct format.

Standard Alarm Menu

The standard alarm menu provides these options:

View Alarm: Displays a dialog showing information about the alarm
Acknowledge: Acknowledges the alarm without comment
Acknowledge with Comment: Prompts the user for a comment then acknowledges the alarm.

The standard alarm menu is defined by a string using this syntax:

Alarm:source-name,condition-name,active-time,cookie

To obtain a string in this format use the following SQL expression in a select list:

'Alarm:'||SourceName||','||ConditionName||','||Cast(ActiveTime as VARCHAR)||','||Cast(Cookie AS VARCHAR) AS "#RowMenu"

Further Information

SQL Test Tool

To learn about the SQL Test Tool in Fernhill SCADA.

Fernhill SCADA Structured Query Language (SQL)

To learn about the structured query language (SQL) supported by Fernhill SCADA.

Glossary

For the meaning of terms used in Fernhill SCADA.