Object: The goal of this exercise is to practice the creation of a non-template based custom table-valued function for the RTQP Query Engine.
Using the following resource:
As well as these additional resources:
- Install, configure and test PI SQL DAS (RTQP Engine) & PI SQL Client
- Understand the basics of RTQP Engine schema
- PI SQL Commander
- Accessing the PI SQL Query Compendiums and Sample Queries
- RTQP Engine Reference Guide
- RTQP Engine Data Model
- General SQL Training - w3schools
- Nugreen Database
- Table-Valued Functions vs Function Tables
Create a custom function that provides the Plant name for any element in the NuGreen AF Database, aside from the root 'NuGreen' element itself (as this does not have any associations with a specific plant).
- The function should accept an ElementID as input
- It should output the name of the Plant ('Little Rock' should contain a space).
Explore the NuGreen database to see what you may be able to query against to always return the name of the plant.
Once it's created, test your function, either as a table-valued function, a function table, or both, by passing it all elements in NuGreen (aside from the root 'NuGreen' element itself). The output should appear such as this:
Have questions? Ask the community
When you are ready, compare your answers with the Solution: Creating Custom Functions with the RTQP Engine