7 Replies Latest reply on Oct 11, 2018 8:58 PM by vkaufmann

    PI SQL Client query to get Attribute data based on Event Frame timestam

    dadijke

      Hello,

      I'm having some difficulties to build the right query to retrieve data of an attribute based for a timestamp related to an Event Frame. The attribute is not part of the Event Frame itself.

       

      This is what I tried:

       

      SELECT e.Name, ea.Name Attribute, ef.StartTime, s.Value, ea.UnitOfMeasure

      FROM [Master].[Element].[Element] e

      INNER JOIN [Master].[Element].[Attribute] ea ON ea.ElementID = e.ID,

      (SELECT StartTime FROM [Master].[EventFrame].[EventFrame] WHERE Name = 'EFName') ef

      CROSS APPLY [Master].[Element].[GetSampledValue] (ea.ID, ef.StartTime) s

      WHERE e.Template = 'ElementTemplate'

      AND ea.Name IN ('Attribute1', 'Attribute2')

       

      Result: [SQL Query Engine] Object reference not set to an instance of an object.

       

      When just coping the starttime as a string, the result is fine:

       

      SELECT e.Name, ea.Name Attribute, ef.StartTime, s.Value, ea.UnitOfMeasure

      FROM [Master].[Element].[Element] e

      INNER JOIN [Master].[Element].[Attribute] ea ON ea.ElementID = e.ID,

      (SELECT StartTime FROM [Master].[EventFrame].[EventFrame] WHERE Name = 'EFName') ef

      CROSS APPLY [Master].[Element].[GetSampledValue] (ea.ID, '01-Oct-18 09:02:52') s

      WHERE e.Template = 'ElementTemplate'

      AND ea.Name IN ('Attribute1', 'Attribute2')

       

      How do I add the event frame starttime as parameter to the GetSampledValue function?

        • Re: PI SQL Client query to get Attribute data based on Event Frame timestam
          vkaufmann

          Hi Danny,

           

          I was able to make this query work for what you are asking. It's just a minor tweak to the default query for GetSampledValue built-in query.

           

          SELECT ea.Name AS Attribute, ef.StartTime AS Timestamp, s.Value, s.Error

          FROM

          (

              SELECT Name ElementName, ID ElementID

              FROM [Master].[Element].[Element]

              ORDER BY Name

          ) e

          INNER JOIN [Master].[Element].[Attribute] ea ON ea.ElementID = e.ElementID

          INNER JOIN [Master].[EventFrame].[EventFrame] ef ON ef.PrimaryReferencedElement = e.ElementName

          CROSS APPLY [Master].[Element].[GetSampledValue]

          (

              ea.ID, -- @AttributeID

              ef.StartTime -- @Time

          ) s

          WHERE ef.Template = 'EFTemplate'

          AND ea.Name IN ('CDT158','Sinusoid')

          ORDER BY ea.Name

           

           

          --Vince

          3 of 3 people found this helpful
            • Re: PI SQL Client query to get Attribute data based on Event Frame timestam
              dadijke

              Thanks Vince. That helps.

              But the attribute is not in the Event Frame itself, nor in the Primary Referenced Element; the attribute is in a child element of the primary referenced element:

              SELECT ea.Name AS Attribute, ef.StartTime AS Timestamp, s.Value, s.Error

              FROM

              (

                   SELECT ElementID, ParentElementID

                   FROM [Master].[Element].[ElementHierarchy]

                   WHERE Name = 'ElementName'

              ) e

              INNER JOIN [Master].[Element].[Attribute] ea ON ea.ElementID = e.ElementID

              INNER JOIN [Master].[EventFrame].[EventFrame] ef ON ef.PrimaryReferencedElementID = e.ParentElementID

              CROSS APPLY [Master].[Element].[GetSampledValue]

              (

                   ea.ID, -- @AttributeID

                   ef.StartTime -- @Time

              ) s

              WHERE ef.Template = 'EFTemplate' AND ef.Name = 'EFName'

              AND ea.Name IN ('Attribute1', 'Attribute2')

               

              But actually it makes sense to have the attribute in the Primary Referenced Element. After moving the elements, your query works fine. I only created the join on ID, instead of Name.

               

              Danny

              • Re: PI SQL Client query to get Attribute data based on Event Frame timestam
                dadijke

                Hi Vince,

                Just wondering, do you know if I can the attribute rows as columns of a view which I already have for the Event Frame?

                  • Re: PI SQL Client query to get Attribute data based on Event Frame timestam
                    vkaufmann

                    This works for me:

                     

                    SELECT ef.Name, ef.StartTime, s.*

                    FROM

                    (

                        SELECT ID, Name, Template, StartTime, EndTime

                        FROM [Master].[EventFrame].[EventFrame]

                    ) ef

                    CROSS APPLY [Master].[EventFrame].[EFTemplate_GetSampledValue]

                    (

                        ef.ID,  --Event Frame ID

                        ef.StartTime  --Time

                    ) s

                    WHERE ef.Template = N'EFTemplate'

                     

                     

                    This obviously needed a custom function to make this work. Here is the definition of my function:

                     

                    CREATE FUNCTION [Master].[EventFrame].[EFTemplate_GetSampledValue2]

                    (

                         @EventFrameID Guid,

                         @Time DateTime

                    )

                    AS

                    SELECT *

                    FROM [Master].[EventFrame].[GetSampledValue]

                    <

                         N'EFTemplate', --Template

                         {

                             N'|CDT158.Avg', -- AttributeTemplatePath

                             N'CDT158.Avg', -- ValueColumnName

                             N'CDT158.Avg_UOM', -- UnitOfMeasureColumnName

                             N'CDT158.Avg_Error' -- ErrorColumnName

                         },

                         {

                             N'|Sinusoid.Avg',

                             N'Sinusoid.Avg',

                             N'Sinusoid.Avg_UOM',

                             N'Sinusoid.Avg_Error'

                         }

                    >

                    (

                         @EventFrameID,

                         @Time

                    )

                     

                    Or depending on what you need, I could simply create a view:

                     

                    CREATE VIEW [Master].[EventFrame].[EFTemplate]

                    AS

                    SELECT ID, Name, Description, v.*

                    FROM [Master].[EventFrame].[EventFrame] e

                    INNER JOIN [Master].[EventFrame].[Value]

                    <

                      N'EFTemplate', --Template

                      {

                       N'|CDT158.Avg', -- AttributeTemplatePath

                       N'CDT158.Avg_TimeStamp', -- TimeStampColumnName

                       N'CDT158.Avg', -- ValueColumnName

                       N'CDT158.Avg_UOM', -- UnitOfMeasureColumnName

                       N'CDT158.Avg_Error' -- ErrorColumnName

                      },

                      {

                       N'|Sinusoid.Avg',

                       N'Sinusoid.Avg_TimeStamp',

                       N'Sinusoid.Avg',

                       N'Sinusoid.Avg_UOM',

                       N'Sinusoid.Avg_Error'

                      }

                    > v

                    ON e.ID = v.EventFrameID

                    WHERE e.Template = N'EFTemplate'

                     

                    SELECT Name, [CDT158.Avg_TimeStamp], [CDT158.Avg], [Sinusoid.Avg_Timestamp], [Sinusoid.Avg]

                    FROM [Master].[EventFrame].[EFTemplate]