Learn how to specify SQL queries

The SQL query that is associated with a tag determines how data is written to PI tags or to the RDBMS tables. SQL queries can be specified in either of two ways:

• In the extended descriptor (ExDesc) attribute of a PI tag. To specify a query in the extended descriptor attribute, precede it with the /SQL keyword, terminate it with a semicolon, and enclose it in double quotes. (You must specify the /SQL keyword in uppercase letters.) For example:

  • ExDesc:

    /SQL="SELECT timestamp, value,
    status FROM table1
    WHERE timestamp > ? ORDER BY timestamp ASC;" P1=TS

• In text files residing in a query files directory. This directory is configured for the interface instance using PI ICU. Typically, query files have a .SQL extension, but there is no required naming convention. To associate a query file with a tag, specify the name of the file in the InstrumentTag attribute and specify placeholders for replaceable parameters in the ExDesc attribute. For example:

  • MYQUERY.SQL is a text file that contains the following query:

    SELECT timestamp, value, status
    FROM table1
    WHERE timestamp > ?
    ORDER BY timestamp ASC;

  • InstrumentTag: MYQUERY.SQL

  • ExDesc: P1=TS

    Note: The PI Data Archive expects data to arrive in chronological sequence. To ensure that data arrives in chronological sequence use the ORDER BY clause to sort by timestamp.

To specify multiple SQL statements, separate them with semicolons. Following the query, specify any placeholders for replaceable parameters. For example:

/SQL="SELECT timestamp, value, status
FROM table1
WHERE timestamp > ?
ORDER BY timestamp ASC;
DELETE FROM table1 WHERE timestamp < GETDATE()-1;" P1=TS

Note: For input tags, when multiple SQL statements are specified, make sure there is only one SELECT statement among them. Use the UNION ALL construct if more than one SELECT is needed.