SQL keywords
WHERE
With the option "WHERE" additional filters can be specified in an SQL statement that are not to be adopted from the FROM specification (for example, with filter options that are not possible in the FROM section).
When you query data point elements in a select statement, you have to use the data point type clause since WinCC OAuses IDs and the same data point element name may have different IDs for different DP types. Therefore, you may NOT use a query like:
string strQuery = "SELECT '.name:_original.._value, .Datapoint1.Element1:_original.._value' FROM '*'";
Instead use:
string strQuery = "SELECT '.name:_original.._value,.Datapoint.Element1:_original.._value' FROM '*' WHERE _DPT = \"ExampleDp_Float\"";
You have to enter the data point type also if the WHERE clause contains DP element names, for example, WHERE '.dpe' = 1!
Only config attributes can be used as WHERE condition in the SQL panel.
Do not use the same attribute twice in a SELECT clause.
To execute the query faster, specify the data point directly after the FROM statement.
See the relevant chapter of this section for explanations on the TIMERANGE() function or Group functions.
In this case only the values of the data point are written to the results table whose online value is greater than 45.
main()
{
dyn_dyn_anytype tab;
int z;
dpQuery("SELECT '_original.._value' FROM 'ExampleDP_Arg*' WHERE ('_original.._value' > 45) TIMERANGE(\"2007.10.06 09:00:00.000\", \"2007.10.06 10:55:00.000\",1,2)", tab);
/* Note that the query has to be written on one row without line breaks */
shape list1=getShape("SELECTION_LIST1");
for(z=2;z<=dynlen(tab);z++)
// dynlen(tab) Returns the length of tab.
setValue(list1,"appendItem",tab[z][2]);
}
The FROM statement in this example accepts the online values of all data points. Via the WHERE function, only data points of the type ExampleDP_Float are permitted.
dpQuery("SELECT '_online.._value' FROM '*'WHERE _DPT = \"ExampleDP_Float\" TIMERANGE(\""+start+"\",\""+stop+"\", 1, 2)", tab);
The FROM statement in this example accepts the online values of all data points. Via the WHERE function, only data points whose alias begins with an "A" are permitted.
dpQuery("SELECT '_original.._value' FROM '*' WHERE _ALIAS LIKE "A*"", tab);
The FROM statement in this example accepts the online values of all data points. Via the WHERE function, only data points whose comment contains "OP" are permitted.
dpQuery("SELECT '_original.._value' FROM '*' WHERE _COMMENT LIKE "*OP*"", tab);
In this example all data point elements of all data points (*.**) of data point type "PUMP1" from 09.07.2018 16.02 to 09.07.2018 16:22 are queried.
main()
{
dyn_dyn_anytype tab;
int z;
dpQuery("SELECT '_online.._value' FROM '*.**' WHERE _DPT = \"PUMP1\" TIMERANGE(\"2018.07.09 16:02:00.000\", \"2018.07.09 16:22:00.000\",1,2)", tab);
/* Note that the query must be written on one row without line breaks */
shape list1=getShape("SELECTION_LIST1");
for(z=2;z<=dynlen(tab);z++)
//dynlen(tab) returns the length of tab
setValue(list1,"appendItem",tab[z][2]);
}
WHERE and AND
With the keyword AND you can restrict a WHERE query. With the example query below, you can query all online values with leaf elements that have userbit7 set. The query has to be programmed on one line (without a line break) because otherwise a syntax error will be shown.
main()
{
dyn_dyn_anytype tab;
int z;
int length;
dpQuery("SELECT '_online.._value' FROM '*' WHERE _LEAF AND '_online.._userbit7' == 1",tab);
//select all online values with leaf
//elements that have the userbit7 set.
length=dynlen(tab);
DebugN("Length:",length);
for(z=1;z<=length;z++)
{
DebugN(tab[z]);
}
}
It is also possible to reduce the data by using the keywords for data points.
main()
{
dyn_dyn_anytype tab;
dpQuery("SELECT ALL '_online.._value' FROM '*' WHERE _DPT = \"ANALOG1\" AND _EL = \"C1.AVG_WT0\"", tab);
DebugN(tab);
}
or
main()
{dyn_dyn_anytype tab;
dpQuery("SELECT ALL '_online.._value' FROM '*' WHERE _DPT = \"ANALOG1\" AND _EL = \"C1.AVG_WT0\" AND DP = \"DP1 \"", tab);
DebugN(tab);
}
\"TRUE\" und \"FALSE\"
If you want to use a boolean comparison in a WHERE clause the key words TRUE or FALSE have to be written as follows (\"TRUE\"):
string SQLAbf = "SELECT '_online.._value' FROM '*' WHERE _LEAF AND '_online.._userbit7' == \"TRUE\" ";
The query can be used in the same way as the query in the "AND" example above.
TIMERANGE() function
The TIMERANGE() function is beyond SQL. It allows time-dependent queries to be run on data points but it must follow a SELECT statement. This function is the counterpart to the dpGetPeriod() functionality in WinCC OA, but is not available with Connect functions (since they connect to current values).
The syntax of the function is:
TIMERANGE (start , stop , modus , bonus)
Here, start and stop indicate the time range for which the data will be read out. It must be noted that start and stop are of the type string (and not of the type time) and must have the following format:
YYYY.MM.DD hh:mm:ss.ttt
1998.03.05 14:06:45.012
, as an example
In addition to "now", the keywords “min” and “max” can be used.
The option "now" can be used to query values as of a point in time until now or to query the current value:
If the handling of daylight saving time or different timezones are an important topic within the project, then the ISO8601 time format in strings must be used instead of default string format for time variables.
To do this, the ISO8601 time format must be activated with by setting the config entry [ui] useISOTimeformat.
[ui]
useISOTimeformat = 1
In the example below the values are written into a selection list.
/* Query as of 16.8.2017, 9 o'clock until now + 1 value before and after the interval.
Note that in order that the example works you need a selection list (In the example "SELECTION_LIST1").
Add the selection list to your panel. See Graphics editor GEDI ->
Selection list.
Add also
an archive config to the data point ExampleDP_Arg1. that is queried.*/
main(mapping event)
{
dyn_dyn_anytype tab;
int z;
shape list1=getShape("SELECTION_LIST1");
dpQuery("SELECT '_online.._value' FROM 'ExampleDP_Arg1' WHERE _DPT = \"ExampleDP_Float\"TIMERANGE(\"2017.08.16 09:00:00.000\", \"now\", 1, 1)", tab);
for(z=2;z<=dynlen(tab);z++)
// dynlen(tab) returns the length of tab
setValue(list1,"appendItem",tab[z][2]);
}
/* The following example returns the current value + 1 value before and after the interval. Note that in order that the example works you need a selection list
(In the example "SELECTION_LIST1").
Add the selection list to your panel. See Graphics editor GEDI ->
Selection list.
Add also
an archive config to the data point ExampleDP_Arg1. that is queried.*/
main(mapping event)
{
dyn_dyn_anytype tab;
int z;
shape list1=getShape("SELECTION_LIST1");
dpQuery("SELECT '_online.._value' FROM 'ExampleDP_Arg1' WHERE _DPT = \"ExampleDP_Float\"TIMERANGE(\"now\", \"now\", 1, 1)", tab);
for(z=2;z<=dynlen(tab);z++)
// dynlen(tab) returns the length of tab
setValue(list1,"appendItem",tab[z][2]);
}
"_corr.._manager" and "_corr.._user"
"_corr.._manager" and "_corr.._user" attributes can only be used in an SQL query within the "WHERE" clause together with the "TIMERANGE" keyword. The "_corr.._manager" attribute is only available for NGA.
This is a valid query with "_corr.._manager" attribute:
SELECT '_original.._value' FROM '*.**' WHERE '_corr.._manager' == 16844034 TIMERANGE("2023.09.10 16:29:56","2023.09.11 16:29:59",1,0)
ARCHIVENUM
By using the keyword ARCHIVENUM, a specific archive can be queried. In the example below, the archive with the number 2 is queried:
dpQuery("SELECT '_online.._value' FROM 'ExampleDP_Arg1' WHERE _DPT = \"ExampleDP_Float\" TIMERANGE(\"2005.10.26 00:00:00.000\", \"2005.10.26 23:59:59.000\", 1, 2) ARCHIVENUM 2", tab);
Modus - Modus = 1
Bonus
In addition to the modus parameter, the number of additional required values can also be transferred. So, for example, a modus 1 with bonus 2 returns all the values in accordance with standard behavior plus (at least) two events before and after the interval. The values before and after the limit must be specified for each attribute. This ensures that there are really two valid values for each column of the query.
with start ="1998.05.26 11:04:25.000" of the type string and stop ="1998.05.26 23:59:59.000" of the type string results in:
dpQuery("SELECT '_online.._value' FROM 'mld_*.**' TIMERANGE(\""+start+"\",\""+stop+"\",1,2)", tab);
SORT BY function - SORT BY x [ASC | DESC]
This function sorts the result by the x. entry between the keywords SELECT and FROM. The ASC parameter ensures that the data is sorted in ascending order (default setting), the DESC ensures sorting in descending order. This function is not available with Connect commands. SORT/ORDER BY 0 refers to the timestamp (first column of the result table). SORT\ORDER BY 1 refers to the first attribute after the 'SELECT' (second column of the result table). If you do not specify a sort order, the function sorts according to the timestamp.
dpQuery("SELECT '_online.._value', '_online.._stime'FROM 'mld_float.**' TIMERANGE (\"1998.05.2600:00:00.000\", .\"1998.05.26 23:59:59.000\",1,2) SORT BY 1 DESC", tab);
After retrieving the values (_online.._value and _online.._stime) in the specified time range, they are sorted in descending order after the first specification between the SELECT and FROM (here: _online.._value).
dpQuery("SELECT ALERT '_alert_hdl.._value', '_alert_hdl.._text' FROM 'mld_float.**' TIMERANGE(\""+start+"\",\""+stop+"\", 1, 2) SORT BY 2,1", tab);
Here the data is first sorted by the message text so that all messages with the same text are together. If three ranges are defined for alert handling, two groups will emerge (exceed or fall below the range). These two groups are then ordered by size.
Explanations on the TIMERANGE() function or Group functions can be found in the relevant chapter of this section.
ORDER BY function
See SORT BY function - SORT BY x [ASC | DESC]. The functions are identical with regard to their call and internal mode of operation.
GROUP BY function - GROUP BY x
This function puts the data into groups. First, the data must be sorted by the same column (see SORT BY).
The use of the GROUP BY function only makes sense with a group function since one line is returned as the result of grouping (exception, for example,. the sum of all values obtained from an SQL statement). Without the group function, the result column contains the entry of the last line of this group.
Three different grouping methods exist:
GROUP BY function by data point elements
with a number from 1 to n, where n is the number of data point parts in front of the FROM statement.
dpSet("mld_float.:_original.._value", 28);
delay(1);
dpSet("mld_float.:_original.._value", 5);
delay(1);
dpSet("mld_float.:_original.._value", 28);
delay(1);
dpSet("mld_float.:_original.._value", 5);
delay(1);
dpSet("mld_float.:_original.._value", 28);
delay(1);
dpSet("mld_float.:_original.._value", 5);
...
...
dpQuery("SELECT '_online.._value' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\",\"1998.05.26 23:59:59.000\",1,2) ORDER BY 1 GROUP BY 1", tab);
Here all the values occurring are ordered and grouped by size. There is one output line for each group. Thus all values that occur several times are combined in a group:
1 | 2 | |
1 | Type:0 Sys:0 Dp:0 El:0 Conf:29 Det:0 Attr:0 |
:_online.._value Type:131173 Sys:0 Dp:0 El:0 Conf:29 Det:0 Attr:0 |
2 |
System1: mld_float. Type:1 Sys:1651 Dp:1 El:0 Conf:0 Det:0 Attr:0 |
5 |
3 |
System1: mld_float. Type:0 Sys:1 Dp:1651 El:1 Conf:0 Det: 0 Attr:0 |
28 |
GROUP BY function by the time
Here, with the command SECS[x], the data can be grouped in packages of x seconds.
With MONTH the data read is grouped by the month. "0" must be specified as the sort characteristic.
dpQuery("SELECT 'MIN(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2) ORDER BY 0 GROUP BY SECS(60)", tab);
The data that occurred in the specified time range is sorted by the time (ORDER BY 0) and fitted into groups of 60 seconds. The minimum of these values is determined.
GROUP BY function by data point levels
If one of the following keys is used as the grouping characteristic, n groups are formed for n different occurrences within the key. Grouping by one of these characteristics requires that "0" is specified as the sorting characteristic.
The keywords for the components of the data points and types are:
_SYS, _DP, _EL and_ELC
For explanations on this, refer to "Keywords for the individual parts of a data point".
dpQuery("SELECT 'COUNT(_online.._value)' FROM 'mld_*.**' TIMERANGE(\"1998.05.26 11:04:25.000\",\"1998.05.26 23:59:59.000\",1,2) GROUP BY _DP", tab); SORT BY 0
The result is grouping by data points (if there are three different data points, there will be three groups). The number of saved values is generated for each data point.
LAST function - LAST x
With this command (at the most) the last x lines of the result table are returned, or less if the query is restricted by other conditions.
dpQuery("SELECT ALERT '_alert_hdl.._value', '_alert_hdl.._text' FROM 'mld_*.**' LAST 2", tab);
DebugN("Number n-1: ",tab[2][3]);
DebugN("Text n-1: ",tab[2][4]);
DebugN("Number n : ",tab[3][3]);
DebugN("Text n : ",tab[3][4]);
FIRST function - FIRST x
as LAST, only in this case the first x lines of the result table are returned.
dpQuery("SELECT ALERT '_alert_hdl.._value','_alert_hdl.._text' FROM 'mld_float.**' FIRST 2", tab);
DebugN("Number 1: ",tab[2][3]);
DebugN("Text 1: ",tab[2][4]);
DebugN("Number 2: ",tab[3][3]);
DebugN("Text 2: ",tab[3][4]);
NULL
In contrast to "0", NULL determines whether, for example, the value of an existing data point attribute is 0.
A query must be made for "0" to determine the existence of an attribute.
dpQuery("SELECT '_online.._value' FROM '*' WHERE '_alert_hdl.._type' != NULL", tab);
This query does not make much sense: All the data points in this project are output as the query result. This happens because the type attribute is always defined.
dpQuery("SELECT '_online.._value' FROM '*' WHERE '_alert_hdl.._type' !=0", tab);
With this constellation, all data points with alert handling are output.
Logical comparisons
Logical comparisons are used to select individual attributes. The following logical operators are available:
Operator | Description |
---|---|
=,==, IS | is equal to |
!=,<>, IS NOT | is not equal to |
>, >= | greater (equal to) |
<,<= | less than or equal to |
&&, AND | logical and |
||, OR | logical or |
IN (a1,a2,...) | selection list, see example below |
LIKE "string" | comparison of string with wildcards, see example below |
NOT LIKE "string" | negation of comparison of string with wildcards, see example below |
IN comparison
dpQuery("SELECT '_online.._value' FROM '*' WHERE _DP IN (\"mld_float\",\"mld_bit\")", tab);
The online values of all data points are read and then filtered later, whether the level DP is called mld_floator mld_bit.
LIKE comparison
A text comparison is carried out that only allows data points that begin with mld. One line for each data point is returned with the online value as the result.
dpQuery("SELECT '_online.._value' FROM '*' WHERE _DP LIKE \"mld*\"", tab);
NOT LIKE comparison
dpQuery("SELECT ALL '_online.._value' FROM 'ExampleDPArg*.' WHERE _DP NOT LIKE "ExampleDPArg3", tab);
All data points ExampleDPArg* are returned except ExampleDPArg3.
Group functions
The following group functions that are also provided by standard SQL can be used to evaluate a group of result lines.
If a group function is specified without a GROUP BY clause, the entire result table is placed in one line. The argument of the group function must be a valid specification before the FROM statement. The wildcard "*" is, in contrast to standard SQL, not supported here.
If a GROUP BY clause is specified, the table must first have been sorted by the same columns (in the same order): With SORT BY 1,3,4: GROUP BY 1,4 (but not GROUP BY 4,1).
The group command is followed by the statement in brackets before the FROM. The group commands are listed as follows:
COUNT - COUNT(x)
This function returns the number of lines of a group.
dpQuery("SELECT 'COUNT(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2)", tab);
MIN - MIN(x)
The function MIN is used to output the minimum within a group.
dpQuery("SELECT 'MIN(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2)", tab);
MAX - MAX(x)
The option MAX returns the maximum of the group as the result.
dpQuery("SELECT 'MAX(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2), tab);
AVG - AVG(x)
This group function returns the average value (arithmetic average) of the values in question.
dpQuery("SELECT 'AVG(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2), tab);
SUM - SUM(x)
The function SUM is used to form the group sum.
dpQuery("SELECT 'SUM(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2)", tab);
The result for all group functions can be queried with the following statement
dpQuery("SELECT 'MAX(_original.._value)', 'MIN(_original.._value)', 'SUM(_original.._value)','AVG(_original.._value)' FROM 'mld_float.**' TIMERANGE(\""+start+"\",\""+stop+"\",1,2)", tab);
Explanations on the TIMERANGE , on the variables start and stop can be found in the relevant chapter of this section.