• Replies 5 replies
  • Subscribers 22 subscribers
  • Views 40 views
  • Users 0 members are here
Options
Related

PI SQL Query - ROW_NUMBER() ?

Javith Fareeth

PI SQL Query has multiple result. How can i add the row number. there is one example but that is for linked server.

My requirement is for PI SQL client and i read the result back to AF attribute.

  • Hello Javith,

     

    There is no function implemented with PI SQL Client / PI SQL DAS (RTQP) for the row count.

     

    In addition I have some doubts about creating a Linked Table in PI AF to access PI AF Data through PI SQL DAS (RTQP) because this appears a very indirect approach. The ROW_NUMBER() function is available in SQL Server which means you could create a Linked Server in a SQL Server instance through PI SQL Client OLEDB, make the row numbers available e.g. by creating a view and create a Linked Table in PI AF to get the data from that view but this is even more indirect.

     

     

    Can you indicate your use case or what information you finally want to see on the AF Attribute?

     

    I assume you like to get the count of events from another Attribute over a certain period. If so, you should be able to get this through Asset Analytics - by creating an Analyses in PI System Explorer using the EventCount() function.
  • in reply to Gregor.Beck

    My requirement is to correlate pressure peak value with sequence number of different assets. To find which sequence of which asset is contributing to peak pressure.

    Common Pressure tag - 1 no.

    Assets - 18 no.

    Each asset sequence goes from 1 t 24.

    So i take 20 seconds range calculation for Pressure and combine 20 seconds samples of sequence number of all assets for the duration of last 4hrs .As shown below.


    pastedImage_2.png.png

    And then take average of Pressure by grouping sequence number. 

    Combine all the sequence of all asset and find the max 3 pressure values along with asset name and sequence number. 

    Finally i get this result,

     


    pastedImage_5.png.png

    If you can suggest something can be done in AF analytics it will be great. 

  • in reply to Javith Fareeth

    Hello Javith,

     

    I am not quite sure, also not because I don't really understand how you sample 20 second averages over a period of 4 hours and assign the sequences from 1 to 24. However, if the sequence information is constant, meaning you are able to uniquely link the sequence to timestamps, it may be possible to do this based on an Analysis. You would have to record the sequence to a PI Point but because I understand the sequence is the same for all assets, spending just a single PI Point can work.

  • I also have a use case for this functionality.

     

    I have bad actor KPI's relating to lots of assets - a dynamic number, and I want to be able to query to determine what are my top bad actors. So I used the PI SQL Client driver to do the searching and sorting of the results and have this as a liked table in AF (back to itself). I then want to display say the top 5 bad actors on each site. However there is no way in a table lookup attribute DR to extract a specific row number. It only lets you choose the first row returned.

     

    Ultimately this will displayed in a PI Vision page so maybe a custom symbol could be written to display the table, but I am not a JavaScript guru so I want to avoid this at all costs. And I have limits on tags so cannot go wild with an Analysis solution.

     

    If PI SQL Client would support returning a specific row, or the table lookup DR supported returnign a specific row number that would make this kind of use case easy to implement.

  • in reply to VCampus-METCO

    Hello Simon Dyson and Javith Fareeth Basha,

     

    You both have totally different use cases and both appear reasonable to me. I suggest you to post an enhancement request on Uservoice for the PI SQL Client.