4 Replies Latest reply on Oct 11, 2013 3:04 AM by hanyong

    How to get an attribute value at specific time stamps

    wasifh

      Hi

       

      I am new to vCampus. I have a problem. I need to bring second attribute value of the an element at the same time stamps of the first attribute value (based on some condiyion). I know transposed query in AF server can do this but it is very time consuming as database is big as I never seen an output and get time out after 40 min. I have used the normal query to get the value of one attribute and that get value for the second attribute from the by using the In (timestamp), the problem is it only gives me value for one element at one time stamp. As I have hundreds of elements. I think it make sense to you guys.   

       

       

        • Re: How to get an attribute value at specific time stamps
          hanyong

          Hi Hafiz,

           

          Seems like your question is more related to querying for data via the PI OLEDB Enterprise provider, am I right? From what you mentioned, I would say that your thought process is accurate. If the query is written appropriately, you should be able to retrieve values from multiple elements for multiple timestamps. Would you be able to share a sample of your query so that we can know where you are at?

            • Re: How to get an attribute value at specific time stamps
              wasifh

              SELECT eh.Name ChargerID,c.Time, c.ValueDbl

               

              FROM Database.Asset.ElementHierarchy eh

               

              INNER JOIN Database.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID

               

              INNER JOIN DataBase.Data.Archive c ON c.ElementAttributeID = ea.ID

               

              WHERE eh.Path LIKE N'\%\'

               

              AND eh.Name  like ('1%')  AND ea.Name IN ('time5E')

               

              AND c.Time In ( SELECT c.Time

               

              FROM Database.Asset.ElementHierarchy eh

               

              INNER JOIN Database.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID

               

              INNER JOIN DataBase.Data.Archive c ON c.ElementAttributeID = ea.ID

               

              WHERE eh.Path LIKE N'\%\'

               

              AND eh.Name LIKE N'1%'

               

              AND ea.Name IN ('FAULT_CODE')

               

              AND c.Time between '25/08/2013' and '*'

               

              AND c.ValueDbl is not null

               

              AND c.ValueDbl between 1 and 15

               

              )

               

              OPTION (ALLOW EXPENSIVE,FORCE ORDER,IGNORE ERRORS)

               

              Here in statement return about 300 time stamp. The overall output shows only one of the time stamp value of time5E. Remember number of elements are about 50. I think he needs to know which element is related to which timestamp. Even though if I limit the query to one element, it only shows me first time stamp value of the element and neglect others.

               

              It needs to be sorted in the query