Example queries

For the example queries below, the following data records have to be contained in the archives (for clarity, only selected columns are shown):

HISTVAL table

DPNAME DPTYPE

ORIGINAL

VALUE

STIME

INVALID

_BIT

valve1 valve 1 2001.01.30 10:31:02.211 0
valve2 valve 1 2001.01.30 10:31:02.212 0
valve3 valve 1 2001.01.31 02:11:01.111 0
valve1 valve 0 2001.01.31 02:31:02.211 0
valve1 valve 1 2001.01.31 02:35:02.211 1
red1.m.switch motor 1 2001.01.31 02:38:02.211 0
red1.f.sw1 motor 1.5 2001.01.31 10:31:02.211 0
red1.speed motor 1.7 2001.01.31 10:32:02.211 0
red1.m.temp motor 63.1 2001.01.31 10:33:02.211 0
red2.m.temp motor 66.4 2001.01.31 10:33:02.212 1
red1.m.temp motor 61.2 2001.01.31 10:33:02.213 0
red2.m.temp motor 64.3 2001.01.31 10:33:02.214 0
red3.m.temp motor 65.3 2001.01.31 10:33:02.215 0
red1.m.switch motor 0 2001.01.31 10:33:02.215 1

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"

DPNAME ORIGINALVALUE STIME
valve1 1 2001.01.30 10:31:02.211
valve2 1 2001.01.30 10:31:02.212

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.")

DPNAME ORIGINALVALUE STIME
red1.m.switch 1 2001.01.31 02:38:02.211
red1.m.temp 63.1 2001.01.31 10:33:02.211
red2.m.temp 66.4 2001.01.31 10:33:02.212
red1.m.temp 61.2 2001.01.31 10:33:02.213
red2.m.temp 64.3 2001.01.31 10:33:02.214
red3.m.temp 65.3 2001.01.31 10:33:02.215
red1.m.switch 0 2001.01.31 10:33:02.215

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"

DPNAME ORIGINALVALUE STIME
red1.m.switch 1 2001.01.31 02:38:02.211
red1.f.sw1 1.5 2001.01.31 10:31:02.211
red1.speed 1.7 2001.01.31 10:32:02.211
red1.m.temp 63.1 2001.01.31 10:33:02.211
red2.m.temp 66.4 2001.01.31 10:33:02.212
red1.m.temp 61.2 2001.01.31 10:33:02.213
red2.m.temp 64.3 2001.01.31 10:33:02.214
red3.m.temp 65.3 2001.01.31 10:33:02.215
red1.m.switch 0 2001.01.31 10:33:02.215

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*.*."

DPNAME ORIGINALVALUE STIME
red1.speed 1.7 2001.01.31 10:32:02.211

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."

DPNAME ORIGINALVALUE
red1.m.temp 63.1
red2.m.temp 66.4
red3.m.temp 65.3

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.

ABBR DPNAME DPTYPE STIME ACK_TIME PRIOR ACK_TYPE
A valve1 valve 2002.02.15 08:15:03.213 2002.02.15 08:15:07.213 60 TYPE_3: 'Incoming alert' is acknowledgeable
A red1.f.schl1 motor 2002.02.15 08:20:01.202 2002.02.15 08:21:01.204 60 TYPE_4: Alert pair requires acknowledgement
aA red1.m.switch motor 2002.02.10 09:15:01.209 2002.02.10 09:16:01.209 20 TYPE_4: Alert pair requires acknowledgement
aA red1.f.schl1 motor 2002.02.15 08:20:01.202 2002.02.15 08:20:01.202 20 TYPE_3: 'Incoming alert' is acknowledgeable
A red1.rot motor 2002.02.16 12:22:01.201 2002.02.16 12:22:01.201 60 TYPE_3: 'Incoming alert' is acknowledgeable
A red1.m.temp motor 2002.02.16 12:22:01.202 2002.02.16 12:22:01.202 60 TYPE_3: 'Incoming alert' is acknowledgeable
aA red2.m.temp motor 2002.02.16 12:22:01.203 2002.02.16 12:23:03.203 20 TYPE_3: 'Incoming alert' is acknowledgeable

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:

ABBR DPNAME STIME ACK_TIME PRIOR ACK_TYPE
aA red1.m.switch 2002.02.10 09:15:01.209 2002.02.10 09:16:01.209 20 TYPE_4: Alert pair requires acknowledgement
A red1.m.temp 2002.02.16 12:22:01.202 2002.02.16 12:23:01.202 60 TYPE_3: 'Incoming alert' is acknowledgeable
aA red2.m.temp 2002.02.16 12:22:01.203 2002.02.16 12:23:03.203 20 TYPE_3: 'Incoming alert' is acknowledgeable

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"

DPNAME STIME ACK_TIME ACK_TYPE
valve1 2002.02.15 08:15:03.213 2002.02.05 08:15:03.213 TYPE_3: 'Incoming alert' is acknowledgeable
red1.f.schl1 2002.02.15 08:20:01.202 2002.02.16 12:22:01.202 TYPE_3: 'Incoming alert' is acknowledgeable
red2.m.temp 2002.02.16 12:22:01.203 2002.02.16 12:22:01.203 TYPE_3: 'Incoming alert' is acknowledgeable