Asset Framework and PI System Explorer (PI Server 2018)

WHERE clause syntax

Save PDF

WHERE clause syntax

Save PDF

Last UpdatedJan 11, 2023
3 minute read

The WHERE clause uses SQL syntax and conforms in general to the syntax described on the MSDN DataColumn.Expression Property page. You can type the clause directly into the Complete WHERE Clause field in the Table Lookup Data Reference window, or allow PSE to provide the correct syntax by using the Column, Operator, and Attribute or Value lists in the Where section. The WHERE clause is optional for the Table provided time series data rule.

The WHERE clause syntax follows these guidelines.

Column	Column names that contain non-alphanumeric symbols must be surrounded by brackets: [Asset ID] ='%Element%'
Operator	The INTERPOLATE operator is available with the Select first row matching criteria rule. The comparison operators =, <>, >, >=, <, <=, IN, and LIKE are supported for all lookup rules. You can use the * and % characters interchangeably for wildcard characters in a LIKE comparison. A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern, but not in the middle of a string. If the string in a LIKE clause contains a * or %, those characters should be enclosed in [ ] brackets.
Attribute or Value	@attribute Returns the value of the PI AF attribute. The attribute must be enclosed in [ ] brackets if it contains any non-alphanumeric character, including spaces, or includes a UOM or Time Zone specification. For example: [height] >= @[Level Gauge;UOM=m]
	literal Strings should be enclosed in single quotes. Numeric values are not quoted and should be in invariant format (where the decimal character is '.'). Timestamps are best specified in yyyy.mm.dd hh.mm.ss format (0-23 for hh), and enclosed in the # character. For example: #2015.01.30 14:00:00#
	substitution Select or enter a substitution parameter in '%substitution parameter%' format. For more information on substitution parameters, see List of PI AF substitution parameters.

The Like and IN operators

The LIKE and IN operators enable you to specify a non-exact match for query results in a WHERE clause. Use the % character to specify zero, one or multiple characters at the beginning or end of a string.

Note: You can use the LIKE and IN operators as an alternative to the = operator when query results cannot be retrieved from a table due to the presence of control characters. See Remove control characters from imported AF tables for more information on removing control characters from an imported AF table.

Failed query example

In the following example, no data is returned by the query because the search value (ValueA) is located in a cell that contains control characters and the = operator specifies an exact match on a value in the same cell. The presence of control characters prevents the SELECT statement from returning an exact match.

SELECT ColumnB FROM Table1 WHERE ColumnA = 'ValueA'

Successful query examples

You can use the LIKE or IN operators to specify a non-exact match from a column that contains control characters. Consider the following examples that execute a successful search for data in MyTable. Both ColumnA and ColumnB contain control characters.

SELECT ColumnA FROM MyTable WHERE ColumnB LIKE '%ValueA%'

Another alternative is to use the IN operator to specify multiple values in a WHERE clause.

SELECT ColumnA FROM MyTable WHERE ColumnB IN ('ValueA', 'ValueB', 'ValueC')

Asset Framework and PI System Explorer (PI Server 2018)