Using Functions Within the RTQP Query Engine

Document created by kduffy on Dec 26, 2018Last modified by kduffy on Dec 26, 2018
Version 3Show Document
  • View in full screen mode

The RTQP Query Engine has a number of options when it comes to performing queries that contains functions and calculations.

 

These options include:

  • Scalar Functions
  • Built in Table-Valued Functions
  • Ad-hoc Template Based Table-Valued Functions
  • Custom Template Based Table-Valued Functions
  • Custom non-Template Based Table-Valued Functions

 

The allowable Scalar Functions are found in the LiveLibrary documentation as well as in the Scalar Functions list in the Object Explorer of PI SQL Commander:

 

The list includes math, string, date/time, and aggregate functions, as well as WhoAmI(), which provides the current user context, just like the command prompt's whoami command, or the Microsoft SQL Server's SELECT Current_User.

 

The next three methods are covered to some extent in Not authorized to view the specified discussion 2664.

  • Built in Table-Valued Functions are listed in either Master > Element > Table-Valued Functions or Master > EventFrame > Table-Valued Function

 

Each function's right click menu contains the option to "Execute Predefined Query", which can showcase the usage of this function:

 

Their descriptions can also be found in the LiveLibrary documentation for Element and EventFrame schemas.

  • Ad-hoc Template Based Table-Valued Functions can be found in the Templates Folder under either Master > Element or Master > EventFrame

Right clicking on each of these template based functions will provide a script to create them as a custom function.

The queries can be executed on an ad-hoc basis by removing the CREATE FUNCTION header, or by following the example queries in the Query Compendium's PI SQL Client > Queries > Element > 3-TemplateSpecificData.sql.

 

  • Custom Template-based Table-Valued Functions can be created either by executing the CREATE FUNCTION command demonstrated on the Template based ad-hoc table-value function's right click menu, or by using the Template-Specific Data Model wizard to add the requested functions to the schema:

 

Finally, this leaves the Custom non-Template Based Table-Valued Functions. These functions can be anything, as long as they are based on the following three structures list in the LiveLibrary's section on CREATE FUNCTION:

For the TVF's that are based on a template, it's easier to use the wizard or the templates folder to provide the structure, but for TVF's are not based on a template, the function can be created manually.

 

As an example, let's create a function that, when passed an attributeID, it returns the attribute's name.

  • We'll start by writing a query that returns the name, hard coding a randomly obtained AttributeID:

  • Next, we'll create a function header, replacing the hard coded AttributeID in the WHERE statement with the function's input parameter:

Note: We knew that the data type of @AttributeID is Guid because the Object Explorer tells us this for the Master.Element.Attribute table's columns:

  • We'll execute this query to create the function, and confirm that it now appears in the list of TVFs

Note: We can optionally also create a function table for this table-valued function by selecting "Create Function Table" from its right click menu:

  • Lastly, we can demonstrate the functionality of this new TVF by passing it the same AttributeID from earlier:

function table option:

This is a very simple example of what custom table-valued functions can do in the RTQP query engine, but unlike PI OLEDB Enterprise, the RTQP custom functions are not constrained to template-based transpose functions.

Attachments

    Outcomes