Objective: The goal of this exercise is to practice the generation of PI OLEDB provider queries. Whether the application is using PI OLEDB Provider directly or using it through the PI ODBC Driver or PI JDBC Driver, the queries themselves are the same.
Using the following resources:
- Not authorized to view the specified discussion 2660
- Accessing the PI SQL Query Compendiums and Sample Queries
- Querying Digital State Values in PI OLEDB Provider
- Using Functions with PI OLEDB Provider
As well as these additional resources:
- Not authorized to view the specified discussion 2655
- How to Install PI SQL Commander
- The PI OLEDB Provider User Guide's explanation of catalogs (page 125-171)
- General SQL Training - w3schools
For each of the following scenarios, produce a PI OLEDB Provider query that outputs the requested data.
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. You're interested in seeing a running list of the recent phases that the meter has cycled through and for how long.
- Find the previous 10 values of CDM158, expressed as a string, as well as the timestamps of those values.
2. You have numerous tags coming in from one set of interfaces and you want to see just the tags that are on the high side of their range.
- Find the snapshot value of all tags with a pointsource of R whose current values are greater than their typical values.
3. At the end of every packaging cycle, the final weight is recorded in a tag. You'd like to see the total weight of the packaged product during a shift.
- Find the total of all recorded values for CDEP158 from yesterday for midnight to 8am, 8am to 4pm, and 4pm to midnight.
4. Your connection monitoring application makes a list of all connections made to the data archive.
- Find all connections made to the data archive in the last week
Hint: all new connections will be logged in the server message logs starting with "New Connection ID: <#>"
[If you don't use PI batch, either skip #5 or use Manually Create a PI Unit Batch to add a PI Batch and have a successful query]
5. You need to report the temperature min, max, and average of a reactor during a batch for reporting purposes
- Report the batchid; time range; and the min, max, and average temperatures during the time period of a batch of your choice (or Batch 1001 from the Manually Create a PI Unit Batch instructions)
Have questions? Ask the community
When you are ready, compare your answers with the Solution: Making PI OLEDB Provider Queries - Reads