Example queries

HISTVAL table

Example

The following query retrieves all data points and original values for the source time of 30.1.2001:

SELECT DPNAME,ORIGINALVALUE,STIME FROM HISTVAL WHERE STIME BETWEEN "2001.01.30 01:00:00.000" AND "2001.01.31 01:00:00.000"

Example

The following query retrieves all data points, including original values and source time, with a data point name ending in "switch" or "temp":

SELECT DPNAME,ORIGINALVALUE,STIME FROM HISTVAL WHERE DPNAME IN ("*.*.switch.","*.*.temp.")

Example

In this query, all entries with a set INVALID_BIT of the data point type "motor":

SELECT (INVALID_Bit),DPNAME,ORIGINALVALUE,STIME FROM HISTVAL WHERE DPTYPE = "motor"

Example

The following query retrieves all data points, original values and source time, with a data point name beginning with "red" and containing precisely one dot (period character) in the name:

SELECT DPNAME,ORIGINALVALUE,STIME FROM HISTVAL WHERE DPNAME LIKE "red*.*."

Example

The following query retrieves the maximum original values for all switches (in ascending order of data point names):

SELECT DPNAME,MAX(ORIGINALVALUE)FROM HISTVAL WHERE DPNAME LIKE "*.*.temp." GROUP BY DPNAME ORDER BY DPNAME

Group functions such as MAX, MIN should only be used to query values of data points that are of the same type (see example above). Since there are data point elements of different types (Float, String, DynBool and others) a complete table cannot be queried correctly by such types and an error message is displayed.

If you use a data point name as filter criterion in an SQL statement, you always have to add a dot after the data point name as in WHERE DPNAME LIKE "red1.m.temp."

ALERTS table

It is not possible to execute shared access queries such as UNION and JOIN for the HISTVAL and ALERTS tables. You can only query closed intervals for the ALERTS table.

Example

The following query retrieves all data points ending with the word "switch" or "temp" and source time between 10.02.02-16.02.02:

SELECT ABBR,DPNAME,STIME,ACK_TIME,PRIOR, ACK_TYPE FROM ALERTS WHERE DPNAME IN ("*.*.switch.","*.*.temp.") AND STIME BETWEEN "2002.02.10 07:00:00.000" AND "2002.02.16 01:00:00.000"

The query shows priority abbreviation, source time, acknowledgement time, priority of alert range and type of acknowledgement of all data points with the above-mentioned names:

Example

The following query retrieves all data points during the period 14.02.2002-17.02.02

SELECT DPNAME,STIME,ACK_TIME FROM ALERTS WHERE STIME BETWEEN "2002.02.14 01:00:00.000" AND "2002.02.17 01:00:00.000"