The SQL query language
A query from the HISTVAL table using the OLE DB provider is based on the SQL query language:
-
Read-only queries (SELECT)
-
Simple SQL syntax (no joins, subselects and intersects or unions)
-
Only "language-neutral" queries allowed
-
Wildcards allowed, even in DP names (also hierarchical)
-
Extension of time queries to milliseconds
-
Group alerts combining several single alerts of alert handling are allowed (ALERTS table).
Supported SQL commands
Command | Meaning |
SELECT FROM WHERE |
Selection of columns from tables according to specific conditions Note: Within SELECT queries only * or column names are allowed, no expressions. |
AS | For specifying a column header |
AND | AND-ing of conditions |
OR | OR-ing of conditions |
NOT | Negation of conditions |
( ) | Bracketing of conditions |
IN | Selection from list |
BETWEEN (AND) | Everything between 2 values |
LIKE | Comparison using wildcards |
NOT LIKE | Negation of comparison using wildcards. |
GROUP BY | Grouping of associated rows |
SUM | Sum over all rows that were selected with GROUP BY |
MIN | Minimum over all rows that were selected with GROUP BY |
MAX | Maximum over all rows that were selected with GROUP BY |
COUNT | Number of rows that were selected with GROUP BY |
AVG | Average over all rows that were selected with GROUP BY |
ORDER BY (DESC) | Sorting of result rows (descending order) |
Supported operators
Operator | Meaning |
= | Equal to |
< | Less than |
> | Greater than |
<> | Not equal to |
<= | Less than or equal to |
>= | Greater than or equal to |
+ | Addition |
- | Subtraction |
* | Multiplication |
/ | Division |
|| | String concatenation |
IS NULL | Question whether column contents are undefined |
? : | Question-mark operator (only allowed with column selection) |
SQL functions
Function | Meaning |
STR(x) | Converts x into a string |
INT(X) | Converts x into an integer |
FLOAT(X) | Converts x into a floating-point number |
STR2DATETIME(s,f) | Converts s (string) into a DBTYPE_DATE whereby f specifies the date format |
DATETIME2STR(d,f) | Converts d (DBTYPE_DATE) into a string whereby f specifies the required date format |
Tips and tricks
Familiarize yourself with the SQL chapter of the Online Help.
-
Specifying the format in the two date functions is optional. The default format is: "yyyy.mm.dd hh:mm:hh.mmm", for example, "2001.04.22 12:11:31.000".
-
The parameters given in the strftime() function of the ANSI standard for C can be used in the format string:
Function Meaning %a Abbreviated name of the day of the week %A Full name of the day of the week %b Abbreviated name of the month %B Full name of the month %c Local representation of date and time %d Day in the month (01 -31) %H Hour (00 -23) %I Hour (00 -12) %j Day in the year (001 -366) %m Month (01 - 12) %M Minute (00 -59) %p Local equivalent of AM (morning) and PM (afternoon) %S Second (00 -59) %U Week in the year (Sunday is the first day of the week) (00-53) %w Day of the week (0-6, Sunday is 0) %W Week in the year (Monday is the first day of the week) (00-53) %x Local representation of the date %X Local representation of the time %y Year without century (00-99) %Y Year with the century %Z time zone name %% %
-
Strings can be enclosed in single (') or double quotes (") as delimiters. Placing a backslash in front of a special character (that is, a string delimiter or a wildcard) cancels its meaning. Two backslashes qualify the backslash itself as character.
-
When specifying wildcards in the query, both the SQL syntax ("%" stands for any characters, "_" for any single character) and the WinCC OA syntax ("*" stands for any characters, "?" for any single character) can be used (see also config entry usePvssWildCards in the chapter Entries in the configuration file). The WinCC OA syntax is used by default which is more practical because the "_" character is frequently used in data point names and in the SQL syntax every instance would need a leading "/".
-
If the source time appears in a condition, the meaning of the equality operator is modified just in this case. It returns the values that were valid at this time. These need not have been generated at this time, however, and may therefore have an earlier source time in the database.
Example
SELECT ORIGINALVALUE, STIME FROM HISTVAL WHERE STIME = "2001.05.01 10:30:00.000"
This can return "2001.05.01 10:25:55:910" if the last entry was before 10:30. This exception only applies to the source time in the WHERE clause.
Unsupported SQL keywords
CREATE (DLL)
CREATE (DML)
CLOSE DATABASE
COMMIT
CONNECT
CONNECT BY PRIOR
CRINSTAB
DELETE FROM
DISTINCT
DROP
EXISTS
GRANT
HAVING
INNER JOIN
INSERT
INTERSECT
INTO TEMP
LEFT JOIN
RENAME
RESOURCE
REVOKE
RIGHT JOIN
SET AUTOCOMMIT
START WITH
UNION
UPDATE SET