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