How can we improve the PI Server?

Allow substitution parameters to be used in the SELECT and FROM clauses of the TableLookup Data Reference

As a user, I would like TableLookup Data Reference to be able to use substitution parameters to specify the column name(s) and table name for the SELECT and FROM fields respectively. This would allow for the use of attribute values like those from String Builder attributes to be used to identify the desired column(s) or table and facilitate the use of table lookup within Element Templates.

64 votes
Sign in Sign in with OSIsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Jason Jian-Hao Lu shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →


Sign in Sign in with OSIsoft
Signed in as (Sign out)
  • BradR commented  ·   ·  Flag as inappropriate

    I would like to use a substitution parameter in the WHERE clause to reference one of a group of attributes in the template.

    select mycolumn from mytable where table-ref-column = @template-ref-column%which -one%

    %which-one% would contain a numeric value.

    Is there a way to reference an array of "template-ref-column" to obtain the correct data from "mytable"?

  • EDYJR commented  ·   ·  Flag as inappropriate

    This Feature would be Awesome. Please Make It Happen. Need it ASAP!

  • rpalmen commented  ·   ·  Flag as inappropriate

    Unfortunately cannot vote multiple times, but new use case.
    Using templated lookups, but still need to override individual lookups to use the correct result column.

    Quite tricky currently, as one unwanted reset-to-template starts delivering faulty lookups...

  • Diego Curras commented  ·   ·  Flag as inappropriate

    Here is another user case:

    I would like TableLookup Data Reference to be able to use a reference to an attribute value (i.e @Attribute) to specify the column name(s) for the SELECT

  • Fabiano Batista commented  ·   ·  Flag as inappropriate

    Here is another user case:
    A customer has a federated PI System with several remote AF units, and one central AF showing the same content from each of those units. Each unit has the same set of templates and tables. The remote AF structures are supposed to be copied to the central unit via PI System Connector, each one appearing in the right branch of the central AF hierarchy. The central AF server has also the same set of templates and table names. Today, PI System connector cannot merge table data, but instead it replaces the existing central table by the content from the last unit which table was synchronized by PI System Connector.
    As a solution, the tables at each unit could be renamed by appending a unique identifier (such as unit code). Then, the element template could be configured with a TABLE LOOKUP containing a substitution parameter in the FROM clause to allow pulling data from different tables (i.e., unit specific tables).
    Some benefits of this solution:
    - Less element templates needed (easier to maintain in a federated PI system environment)
    - Better lookup performance (each branch on the central unit will be pointing to smaller tables, instead of pointing to a single big table)

  • Jason Jian-Hao Lu commented  ·   ·  Flag as inappropriate

    Here's an example from a TechSupport case:

    Customer wants to create a Table Lookup attribute template that uses the value of a String Builder attribute which includes substitution parameters to select the Table Name associated with it's Parent Element.

    For example, customer has a table named after each Element and they want to build out their Element hierarchy. They would want to do something like this to link the table lookup attributes to the desired table:

    Element: 11D01
    Table Name: 11D01 Stable
    String Builder attribute: %Element%;" Stable";

    Table Lookup Data Reference:

    SELECT <column> FROM %@String Builder attribute% WHERE <condition>

    The substitution paremters in the FROM clause would resolve to "11D01 Stable" as intended.

Feedback and Knowledge Base

Posted ideas will have one of the following statuses.
Full definition of these statuses can be found on the Home Page.
No status