Querying Digital State Values in PI OLEDB Provider

Document created by kduffy on Oct 8, 2018Last modified by jkim on Nov 8, 2018
Version 3Show Document
  • View in full screen mode

Digital state tags can be tricky with the PI OLEDB Provider. In PI System Management Tool's (PI SMT) Archive Editor, we see string values for cdm158, but PI OLEDB Provider shows a largely negative integer:


While the PI OLEDB Provider is capable of returning both the integer offset and the string value of a digital state tag, due to the nature of the query engine requiring a single value instead of a set/offset value pair, the string value is a little more difficult to obtain. Let's use PI SQL Commander to take a closer look.


From the Object Explorer, expand the pids catalog to see that every table in this catalog is a digital state set, as confirmed by PI System Management Tool's Points > Digital States:


If we right click on the SYSTEM table and execute the predefined query (and remove "TOP 100" in order to see all 318 states), we see that the "name" column and the "offset" column match what we see in PI SMT:


But we also see the "code" column. This code column represents a unique value across all digital state strings across the entire PI Data Archive. It's a combination of the state set's code, then the offset of each specific state in that set. If we query other state sets we can see this more clearly:


Each of these states have a unique set of codes. This enables the PI OLEDB Provider to determine exactly which digital state set and offset corresponds to the value being returned in the piarchive table queries. To see the complete list of codes for all digital states, you can output everything from the pids table:


Now that we've explored the digital state catalog, let's apply it to the piarchive values of digital state tags. For example, let's check the values for the last 10 minutes for cdm158, the built-in digital state tag.


The values being returned are the codes that we saw earlier, but this code means nothing to anything other than the PI OLEDB Provider itself, so the output as is not particularly useful.


To convert this to a string and offset, we have three options. The first two are joining to the tables, so let's explore those first.


Since we know that this tag uses the Modes digit state set, we can join specifically that table:

SELECT c.tag, c.time, m.name, m.offset

FROM [piarchive]..[picomp2] c

JOIN [pids]..[Modes] m

ON c.value = m.code

WHERE tag = 'cdm158'

AND time BETWEEN '*-10m' AND '*'


Sometimes, however, the digital state set is not known or there are multiple state sets that need to be joined for multiple tags, so it's easier to use the pids table for everything:

SELECT c.tag, c.time, d.name, d.offset

FROM [piarchive]..[picomp2] c

JOIN [pids]..[pids] d

ON c.value = d.code

WHERE tag = 'cdm158'

AND time BETWEEN '*-10m' AND '*'


However, the easiest way to query these string values, if the offset code is not necessary, is to use the DIGSTRING() function.


DIGSTRING takes in a digital state code as int32 and outputs the corresponding string.

Note: PI OLEDB Provider returns values as a variant, so the value needs to be cast as int32 prior to being passed to DIGSTRING().


SELECT tag, time, DIGSTRING(CAST(value as int32))

FROM [piarchive]..[picomp2]

WHERE tag = 'cdm158'

AND time BETWEEN '*-10m' AND '*'

2 people found this helpful