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

CAUTION:

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!

Restriction:

Only config attributes can be used as WHERE condition in the SQL panel.

Do not use the same attribute twice in a SELECT clause.

Note: A change of the alias or comment does not trigger a new evaluation of the filter (queryConnect()). This is only done on change of a subscribed attribute.
Note:

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

CAUTION: The historical alert texts cannot be queried.
Note: A query with the keyword TIMERANGE is processed in the Data Manager. If this keyword is not specified, the query is processed in the Event Manager.

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

The option "now" can be used to query values as of a point in time until now or to query the current value:

Note: For the timerange times until the year 2038 can be used.
Important: The option "now" must be lower-cased!
Restriction:

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
Tip: Further information on time variable conversion can be found in Time Variable Conversion

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]);
}

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);
Note: The keyword ARCHIVENUM has to be defined at the end of the query after the keyword TIMERANGE (see example above).

Modus - Modus = 1

Restriction: For historical reasons, the Modus function is still included. The use of values other than 1 (2, 3) is not supported anymore.

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.

CAUTION: The bonus parameter is not supported for alert queries.
Attention: The bonus parameter can be used for single queries only (without wildcards).

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);
Note: The query syntax allows two options for the string definition (see, for example, main).

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 data point ID (first column of the result table). SORT\ORDER BY 1 refers to the first attribute after the 'SELECT' (second column of the result table).

CAUTION: If data points of different types are mixed with this command, this could result in unexpected events (now is 1 greater than or less than "abc"?).
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.

CAUTION: The SORT BY function must be called before the GROUP BY function!

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.

CAUTION: This method is not intended for processing alarm values (ALERT) and in conjunction with Connect commands.

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:

Table 1. Query GROUP BY
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.

CAUTION: This function is not available with Connect commands.
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.

CAUTION: When using a query with the option "queryRDBDirect = 1" the condition "!= NULL" in the WHERE clause (e.g. "'_online.._value' != NULL") will translate directly to "is not NULL" for Oracle. Since empty strings are stored as NULL in Oracle, this condition will rule out empty strings. The same thing applies to "= NULL" or "is NULL".

Logical comparisons

Logical comparisons are used to select individual attributes. The following logical operators are available:

Table 2. Logical comparisons
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.

Note: In this example, for performance reasons, the data point expression should be specified directly after the FROM, as this means that not all data point values will have to be read.

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.

Note: For _alias and _comment (they are part of _common config) LIKE must be used instead of an equal sign (=).
dpQuery("SELECT '_online.._value' FROM '*' WHERE _DP  LIKE \"mld*\"", tab);
CAUTION: In this example, for performance reasons, the data point expression should be specified directly after the FROM since this means that not all data point values will have to be read.

NOT LIKE comparison

dpQuery("SELECT ALL '_online.._value' FROM 'ExampleDPArg*.' WHERE  _DP NOT LIKE "ExampleDPArg3", tab);

All data points ExampleDPArg* are returned except ExampleDPArg3.

Note: In this example, for performance reasons, the data point expression should be specified directly after the FROM since this means that not all data point values will have to be read.

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:

Note: The functions MIN, MAX,AVG and COUNT only support numeric data types and dynamic numeric data types.

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.