Objective: The goal of this exercise is to practice the generation of Real Time Query Processing (RTQP Engine) queries. Whether the application is using PI SQL Client (OLEDB), PI SQL Client (ODBC), or PI SQL Client (JDBC), the queries themselves are the same.
Using the following resources:
- Not authorized to view the specified discussion 2664
- Accessing the PI SQL Query Compendiums and Sample Queries
- Table-Valued Functions vs Function Tables
- What is Cross Apply?
As well as these additional resources:
- Not authorized to view the specified discussion 2659
- PI SQL Commander
- Generate Sample Event Frames for NuGreen Database (note: requires PI OLEDB Enterprise)
- RTQP Engine Reference Guide
- RTQP Engine Data Model
- General SQL Training - w3schools
- Nugreen Database
Produce an RTQP query for each of the following scenarios that output the requested data using PI SQL Client (OLEDB):
Note: Each of the following scenarios represents a real-world usage of PI system data, but the tags being used are the default tags in order for everyone to be able to execute the query. If a scenario is particularly applicable to your operation, I encourage you to swap out the default tags with your own tags to make the queries more realistic.
1. An incident occurred at the Wichita plant yesterday at 10 pm; a snapshot of the pump in question needs to be looked at for investigation purposes. The tool used for investigations, however, cannot execute queries that contain functions.
- Generate a query that outputs the value of every attribute of Pump P-344 (NuGreen\Wichita\Distilling Process\Equipment\) yesterday at 10:00 pm. You cannot use a table-valued function in your final query (hint: Table-Valued Functions vs Function Tables).
2. A dashboard is showing the current values of the utility flow to all of the Compressors in a plant, but engineering would like another dashboard displaying the values in a different unit.
- Generate a query that outputs the current value for Steam Flow for all of the compressors in Little Rock (NuGreen database), displaying their values in m3/s instead of ft3/s.
3. A report needs to be generated showing the utility usage of the heaters across all plants for the last day.
- Generate a query that outputs the average Fuel Gas Flow for yesterday for all heaters in the NuGreen database, ordering them first by element name.
4. Long reactions can be a sign of reactor fouling, so maintenance would like a list of all vessels with multiple reactions yesterday taking longer than expected.
- Generate a query that outputs a list of units that had more than 2 reactions, stored in the AF server as 'OSIUnitProcedure' type event frames, with a duration longer than 30 minutes, ordered by their number of occurrences.
Note: To do this task, you'll need to Generate Sample Event Frames for NuGreen Database ; this currently requires the use of PI OLEDB Enterprise.
5. The members of the maintenance team are not SQL experts and don't feel comfortable running a query this complex just to get their list of units. They want to have a very simple query to execute and have the SQL DBA be in charge of keeping the query up to date with any changes.
- Create a view in the Event Frame schema that outputs the list generated in item 4 using a query in the form of (SELECT * FROM Master.EventFrame.FouledReactors)
Have questions? Ask the community
When you are ready, compare your answers with the Solution: Writing PI SQL Client and RTQP Queries