Select first row matching criteria

The Select first row matching criteria rule enables you to specify the first row that matches the value returned from the WHERE clause.

Syntax

SELECT column FROM table WHERE where clause ORDER BY column ASC|DESC; options and parameters

Arguments

• SELECT column

  If a column name contains non-alphanumeric characters, including spaces, it must be enclosed in [ ] brackets.

• FROM table

  If a table name contains non-alphanumeric characters, including spaces, it must be enclosed in [ ] brackets.

• WHERE clause

  In addition to =, <>, >, >=, <, <=, LIKE, and IN relational operators, you can specify INTERPOLATE to interpolate a value for the result column based on an interpolation of the specified input columns.

  Beginning with PI AF 2018, you can perform two kinds of interpolation:

  • Linear interpolation, in which interpolation is for 2 columns of data. To specify linear interpolation, use INTERPOLATE(column, value) syntax.

  • Standard bilinear interpolation, in which interpolation is for 3 columns of data. To specify bilinear interpolation, use INTERPOLATE(column_X,value_X) AND INTERPOLATE(column_Y,value_Y) syntax.

    Irregular bilinear interpolation and bilinear extrapolation are not currently supported.

    Note: The default behavior of the INTERPOLATE operator is to extrapolate if the input value is outside the observed table range. Note also that extrapolation behavior changes when the Stepped check box is selected.

    For more information on WHERE clause syntax, see WHERE clause syntax. For examples of INTERPOLATE operator usage, see Examples of linear and bilinear interpolation in table lookup data references.

• ORDER BY column

  Optional. Specifies the sorting order so that the correct row is used when more than one row matches the WHERE clause. Ascending (ASC) order is the default unless descending (DESC) is specified.

• Options

  You can enter the following options in a list separated by semicolons.

  Stepped	When set to True, the returned value plots as stepped in applications.
  TZ=time zone	Specifies the time zone of the source table. Note: Set the time zone in the general description of the table, so that you do not need to specify it with each table lookup.
  UOM=uom	Specifies the unit of measure for the value returned by the result column. Note: You can also set the unit of measure in the table column definition, so that you do not need to specify it with each table lookup.
  RWM=value	Specifies the value to return when there is no column match. If the value is No Data, the digital state of No Data is returned.
  RWN=value	Specifies the value to return when the result column is null. If the value is No Data, the digital state of No Data is returned.

• Parameters

  You can enter parameters in a list separated by semicolons. Begin each parameter name with the @ character in @parameter=value format (value is described in "Attribute or Value" in WHERE clause syntax). For additional information on using parameters, see Parameters for linked table queries.

Example

SELECT [Installation Date] FROM [Equipment Specifications] WHERE [Asset ID] = '%Element%'