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 |