SQL placeholders

A placeholder ('?' character) is a text character used in a SQL statement in place of a PI Server tag or text. When the SQL statement is executed, the '?' character is replaced by a specified value or expression before it is passed to the relational database. The placeholder acts as a variable, allowing users of the query to supply parameters that produce different data using the same SQL query.

The following types of placeholders can be used with a relational dataset:

• Text: the placeholder character is replaced with a text string.

• StartTime or EndTime: the placeholder character is replaced with the start or end time for a data collection interval.

• ResolveTag: the placeholder character is replaced with a PI tag value. The PI Server is queried first for the requested placeholder values. Then the relational database is queried once for each PI query value, using the PI values as filters.

To use placeholders, enter a SQL statement with placeholder characters and click the Verify SQL button.

The placeholders table is populated with one row for each '?' character in the SQL Statement, where placeholder 1 is the first '?' character, placeholder 2 is the second '?' character, and so on. Placeholders are evaluated sequentially, from left to right through the SQL statement, and their names cannot be changed. The following parameters are used to process placeholders:

Note: Oracle (Microsoft) provider uses different SQL syntax for placeholders. In Oracle a placeholder is denoted by a colon character ':' as a prefix, for example: select * from sinusoid where timestamp > :a and timestamp < :b