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:
- 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.